Merging Data from Two Clinics into a Central Database (continued)

Step II: Restoring FKEY for an Epi Info Automatic RELATE in the ENTER Program

At this point, we have finished MERGING the Clinic CLIENT data into the Central CLIENT table, and the Clinic VISIT data into the Central VISIT table. But now we also want the VISIT records at Central to relate to the CLIENT records in the same database.

Since both CLIENT and VISIT from the same patient have the same CLINIC and ID values, we can easily RELATE them in Analysis. But the data entry program, ENTER.EXE, will only work with the internal keys provided by Epi Info, namely UNIQUEKEY in CLIENT and FKEY (for Foreign Key because it points to related records in a table external to the one containing the FKEY) in VISIT records. After the UPDATE APPEND operations that we performed separately on the CLIENT and VISIT tables, UNIQUEKEY and FKEY may not match for records with the same Client ID, or FKEY may be missing.

To restore FKEY, we must use a third type of MERGE called MERGE RELATE. MERGE RELATE is a bit of a secret, since it can only be invoked by UNCHECKING both APPEND and UPDATE in the MERGE dialog. Then the word RELATE is appended to the MERGE command, and it will regenerate an Epi Info relationship (FKEY in VISIT equal to the UniqueKey of the CLIENT) between the two tables involved, using the keys that you provide. In this case the appropriate keys are ID and CLINIC. DateOfVisit is not involved, since there is none in the CLIENT table, and VISITS for a single Client will all have the same FKEY, equivalent to the Client ID, regardless of the date of visit.

Notes

  1. You must READ VISIT and MERGE CLIENT, not the reverse, since we are inserting new values for FKEY into the Visit (child) Table.

  2. There is an unfortunate BUG in BUILD KEY that does not allow you to put multiple fields on separate lines by pressing the ENTER key. You must use CTRL-ENTER instead to begin a new line after ID, for example. Since putting more than one key on a line in BUILD KEY causes an error in MERGE (and also in RELATE), we have put the CTRL-ENTER trick in large type.

  3. By some strange bit of logic, checking both UPDATE and APPEND in the MERGE Command dialog places neither word after the program statement, since these are the defaults; adding the words causes a syntax error. Checking NEITHER option, places the word RELATE after the program statement, something I had to learn in an email from the programmer of Analysis.

  4. Trying to remember these quirks can be deleterious to your mental health. It is better to copy working examples, and/or rely on trial and error , during those long evenings as the sun goes down.

Exit from the browser (X in the upper right corner) to see see the data before the MERGE, and then do the MERGE RELATE and see the results. The important commands are displayed in large type above the data.