Recursive Lookups

Posted June 1 1999

Printer-friendly version

Why Recursive Lookups?

Two files may be related on a single key only. Thus the Relational Model; thus the Dictionary Editor. Real world application needs, however, are not always easily subsumed by the Relational Model.

Retail inventory provides an excellent example. A "package deal" (a kit or Bill of Materials) is an inventory item for the purpose of sales reporting. It is not a physical item but is a composite of several existing physical inventory items.

At data entry, an Inventory record will be created and designated as a kit. Then (other) Inventory items will be selected for inclusion in the kit. Selected items are stored in a KitItems file. To keep the items in the package together, a parent-child relation needs to be established.

If the Inventory file has unique key, call it ItemNbr, it will be used to link the Inventory and KitItem files.

Now, the wrinkle. On a kit's update form, there will, obviously, be a browse of its components. What should be displayed in this list? The ItemNbr for the parent record, obviously not. The component's own Inventory ItemNbr, description, unit cost, unit price and how many units are included in the package come to mind. These five fields plus the parent's ItemNbr makes six fields for the KitItem record.

But wait! Only the parent ItemNbr, component ItemNbr and quantity are actually required in the child record. All other fields are already present in and can be lookup from the Inventory file. In fact, because costs and prices do, now and again, change, storing these data in the KitItem's record courts problems; when a cost or price changes, the component will not reflect the new data and a cost/price of the kit will be wrong.

So KitItems will be composed of ParentItemNbr, ChildItemNbr (the original Inventory ItemNbr) and QtyInKit. Description, unit cost and unit price will be looked up from Inventory.

But wait! There are two rather significant problems with this.

First, and the lesser problem, is that the existing relation from Inventory to KitItems (something like INV:ItemNbr -->> KIT:ParentItemNbr) cannot be used to get the lookup data from the Inventory file. The existing relation can only get those data for the parent record. If you populate any of these fields to the component list, all you will get is the parent's data. A second relation (something like KIT:ChildItemNbr <<-- INV:ItemNbr) is required so that each component item can link to the correct data (Relational Model notwithstanding).

The second, and more difficult problem, is that this scenario requires a recursive lookup. That is (hang on, this gets complicated), a file must lookup into itself. How is that? The kit is an Inventory record and it is in access on the update form. The descriptions, costs and prices of the components are found in other Inventory records. So, either the form needs to access the Inventory file multiple times simultaneously or the Inventory file needs to be related to itself in a variant type of many-to-many relation.

I told you this was complicated.

Two possible solutions immediately come to mind. First, the app could maintain a second, mirror, copy of Inventory. A relation between the mirror file's ItemNbr key and the KitItem's ChildItemNbr key easily disposes of the lookup problem. The code necessary to do this is neither especially difficult nor very lengthy. Second, the Inventory file can be mirrored at runtime in a queue. A queue would be lightning fast, once it is built, and provided it doesn't overflow into the swap file.

Both "solutions" are quite simply dumb. No one in his or her right mind would adopt either as a "solution of choice." And, I do not see the need to argue the point. Yet, Windows with its tabbed dialog boxes makes this kind of problem very real.

What would be ideal is a mirror file without the disk space usage or the I/0 on maintenance. A queue without the memory usage would be nice too. Better yet would be an on-demand queue of only that part of the file needed to accomplish the lookup.

Might as well ask the government to declare a tax holiday.

Back to Basics

Maybe that holiday isn't quite so far off. If you think about it, all that is required is two record buffers for one file.

How can two buffers solve this problem? The update form obviously uses one. But the browse shows many records.

Wrong.

A browse, indeed any Clarion procedure, accesses no more than one record at time. This is part of Clarion language at this point in its evolution (there is work underway on changing this but this is well down the road).

Only one? Time to check the Language Reference Manual.

A file declaration requires a Record structure. The Record structure creates the record or file buffer. There is only one Record structure per file therefore only one record buffer per file. Therefore, no more than one record can actually be accessed per file (per thread). Q.E.D. Read that again.

A browse box accesses a file. Therefore, a browse box can access only one record. Q.E.D.

In fact browses do not display file records at all. This is a pivotal Clarion concept. A browse procedure reads the file and creates a View. The View, a sort of memory file, is used to fill a queue. This queue has only the number of entries that will fit in the window's list area. It is not the file being displayed but the queue.

So, if there were two buffers; i.e., two Record structures, the form could contain a browse, which looks up other items in the "same" file. Q.E.D.

A Tale of Two Buffers

If two buffers referred to the same physical file on disk, the kit form and Inventory lookup would be do-able. So would looking up within the same file (e.g., looking up a supervisor's name from an employee record).

A Record structure can be declared only within a file declaration (it is a specialized type of Group). Therefore, to get the second Record structure, a second file declaration is required.

Creating a second file, a copy of Inventory for example, in the Dictionary Editor is easy enough. Just copy, paste and update the Prefix and Label.

The trick is to ensure that both declarations refer to the same physical file.

This, too, is easy enough. Both declarations must use the Name attribute. It is preferable that both file declarations use the same Name, whether a variable or hard coded entry. (Different variables can be used but must be initialize together and to the same values. If the Name is hard coded, there is no option.)

To create the browse on kit components, create the required relationship between the "mirror" file and KitItems and lookup the "missing" fields from the "mirror" file. To lookup a supervisor from an employee form, create the lookup procedure from the "mirror" file. Big grins all around.

Caveat

An application built from such a dictionary will indeed work substantially as expected. However, if file updates are permitted through both file interfaces, the application will begin to behave badly. Since the Application Generator has no way of knowing that both Record structures point to the same physical file, it will omit all concurrency code. This means that two forms may update the same record without the "changed by another station" warning. Whoever hits Enter last, overwrites the record.

Concurrency checking is provided by the FileManager and, here, where one FileManager is needed, two are instantiated.

I have intentionally come on a bit of a round-about course. Starting with a description of what I want the program to do, I moved through what is required to get the desired behavior. For these requirements, I sought Clarion statements and functionalities to meet those requirements.

Yet it seems as if there is a brick wall here. It seems as if I need to override one FileManager with another. And that is beyond me.

The fact is that Topspeed provides a solution for all these needs in a single keyword. "ALIAS" provides two file declarations, two Record structures and buffers but only instantiates one FileManager.

I took the circuitous route to see that we understand why ALIAS is important and what it does for us.

Creating an Alias

The Dictionary Editor makes creation of ALIASes simplicity itself, just press the "Add Alias" button on the Dictionary Editor's main page.

This will call the ALIAS worksheet (Figure 1).

Figure 1

If you are not already using the NAME attribute on the file to be ALIASed, you will receive a message telling you that it is expected. It is possible to complete the ALIAS worksheet without a NAME. Don't.

There is no particular reason to use a variable for the NAME, unless you wish to. However, if you are not using a variable, you must specify a DOS file name. The Clarion default is to use the file LABEL, so that is what you should probably do also.

When you complete all the prompts, your dictionary will look like Figure 2:

Figure 2

It is extremely important to note that if you do not complete the NAME attribute of the base file, your dictionary will still look like Figure 2. However, when you run the app, a file will be created on disk for the "alias" (if you have FILE CREATE turned off, you will get an error message). Of course, this defeats the purpose: Using ALIAS is supposed to work with a single disk file.

Finally, given that the original purpose of ALIAS was to permit multiple relations between files, you will find that the ALIAS inherits the base file's fields and keys but not its relations. You may create any new relations needed, including relations to files already related to the base file. The idea is to use different keys to create new relations. For example, the second relation between Inventory and KitItems can be created to an ALIAS of Inventory. A file can be related to itself through its ALIAS.

When to Alias

The core concept is the Record buffer. Buffers are created per Record structure per thread when the file is opened (the buffer is destroyed when the file is closed or the thread terminated -- this is part of why mixing ABC file handling with traditional file handling is not ... highly recommended). An ALIAS, therefore, will also allocate its buffer per file, per thread.

Translation? If you need to access records in the same file twice on the same thread, you may need to use an ALIAS. If both accesses are in one procedure, as in the kit/components browse, you certainly need an ALIAS. If you are looking up another record in the file you are updating, you quite probably need an ALIAS (releases prior to C5B, certainly do). If you need (not want,need) two relations between the same two files, you need to ALIAS.

The there is a price of course. ALIASing takes memory and impacts EXE size. But if you have carefully analyzed and optimized your data structures and you still need what ALIAS has to offer, nothing else will do.

Download the source

Article comments

Post a comment

You must be logged on to post comments.