LOOKUP (only 1 (one) lookup per process in Lite)

Syntax:

All the above syntax when used with DECLAREd variables, as follows:

LOOKUP k= r=

lookup dat = (FileName & "@" & QualFile) i=A k=(KeyWord) -nx

lookup zip = zipcodes i=A k=(State & ZipCode) -nx

lookup sys = (SysFile) r=(RecNum) -n

In the above examples: FileName, QualFile, KeyWord, State, ZipCode, SysFile, RecNum and LookupKey, are GLOBAL define variables whose value may be set in processing.

Version Ref: 6.2 (6.1.02 USP)

Enhanced index flag to take an expression.

Example:

ix="A"

lookup myfile = test k=("key") i=(ix) -nx

Version Ref: (6.0.00.01)

Partial lookup flag added to *cabe lookup wizard.

-O on an exact lookup now does partial key matching. This kills a lookup once the beginning of the key value no longer matches the lookup key value.

Example:

aa="CR100"

lookup myfile = test k=(aa) i=A -nxo

The lookup will fail once the current record's key changes where the beginning no longer matches CR100.

This means the lookup, in this case, would match the following records.

CR100A

CR100B

CR10000A

But fail in the following cases

CR101

CR110

And so on.

Version Ref:  Various

NOTE: If an alias is used for a lookup filename, this alias is the only name that can be used to refer to this lookup and fields from the specified record, the filename itself will no longer be recognized.

LOOKUP                               dash (-) in place of filename, moves to the
                                     specified record in current file

NOTE: Using a dash (-) in place of the filename parameter in a lookup statement, causes filePro to perform a very special operation. This syntax will cause the current record to be stored, and moves the user to the designated lookup record, and this record is now considered the "current" record. Processing begins on this record on the processing element immediately following the lookup dash statement. Obviously, lookup dash can only be used to move within the current file, it may not be used to place a user in another file.

LOOKUP can also be used to connect to a record in the current file. To do this, simply use the current file name as the lookup file name. This is considered a normal lookup and data can be moved to and from that record in the usual way. This is nothing like the special operation performed by lookup dash described above. When the current filename is used in the lookup (not a dash), the user is not moved to that other record, rather a simple lookup is performed.

Description:

Connects to another file for purposes of retrieving data from that file or posting data into that file.

LOOKUP is used to temporarily connect two files. The two files are:

1. The current file (the main file, the primary file), i.e., the one you're in when you define or run the operation

2. The other file (the lookup file, the lookup), i.e., the one specified in the lookup statement

LOOKUP can be used two ways: to get data from a file; and, to post (copy) data to a file, or both.

Creating a Lookup

There are three steps (or parts) to consider when creating any lookup:

1. Designating which file to use for the lookup.

2. Designating how to find the appropriate record in that file.

3. Designating what to do if the lookup fails, i.e., is not successful in finding the specified record.

A fourth consideration comes into play after the lookup has been run successfully. This step is not actually part of the lookup statement itself, but comes on processing lines after the LOOKUP line.

4. Designating the fields in the lookup file from which to take data, or those in which to put data, or both.

IMPORTANT: The lookup wizard can be used to prompt you step-by-step for the required LOOKUP parameters. This wizard is accessed by pressing the F5 key while the cursor is on a "then" line. (This key is for PC's and ANSI mode terminals. It will be different for character based terminals, on which it is normally CTRL-R.

IMPORTANT: If you use punctuation in filenames, and you want to do a lookup to such a file, you need to use an alias

Lookup Statements

Record-number, key-field and free-record lookups have different formats and a variety of different options.

For help defining the lookup file statement, use the "define  lookup" option accessed with < F5 >.

For an assigned file name:

       Then: lookup assign=filename r=f -e

Record-number options

       Then: lookup filename r=f -e

where "filename" is the name of the file containing the desired records, "r" indicates record-number lookup, "f" is the field containing the record number in the current file (the value in field "f," is a record number from the other file), and "-e" is the default lookup failure choice, "report an error. "

Free-record options

       Then: lookup filename r=free -e

Keep in mind that the lookup file doesn't have to contain empty records. LOOKUP expands the file as needed.

On UNIX/XENIX systems, you might want to add a "protect" flag (-P) to the statement. The -P flag prevents two users from accessing and changing the same lookup record at the same time. It does so by locking the lookup record as soon as it's accessed; the record isn't unlocked until all processing is done and the new data is saved or until a WRITE statement is done on that lookup.

Syntax:

       Then: lookup filename r=free -ep

Keyfield options

Syntax:

       Then: lookup filename k=m i=o -xe

where "filename" is the name of the lookup file, "k" is the keyfield flag and "m" is the reference field in the current file; "i" is the index flag and "o" can be index A-H, 0-9 in the lookup file; -x is the default match, "exact match," and e is the default lookup-failure choice, "report an error."

Indexes

Keep in mind that the index must be built in the lookup file on the field corresponding to the cross-reference field in the current file. For example, if the key field in the current file is zip code, sort the lookup file's index on its zip code field. For best results, make sure the index sort-key and the cross-reference field have the same edit type.

Match options

X, exact match
G, exact or next greater match
L, exact or next lower match

If you pick the 'less than' or 'greater than' mode, the program looks for an exact match first. If it finds no match, it chooses the record with the value just less or greater than the one requested.

Lookup-failure options

The failure option flags tell the program what to do if the right information isn't found. The options are:

B, fill the field with blanks
N, do nothing (use for processing tests)
E, report an error

The E flag is the only one of the three that returns a "Lookup Failed" error message. The N flag is used to test for lookup failures and resolve them while processing is running.

Note:

If you use the N flag without testing for failed lookups (by adding the statement "If: not filename"), you'll get another error message later, when you try to use the field for which the lookup failed. The B flag returns no error message, just fills the field with blanks. See "Connecting Files: Lookups and Posting" earlier in this chapter for more information.

Changing record positions

Use LOOKUP and a hyphen instead of a file name to change the record position in the current file. (Note that this is not the same as doing a lookup from and to the current file.) This option lets you keep the user in update mode while still letting him pick the record to update next. See example 2 below.

Syntax: Writing Field Assignments

Once the lookup files are specified, you must tell the program into which fields, exactly, the data are to be put. Specifying fields for lookups:

       Then: a=filename(n); b=filename(m); . . .

where "a" and "b" are the fields in the current file into which the data go, "filename" is the name of the lookup file, and "n," "m," etc., are the numbers of real or system-maintained fields containing the data in the lookup file.

Specifying fields for posting:

       Then: filename (n) =a; filename (m) =b ; . . .

where "filename" is the name of the lookup file, "n," "m," etc. are the numbers of real fields into which the data go, and "a" and "b" are dummy, system-maintained or real fields in the current file from which the data come.

Example 1

In the COPY and INPUT examples, your records were simply appended to the end of Archive. With LOOKUP, however, you can archive to particular records. In this set of instructions, you archive records according to customer name.

Step 1

Access the "arch" output processing table in sample file, TInvoice. Change the record-number lookup in element 3 (or 1 if you didn't do the INPUT example) to a key-field lookup, as follows:

       lookup archive k=1 i=b -ex

Save the processing table. If you tried the INPUT example, this is how the revised table should look (screen 13-23):

Screen (x): Copying to Particular Records

Step 2

Build Archive index B on field 1, customer. Also, create a few new invoices using the same customer numbers but different item numbers or quantities as in the invoices already archived. Note the balances due.

Step 3

Run the archival; access Request Output; the file name is tinvoice, the output format is "arch." Sort on field 1, if desired, and select all records. Enter an archive date.

Step 4

Check Archive via Inquire, Update, Add. There should be no additional records, but the balances-due should be different.

Example 2

Suppose that you want to let the user update a series of records without having to exit from update mode each time he wants a particular record. You could write the processing as follows (try it on TClient):

       Then: cc= @rn; input aa(15,uplow) "What name do you want to
             update now? Press RETURN to quit"
       Then: bb=aa { ""
         If: aa eq ""
       Then: exit
       Then: lookup - k=bb i=a -ex
         If: cc eq @rn
       Then: getnext -
       Then: screen 1; restart

This set of elements asks the user what name to find next, switches to the new record (if he asks for the same name, moving the user to the next instance of the name - cc eq @RN, getnext -). Once the record is found, the SCREEN command puts him in update mode and RESTART starts processing from the top of the table.

Restrictions

A lookup statement must be alone on its action line.

Any file name (including an assigned file name) used in a lookup statement must be at least three characters long and should start with a letter.

IMPORTANT: If you use punctuation in filenames, and you want to do a lookup to such a file, you need to use an alias

Example 3

         If: lookup cust r=free -e
       Then: cust(4)=3

Copy field 3 of the current (or main) file record into field 4 of the free record in "cust".

Example 4

       Then: aa="1"
       Then: lookup control r=aa -e
       Then: zz=control(8)

Looks up file "control" using the value in field aa to get a particular record number in the "control" file. Copies the value in field 8 on that record in the "control" file to the dummy variable zz.

NOTE: As of Version 5.7.04, browse lookups are now supported in dreport/rreport