Developing Applications in DATATRIEVE
Names and Addresses

Joe H. Gallagher, Ph. D.



In our business activities, we keep track of things and people. If we are in a Human Resources Department, then "people" are our principle business. Even if we are in manufacturing, we are interested in the people who will buy the things that we make. Is is, therefore, very likely that there are names and addresses of people in almost every DATATRIEVE application.

New users of DATATRIEVE and even experienced Data Processing professionals often use on the strict data storage and retrieval capabilities in building their application and overlook the processing and reformatting capabilities of DATATRIEVE that makes the output of their application appear as though it were created by a human.

Consider the following fragment of a record definition.

 
   . . .
03 ID ...   .                        ! primary key (no dups)
03 NAME.
   05 LAST_NAME PIC x(20).           ! secondary key (dups)
   05 FIRST_NAME PIC X(10).
   05 MINIT PIC X(1).
   05 SENIORITY_CODE PIC 9(1)
      VALID IF SENIORITY_CODE IN SENIORITY_TABLE.
   05 SENIORITY COMPUTED BY CHOICE OF
      SENIORITY_CODE EQUAL 0 THEN ""
      ELSE ", "|SENIORITY_CODE VIA SENIORITY_TABLE
      END_CHOICE.
   05 TITLE_OF_ADDRESS_CODE PIC 9(1)
      VALID IF TITLE_OF_ADDRESS_CODE IN TITLE_OF_ADDRESS_TABLE.
   05 TITLE_OF_ADDRESS COMPUTED BY 
      TITLE_OF_ADDRESS_CODE VIA TITLE_OF_ADDRESS_TABLE.
   05 LENGTH_OF_TITLE_OF_ADDRESS COMPUTED BY
      FN$STR_LOC(TITLE_OF_ADDRESS," ") - 1
      QUERY_NAME IS LOTOA.
   05 PRINT_MINIT COMPUTED BY CHOICE OF
      MINIT NOT EQUAL " " THEN MINIT|". "
      ELSE ""
      END_CHOICE.
   05 PRINT_NAME COMPUTED BY CHOICE OF
      LOTOA EQ 0 THEN FIRST_NAME|||PRINT_MINIT|LAST_NAME||SENIORITY
      ELSE TITLE_OF_ADDRESS|||FIRST_NAME|||PRINT_MINIT|LAST_NAME||SENIORITY
      END_CHOICE.
03 COMPANY_NAME PIC X(30).
03 ADDRESS.
   05 ADDRESS1 PIC X(30).
   05 ADDRESS2 PIC X(30).
   05 CITY PIC X(22).
   05 STATE_CODE PIC X(2)
      VALID IF STATE_CODE IN STATE_CODE_TABLE.
   05 ZIP PIC X(9)
      VALID IF ZIP NOT CONTAINING "-".
   05 PRINT_ZIP COMPUTED BY CHOICE OF
      FN$STR_LOC(ZIP," ") EQUAL 1 THEN ""                    !missing
      FN$STR_LOC(ZIP," ") EQUAL 6 THEN FORMAT ZIP USING X(5) !US 5 digit
      FN$STR_LOC(ZIP," ") EQUAL 4 THEN FORMAT ZIP USING X(7) !Canadian
      FN$STR_LOC(ZIP," ") EQUAL 7 THEN FORMAT ZIP USING X(3)BX(3) !Canadian
      ELSE FORMAT ZIP USING X(5)"-"X(4)                      !US 9 digit
      END_CHOICE
   05 CITY_STATE_ZIP COMPUTED BY CITY||", "||| -
       STATE_CODE VIA STATE_CODE_TABLE|||PRINT_ZIP .
   . . .

ID is the primary (unique) key, such as employee number, patient number, or social security number. MINIT is the person's middle initial; it may be left blank. The SENIORITY_CODE is translated into the individuals seniority by the table SENIORITY_TABLE which looks like

 
    DEFINE TABLE SENIORITY_TABLE USING
    0:"",
    1:"Jr.",
    2:"Sr.",
    3:"III",
    4:"IV"
    END-TABLE

The TITLE_OF_ADDRESS_CODE is translated into the TITLE_OF_ADDRESS by the table TITLE_OF_ADDRESS_TABLE which looks like

 
    DEFINE TABLE TITLE_OF_ADDRESS_TABLE USING
     0:"",
     1:"Mr.",
     2:"Mrs.",
     3:"Miss"
     4:"Ms."
     5:"Dr."
     END-TABLE 

Other entries could be added to the TITLE_OF_ADDRESS_TABLE to accommodate religious leaders title, public official, etc.

Since the title of address appears at the beginning of a full name and one has to manage the situation where there is no title of address, it is necessary to determine the length of the string which is the title of address. When the title of address is a null string (has zero length), special care must be exercised to avoid a single space in the front of the first name when the null title of address is concatenated with the first name with the "|||" concatenation operator which leaves one space between the strings. The computed field LENGTH_OF_TITLE_OF_ADDRESS is the length of the title of address field; it is tested in the COMPUTED BY CHOICE OF in the field PRINT_NAME.

A missing middle initial also presents a problem. One does not want a missing middle initial to appear as " .". The COMPUTED BY field PRINT_MINIT properly handles the case.

The last field in the NAME group is PRINT_NAME: it is the final product of our intermediate COMPUTED BY fields. PRINT_NAME accommodates all the possibilities of missing values (including middle initial). PRINT_NAME can produce

 
    Dr. Joe H. Gallagher
    Mr. Joe Gallagher, Jr.
    Joe H. Gallagher, Sr.

each with the exactly the right number of spaces between the parts of the name.

Most of the address fields are much more straight forward. Only the STATE_CODE and the ZIP provide some creativity for DATATRIEVE. The fifty states and U. S. Territories all have standard two letter abbreviations. It turns out that this two letter code can be extended to many foreign countries. The provinces of Canada, the status of Australia, Japan, etc. can be accommodated in such a table. The table might look like


    DEFINE TABLE STATE_CODE_TABLE USING
    . . .
    MO:"Missouri",
    MS:"Mississippi",
    MT:"Montana",
    MX:"Mexico",
    NA:"New South Wales, AUSTRALIA",
    NB:"New Brunswick, CANADA",
    NC:"North Carolina",
    ND:"North Dakota",
    NE:"Nebraska",
    NF:"Newfoundland, CANADA",
    NH:"New Hampshire",
    NI:"Northern Ireland",
    NJ:"New Jersey",
    NM:"New Mexico",
    NO:"Norway",
    NS:"Nova Scotia, CANADA",
    NT:"Northwest Territories, CANADA",
    NV:"Nevada",
    NY:"New York",
    NZ:"New Zealand",
    . . .
    END_TABLE

The zip code also presents a challenge. US Zip Codes are 5 or 9 digits in length; Canadian zip codes are 6 or 7 characters in length depending upon whether a space is put in the middle; and European mail codes are even more complicated. The COMPUTED BY field PRINT_ZIP tries to change the format of the zip code based on the length of the code.

The last field in the record definition fragment, CITY_STATE_ZIP concatenates the three fields together to form a printable line.

The real benefit of all this extra effort and complexity in the record definition pays off when we want to do a simeple application like printing mailing labels. All the complex formatting is done! There is also almost no performance disadvantage to have a complex record definition with lots of COMPUTED BY fields. These extra fields cost you time when the record definition is inserted into the dictionary and when the domain is READY'ed (but this is much less now with the OPTIMIZE clause). And these extra COMPUTED BY fields are not computed until they are used.

DATATRIEVE code to print labels would then look like:


    ! READY your-domain
    
    DECLARE LINE4 PIC X(40).      ! PRINT BUFFER - LINE 4
    DECLARE LINE5 PIC X(40).      ! PRINT BUFFER - LINE 5
    
    FOR your-domain BEGIN
        LINE4 = " "
        LINE5 = " "
        IF (ADDRESS2 EQ " ") THEN BEGIN
            LINE4 = CITY_STATE_ZIP
            END ELSE BEGIN
            LINE4 = ADDRESS2
            LINE5 = CITY_STATE_ZIP
            END
        PRINT PRINT_NAME(-)
        PRINT COMPANY(-)
        PRINT ADDRESS1(-)
        PRINT LINE4(-)
        PRINT LINE5(-)
        PRINT " "     ! This is a blank line to skip to the next label
        END
    . . .
            

The only thing unusual in the code (which can easily be incorporated into a procedure) is the test for a blank (missing) second address line. ADDRESS2, and the movement of the city, state, and zip up one line four is handled either in a four line or a five line address. This look like

 
    Dr. Joe H. Gallagher       or    Dr. Joe H. Gallagher
    4GL Solutions                    4GL Solutions
    10308 Metcalf, Suite 109         10308 Metcalf
    Overland Park, Kansas 66212      Suite 109
                                     Overland Park, Kansas 66212

depending on whether "Suite 109" is at the end of ADDRESS1 and ADDRESS2 is blank, or is in ADDRESS2.

I hope the ideas presented here will help your make more complex and functional record definitions with simpler procedures and REPORT commands. You should make use of the full capabilities of DATATRIEVE to format data and information not just store and retrieve it.


Originally published in the newsletter of the DATATRIEVE/4GL SIG, The Wombat Examiner and 4GL Dispatch, Volume 9, Number 1, pages 7-11; in the Combined SIGs Newsletters of Digital Equipment Computer Users Society, Volume 3, Number 1, September 1985.
Joe H. Gallagher, Ph. D.
dtrwiz@ix.netcom.com
MAILTO

BACK Back