Ab Initio Lookup Files

A lookup file is any file which you wish to use as a reference in a graph. It's one of the few components which is generally not connected to any other component.

There are only two rules which you need to bear in mind when dealing with lookups. Firstly, lookups have to fit into memory. This implies that a lookup file should contain the bare minimum of data required to perform its task. Strip out unwanted fields and duplicate records, especially if it's a big file to begin with.

The second rule is that you can't create a lookup file in the same phase as you reference it. You can, however, create your lookup file in an earlier phase and then reference it. This technique is used a lot but be carefull that you don't overuse it. Where the same lookup data is required for two or more graphs in a suite, it makes sense to have a seperate initialiser graph that creates the lookup data in a single pass. This can represent a significant saving in execution time, especially when the source of the lookup is a database table.

Once you have your lookup file in place, you can use it anywhere that accepts DML code. Most commonly, it's used in Filter by Expression components as shown in the example. The lookup() function takes the name of a file plus one or more keys. If the keys are not found in the lookup file, then the function returns NULL.

By default, if the keys are found in the lookup, the entire lookup record is returned. Thus, suppose we have a lookup record containing the following fields…

 CustomerID, SalesArea, Code

and the key is set to CustomerID. The call

 lookup("Lookup File", "KLNY")

might return a record like

 "KLNY", "SOUTH SHIELDS", "TXLN-01"

with the data loaded into the appropriate fields of the lookup record. However, you can just define a single field to be returned with the standard DML 'dot' notation as in

 lookup("Lookup File", "KLNY").Code

which would return the single value such as “TXLN-01”.

You can, of course define multiple keys for a lookup. Thus you could legitimately issue the call

 lookup("Lookup File", "KLNY", "DERRY").Code

It should go without saying that you can replace the quoted strings shown in the example calls with field or variable names.