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.
Joe H. Gallagher, Ph. D.