Simply Clarion - Lookups, Aliases and RI - Part 2

Posted March 1 1999

Printer-friendly version

Last Month

Last month's article talked about five types of lookups. They included:

• Lookup without dictionary referential integrity checks
• Lookup with dictionary referential integrity checks
• Multiple lookups between the same two files
• Lookups without using dictionary relationships
• Specialized Lookups

We specifically reviewed the first type of lookup after reviewing principles of referential integrity. This month we'll examine Lookup with dictionary referential integrity checks. We'll be referring to the same files in this article and show what modifications might be made. You will recall that we had an employee file with two lookups to the Position file. One was for the employee's position; one was for the supervisor's position. It would be helpful to have last month's article close by.

Lookup with dictionary referential integrity checks

With referential integrity checks, we'll set up a primary, auto-incrementing key, a Long, in the lookup file (Position), and a related foreign key, also a Long, in the employee file. Referential integrity will be maintained using these two keys. The key values themselves will be hidden; the text field of the lookup file will be visible to the end user.

Why do this?

With the scenario we had last month, if there is a change to a given position (the position "Bottle Washer" has been upgraded to "Glass Cleanser Engineer") all records with the former position would need to be updated. In this month's scenario, the text "Bottle Washer" is not carried in the employee record at all, only the pointer to the Position file. When the position change occurs, only the record in Position needs to change. The relationships remain constant. All online programs and reports now use the new values.

How do we do this?

First, we add a new field to Position which will be used in the auto-incrementing key. This field will have no other meaning other than to provide a way to uniquely identify each record. We'll call the field POSId.

One naming convention I use is to use the prefix of the file (POS) and add "Id" to produce the name of this id field. Others may use different naming conventions. Once the field is established above, we add a new key ByPOSId.

Under Attributes, we set Require Unique Value, Primary Key, Autonumber, and Exclude empty keys. Under Fields, we select only the field POSId.

The fields in Position now look like this:

Having made the changes to the lookup file (Position), we now turn our attention to the Employee file. Last month we left this file looking like the following:

Last month we had two lookups from the employee file to Position. String field from the lookup file were put into JobTitle and SupvJobTitle. Instead of these string field, we'll put linking fields (Foreign Keys) which link to the lookup file. Next month we'll cover using an alias file, so for purposes of this article, I'm going to remove the SupvJobTitle field. We'll add this relationship back next month. We add a new Foreign key field EmpPosId. (Since I know I'll have at least two fields linking to the same file, I use the name EmpPosId instead of PosId. Next month I'll add SupvPosId)

Under options we check the box Do Not Autopopulate this Field. Our field list now looks like this:

Our keys for Employee for last month looked like the following:

We now want to add the Foreign Key in Employee which will point to Position.

Under the Fields tab, we select EmpPOSId.

Our key summary for Employee now looks like this:

After setting keys in both files, we can add the relationship between the files into the dictionary:

Because we are using keys with no other meaning than preserving uniqueness, and linkages, the Referential Integrity Constraints "On Update" will always be "No Action". The key value of the parent (Position) should never change; therefore the children foreign keys will never change in an update. The Referential Integrity Constraint "On Delete" depends on the files in question. In this case, we do not want a parent (Position) record to be deleted if there are any children (Employees) pointing to it. Having established this relationship in the dictionary, Clarion sets up the appropriate code to maintain the Referential Integrity.

After establishing the relationship between the two files, we return to the Employee file, and look at the Validity Check properties for EmpPOSId. We indicate that this field must be in the file Position.

Under Options, we set Do not Auto-Populate This field.

Changes to the Application

After making the necessary dictionary changes, we now change the application. We start with the Browse for Employee. Under the Files button of the procedures properties, we only had one file (Employee) before. Because of the linkage between the files, we can now insert a related file (Position).

Pressing the Window button gives us the browse screen we produced last time:

Going into the List Box Formatter for the List Box, we change it to look like the following:

Notice that the Heading text "Employee Position" has been entered for the Position field ApprovedValue. For each record of Employee, the corresponding linked Position record will be retrieved and the ApprovedValue field will be displayed.

Lookup2 Template

Before changing the form, let me show a template we use in this situation. First, the scenario:

• We want the database to maintain the referential integrity via the use of autoincrementing keys and Foreign keys.
• We want these keys to be hidden from the end user. Processing will appear to use the text fields. However, behind the scenes, the appropriate keys will be updated. This gives us an ease of use.
• Although we could hand-code the necessary routines, we prefer to build a template; this makes development easier, more consistent, and more accurate.

The template looks like this:

#TEMPLATE(WCCGFREE,'WCCG Free Templates'),FAMILY('ABC')
#CODE(WCLook2,'Lookup type two'),DESCRIPTION('Type 2 lookup: ' & %ForeignKeyField)
#DISPLAY ('')
#DISPLAY (' Lookup Type Two - Documented in Clarion Online')
#DISPLAY ('')
#DISPLAY ('This is used to set an Foreign Key from a lookup file')
#DISPLAY ('     Linkage is via hidden keys')
#DISPLAY ('     Data entry is to ENTRY field from Local fields')
#DISPLAY ('     Invalid entry forces lookup')
#DISPLAY ('')
#PROMPT ('Select Lookup File', FILE), %LookupFile, REQ
#PROMPT ('Select Id Field in Lookup', FIELD (%LookupFile)), %LookupIdField, REQ
#PROMPT ('Select Text Field in Lookup', FIELD (%LookupFile)), %LookupTextField, REQ
#PROMPT ('Key for alpha lookup', KEY (%LookupFile)), %AlphaLookupKey
#PROMPT ('Key for ID lookup', KEY (%LookupFile)), %IdLookupKey
#PROMPT ('Select File with Foreign Key', FILE), %ChildFile, REQ
#PROMPT ('Select Foreign Key Field', FIELD (%ChildFile)), %ForeignKeyField, REQ
#PROMPT ('Select Local ENTRY Field', FROM (%LocalData)), %LocField, REQ
#PROMPT ('Lookup Procedure',PROCEDURE),%LookupProcedure, REQ

If %LocField = ''
   %ForeignKeyField = 0
Else
   %LookupTextField = %LocField
   If Access:%LookupFile.fetch(%AlphaLookupKey)
      GlobalRequest = SelectRecord
      %LookupProcedure
      If GlobalResponse = RequestCompleted
         %ForeignKeyField = %LookupIdField
         %LocField = %LookupTextField
      Else
        Message(%LocField & ' not in lookup file, changed to blanks. Reenter if required.')
        %LocField = ''
        %ForeignKeyField = 0
      End !If
   Else
      %ForeignKeyField = %LookupIdField
      %LocField = %LookupTextField
   End !If
End !If
If ?%LocField{Prop:Req} and %ForeignKeyField = 0
   Message('This field is required')
   Select(?%LocField)
   Cycle
End !If
ThisWindow.Reset()

#AT (%WindowManagerMethodCodeSection,'Init'),PRIORITY(7501)
If Self.Request = ChangeRecord or GlobalRequest = DeleteRecord
   %LookupIdField = %ForeignKeyField
   If Access:%LookupFile.fetch(%IdLookupKey)
      Message('Child key no longer in lookup')
      %LocField = ''
   Else
      %LocField = %LookupTextField
   End !If
End !If
#ENDAT

Implementing the Template

Prior to using the template, we add a local field to serve as an entry field for the Employee Position. Its length and type will be similar to the text field of the lookup file. In this example I call the field L:EmpPosition. I add this field to the existing form and give it the prompt "Employee Title:"

I right-click on the entry field L:EmpPosition and select Embeds. I highlight Control Events/?L:EmpPosition/Accepted/Generated Code and press Insert. I then highlight the WCLook2 template from the Class WCCGFREE and press Select.

Now I fill in the template prompts with the appropriate information for this structure:

That's all I need to do. The template handles the rest of the code generation.

The Working Program

This is a change to the Thomas Smith record. Note that the Local Field has been primed with the value from the Lookup file.

If I manually enter an approved value, "Bottle Washer," the screen proceeds to the next field. Behind the scenes, the Foreign Key for EMP:EmpPOSId has been set. We know this when we return to the browse.

If we return to the Form and enter an invalid entry for Employee Title...

...we get the selection screen:

We highlight a new position, say Vice President, and press Select. Our Form now looks like this:

The Foreign keys have been updated. Our browse reflects the change:

Advantages

Relational Integrity is maintained between the lookup file and the file with the relational key. Pseudo-orphan records are not created when the lookup file description is changed. We have eliminated all of the disadvantages from the lookup strategy we discussed last month.

Disadvantages

This strategy does generate some extra disk reads. In addition, there will be some storage and processing of the extra keys. Usually the extra validity of the data is worth it.

In a future article we'll show you a way to minimize this. Next time we'll walk through multiple relationships between two files.

Summary

One can quickly build a lookup which maintains hidden internal keys, yet is user friendly. A template assists us in generating the code.

Article comments

Post a comment

You must be logged on to post comments.