Simply Clarion - Lookups, Aliases and RI - Part 4

by Don Reynolds

Published 1999-06-01    Printer-friendly version

The Saga continues...

Part 1 noted at least five kinds of lookups:

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

So far we have covered the first three. Today we'll cover the fourth kind of lookup.

If you are new to this series, you may want to review the previous articles....

Dictionary for Part 3

We're going to be making several changes to the dictionary in part four. In part three the employee file looked like this:

We set up the following key structure for employee:

Our small sample dictionary looked like this:

Two dictionary relationships for Employee were defined.

Dictionary Modifications for Part Four

In part four we will remove the lookup file Position and its alias file. We will remove all defined relationships for codes in the dictionary. In place of the Position file and its alias file we'll set up a general-purpose code file and its parent file. In these two files will be all codes in our system. We will eliminate all other code files. Just to illustrate additional codes, we'll add some additional lookup fields. Employee now looks like this:

We've added three more code fields to employee: One for type of pay, one for vehicle authorization, and one for primary language spoken. The keys for Employee look like this:

Notice that there are no keys for the foreign fields.

Code Files

In this scenario, I actually have something like a code file for a code file. I call it a code class file or a code type file. It looks like this:

The keys for this file are as follows:

The code file looks like this:

And the keys for the code file look like this:

I establish a relationship between the code class file and the code file:

With these dictionary changes I now have a code class file and a code file which I can use throughout my application.

Template Modifications

In part two we used a template to generate the code for that type of lookup. In part four we will also use a template designed to use the revised database structure. I also need to add a special lookup template primarily used in browses. I've included both the part two and part four templates in the following example:

NOTE: The download is missing from the archive. If you have this file (reynolds.exe), please contact the editor. 

Using the templates with the new dictionary

First we'll look at how the application looks when it is run, and then we'll look at the implementation of the templates which support the processing.

Managing the Code files

Since we have a new set of code files, we need a way to modify these records. We build a double browse which looks like this.

As the various rows of code class are selected, the valid codes for the highlighted code are listed in the right-hand listbox. Insert, change and delete buttons allow creation of new classes or codes. When a new code is added, the Class Type is automatically inserted into the record. Update for a Code Class looks like this:

Update for a specific code looks like this (using primary language as an example):

The above three screens handle all of the maintenance for the two code files in this system. No special templates are needed for this.

The Browse and Form

The browse for employee has been modified to look like this:

Of course in your application you might have different, dare we say more relevant, information. In this example each of the coded values appear as if they were in the file, or as if they were in a separate related code file. Only the programmer knows that no relationship exists in the dictionary.

The update form for employee looks like this:

When a lookup is requested we get a screen similar to this (for Pay Type)

Using the new Templates

In parts two and three we only needed to use templates in the form. With this dictionary configuration, we need to use them both in the form and in the browse. We use one template for the form, another for the browse.

Lookup4 with a Form

We use Lookup4 is a similar fashion to how we used Lookup2. However, we also need to specify what kind of lookup type we're looking for. For the additional lookup fields, we would see the following when pressing embeds:

We'll use the same template five times. For the employee job title, the prompts will be filled out like so:

Note that the template gives us choices for Lookup Type. It will fill in the appropriate value for this foreign key. Careful coordination between the template writer and the person entering this coded information is necessary here so that correct values are set.

The prompts for Supervisor's Job title would be filled out exactly the same with the exception of the Local Entry Field and the Foreign Key field where supervisor fields would be used:

Prompts for Pay Type look like this:

Prompts for Vehicle Code look like:

Prompts for Primary Language look like:

Support Template for the Browse

Since we have eliminated the relationships in the dictionary, we need to retrieve the code values for our browse. We'll put them in local variables similar to what we did with our update form.

We'll populate the browse with these local variables.

Our browse embeds now look like this:

The filled-in template for retrieving Employee Job title looks like this:

The template for Supervisor's Job title produces:

The template for Pay Type produces:

And the template for Primary Language is filled out:

The Lookup Browse

We modify the lookup browse to restrict values shown to the global value set by the template.

Advantages

Relational Integrity is maintained for all relationships in this example. In addition, multiple relationships are simulated between two files. The overhead of additional keys is eliminated. A common lookup procedure can be used for all lookups. Using a common code file reduces the need for a multiplicity of code files within an application.

Disadvantages

Since the relationships are not coded in the dictionary, if report writer reports are created, this relationship will have to be created. Also, if we make the template "programmer-friendly" in selecting the "code file type, whenever a new code type is added, the templates will need to be changed.

Summary

Multiple relationships between two files can be programmatically managed even though the relationships do not exist in the dictionary. Templates maintain this integrity and facilitate a consistent look and feel.

Expanding the envelope

In a production example, I would modify the lookup procedure to include the name of the code class. This looks like a separate lookup exists for each type of code we deal with.

Printer-friendly version

Copyright © 1999-2009 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942