# Lookup Dash

Usually when you do a "lookup" it is because you want to retrieve data from, or place data into that "looked-up" record. Usually you look up records from other files. Occasionally you must look up records in the same file you are "standing in", but still, you will want to retrieve some data or write some data to the record youve looked up. Not so with "lookup ". This special lookup doesnt give you a chance to retrieve data or place data into the lookup record, it just MOVES you there immediately. What you do at that point is up to you, but you are no longer "standing" where you were. "Lookup " can not move you to a record in another file only to another record in your current file.

After putting the following four lines at the end of any input table (choose any file YOU are comfortable using), then pressing the "G" key will show you exactly what "lookup -" does. It physically (from your point of view) moves you directly to the looked-up record. Do NOT pick a record number higher than the highest record in your file. This is just an example of programming to demonstrate a feature, and it is not completely foolproof. Try this out and then read on.

If:

Then: your processing is above here

If:

Then: end

@keyG If:

Then: input re(6,.0) "Goto what record?"

If:

Then: lookup r=re -e

If:

Then: end

You must modify the above piece of processing to look like the following if you want to have something you can use. The GOTO record number idea is not very useful, but it helps demonstrate graphically how "lookup -" works.

To make this piece of code very useful, modify it to read as follows:

If:

Then: your processing is above here

If:

Then: end

@keyG If:

Then: input re(6,allup) "Goto what Account Code?"

If:

Then: lookup k=re I=a -nx

If: not -

Then: show "@sorry, not on file";end

If:

(You must have index.A built on account code). To use this processing, you could either press "G" while at the Enter Selection prompt of any record, (which would do the same thing as backing out to the index selection for account code and entering it from there) OR, you could access this @key from within processing. Whenever you want the operator to be able to move to another account code, just use:

If:

Then: goto @keyG

Since "lookup -" saves out the record you are standing on, using this idea would be like pressing SAVE for the operator and then backing them out to the account code index. Also, it can be done from a @when leaving field routine which would save many keystrokes for someone who is doing a repetitive updating task amongst varying account codes.

NOTE: "Lookup -" can be used (with caution) on the automatic table. Once I used it on this table to keep people from seeing various records based on their user ID. Its beyond the scope of this discussion, but a description of the process, which you may be able to use, is as follows: An army base had a filePro file that had varying levels of access allowed according to the operators rank. Captains could see captain stuff, corporals could see corporal stuff, privates saw very little and so forth. We gave each user a login ID (on the *nix system) which had a number appended to his name, john200, bill300, mary400 etc. This number represented their rank. I could then use their login ID to govern access privilege to this filePro file. I used the @id system maintained field to match against a list of privileges in the automatic table. If the record contained a 300 (in the access privilege field) and the operator had a 300 or higher in his login ID, we let him stop on the record. If his login ID contained less than a 300, we "lookup dashed" him to the next record in the file. By capturing UP and DOWN arrows and testing which way they wanted to go, along with keeping track of the way we put them into the file in the first place (which index and no browse mode allowed), we were able to offer a slightly more secure filePro than the average bill of fare. Anytime they came to a record they werent supposed to see, the operator was gracefully moved to the next available record. They were not even aware there were records they werent seeing. The managers loved it and have made use of the idea in many other things theyve created since then. -Ed

Ultra Fast Selection With "LOOKUP "

There is no question that the most spectacular use for "lookup -" is in conjunction with scan/selection sets. A picture is worth a thousand words, so picture this...

You have an invoice file with 48,000 records. Each day you generate about 100 new invoices. At the end of the day you want to print just the newly added invoices. Without "lookup ", your invoice printing report takes 6 minutes just to select the correct records for printing. With "lookup -" the same report selects the proper records in 4 seconds! Imagine this kind of savings throughout the day in many different reporting situations. How much time do you spend watching the Records Read/Selected at the bottom right of the screen count up to the top of your file, before you see the Generating Output message in the middle of the screen start counting down? Eliminating (or drastically reducing to near zero] this selection time WILL change your life.

Making this magic happen involves using a scan/selection table (a -v table as we users have taken to calling them) and requires building an index on the field we are going to select by. For future reference we will call this the "scanning index".

Example 1: (Select by one criteria, i.e. status)

First make sure an invoice (or test) file contains a one character "status" field (or flag) that tells us whether the invoice has been printed yet. (Add a 1 character, allup field in Define files to a test Invoice file to serve as this flag.) When you create the invoice, You must be sure (on the input processing table) to fill this field with a "W" to represent that it is waiting to be printed. On the processing for the output report that actually prints these invoices, You must chance this field to a "P" to represent that it has been printed. By selecting all records that have a "W" in this field, we will be getting all the newly created invoices (or those waiting to be printed, whether they are new or not). Obviously once you print these invoices the status field will Set filled with a P" and wont be selected next time... which Is just what we want to happen.

Put the following into an output processing table called "flagsel". (You can name these lines whatever makes more sense for you, we pick names only for their descriptive value to the lessons.)

(This is not the most elegant way to write this table, but it is the clearest way to show what happens when "lookup " is used on a sort/selection table.)

Processing_tables Name: flagsel

If: aa ne ""

Then: goto getit

askit If:

Then: input aa(l,allup,g) "Enter Status Code"

If: aa eq ""

Then: goto askit

getit If: 1 lt aa

Then: lookup k=aa i=h -ng

If: 1 gt aa

Then: hv(8,,g)="Z" set HV to highest possible value index can hold

If: 1 gt aa

Then: lookup k=hv i=h nl Get the last indexed record on file

If: 1 ne aa

Then: end

If:

Then: select;end

To implement this processing, along with your output report that actually prints the invoices, you would use the following command line:

dreport file -f invoice -v flagsel -a -ih

An English description of what happens when you execute this type of processing is as follows. The dreport program gets the first record in the "A" index. Line 1 is NOT executed because at this point "act" is not equal to anything. The 2nd line then asks the operator what status flag to select records by. Line 3 forces them to answer the question and not leave it blank. Line 4 now tests the status field on the record the program is currently standing on. If it is less than the status required, it does a "lookup " to the next greater record in the Index (which is the index we built on the status field). "Lookup -" causes the processing to save out the current record and move to the requested record. Now the processing table runs to the top and starts processing all over again on this record. Now, since "act" is a global variable, it holds its value across record changes. This being true, line 1 is NOW executed and the program bypasses asking the question again. The Process tests the status field again. This "lookup " on line 4 will be executed over and over again until the first record in the index that is not less than the status field is obtained. At this point, line 4s "if" line fails and line 5 tests if the retrieved records status flag is now greater than the desired status. If it isnt, the process falls to line 6 and fails there also. At line 7, the test will be true, assuming you have some records with a "W" status. If line 7 tests false, the records status must match the one desired and line 8 "selects" the record for further processing by the output report which will actually Print the invoice once all this scan/selection is finished. The next record is retrieved and it starts all over again.

The magic trick is hidden in lines 5 and 6. As soon as a record is retrieved from the index that is greater than the desired status, the lookup key is changed to the "highest possible value" for this type of index, and another lookup is done. But this time with the "-l" option, which tells filePro to get either an exact match for the lookup key or the "next lowest value" in the index. Since the highest possible value for an allup field is "Z", we try for this. If it is there, it will fail the next "if" test and the process ends having selected all the required records. If there is no "Z" record the next lowest one is retrieved and the next "if" line fails anyway. Both cases cause us to be moved directly to the LAST RECORD IN THE INDEX. There are no records left to process so the selection process is over. All the "selected" records are then handed to the output table for its processing, one at a time as usual.

As you can see that "lookup -" is jumping from the first record in the index to the first one that matches your criteria, then every record that matches from that point is "selected" for output processing. As soon as the criteria no longer matches, "lookup-" jumps you past every other remaining record in the index to the last record on file, and scan selection just ends normally.

Example 2: (Select by a range of values, i.e. invoice printed date)

Since this idea is SO powerful and time saving, it is worth exploring the myriads of ways it can be used. The next table is very similar to the preceding one but will select a range of dates.

It should be self-explanatory now, and hopefully you can adapt this to other search criteria besides dates.

To make the below table function correctly, use the date the invoice was printed as your scanning index. In this example as your output report prints each invoice, this field must be filled with @td (todays date). By selecting all records that have nothing in this field we will be retrieving all the invoices newly created today. (Obviously once we print the new invoices, this field will get filled and they wont be new anymore... which is just what we want to happen.) By using a date instead of a flag to represent the printed date, we can later reprint all invoices printed on a particular date, or do a report totaling all invoices printed that day, etc. Moreover, we can select a range of dates and do the same things.

Put the following into an output processing table called "datesel", and be sure there is a field to hold printed date in the file. We will again use field 37 to represent this key held. Build index H on this field and use the following line to execute the selection:

dreport file -f invoice -v datesel -a -ih -h "Select By Printed Date"

Processing_tables Name: datesel

If: da ne ""

Then: goto getit

stdate If:

Then: input da(8,mdy/,g) "Enter starting date?"

If: da = ""

Then: goto stdate

endate If:

Then: input db(8,mdy/,g) "Enter ending date? (RETURN=starting date)

If: db = ""

Then: db=da;goto getit

If: db lt da

Then: goto endate

getit If: 37 lt da 37 is printed date field

Then: lookup k=da i=h ng

If: 37 gt db

Then: ky(8,mdy/,g)="12/31/99" set TV to highest value index can hold

If: 37 gt db

Then: lookup k=ky i=h nl get last indexed record on file

If: 37 lt da or 37 gt db

Then: end

If:

Then: select;end

Getting More Out Of It

Once youve mastered this technique and have a few basic tables to play with, try doing more than one selection on the same table. For instance, ask the operator which group of zip codes he wants labels printed for... his response could be 33000 to 39999 and 60000 to 69999 and others. Once you have all of his request, start a loop that puts the first range into variables and selects them. Instead of "ending" when the first range is fully selected, change the variables to the next range pair and "goto" another iteration of the select loop. Do this as many times as the operator gave you parts for the request. You must, of course, specify a limit to how many requests you allow the operator on one table. Store his choices in as many variables as you need or want to allow. Arrays help in managing routines like this.