TIP #9 Lookup DASH Scan/Selections

The scan select table that follows will quickly pick a range of dates from a data base. It is necessary to have an index built on the date field that will be used to select. For example, if Index A is built on field "8 Invoice Date (8,mdy/)", then the following would select all invoices between the dates given.

It is also important to run this report with the options highlighted in green.

/fp/dreport xtran -f invlist -ia -a -v datev -h "Print Invoice List"

Comments to explain the reasons for different commands are highlighted in green.

Picture your file like the following line of X's.

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

The pink X's are before the start date, the yellow X's are the ones between the given dates and the cyan X's are after the given date. The black X's represent the highest date(s) on file. Since the date is not unique (there could be more than one of any date), I have listed 2 X's in black.

Since we are reading the records, via index A, they will be presented to us in indexed order, by invoice date. So at the beginning of the selection we are looking at the pink dates.

Sep 17, 1997 15:41 File Name: XTRAN Page 1

Processing: datev

1 ------- - - - - - - - - - - - - - - - -

If: 'Sample Scan/select process table using lookup -.

Then: 'Change the dates to correct format to match your indexes, (10,mdyy/)

2 ------- - - - - - - - - - - - - - - - -

If: da ne ""

Then: goto pick

3 ------- - - - - - - - - - - - - - - - -

over If:

Then: input da(8,mdy/,g) "Start Date: (mm/dd/yy) > "

4 ------- - - - - - - - - - - - - - - - -

If:

Then: input db(8,mdy/,g) "End Date: (mm/dd/yy) > "

5 ------- - - - - - - - - - - - - - - - -

If: db eq "" or da eq "" or da gt db

Then: goto over 'make sure dates are filled in and da le db

6 ------- - - - - - - - - - - - - - - - -

This checks to see if the record we are looking at (the first pink X) is at least the lowest date of our range. If it is not, the lookup - will jump over all records between the first record and move us to the first Invoice Date greater or equal to the start date (da).

If: 3 lt da

Then: lookup - k=da i=a -ng

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

^ that moves you to the first yellow X

7 ------- - - - - - - - - - - - - - - - -

pick If: 3 ge da and 3 le db ' select all Invoices dated between da and db, inclusive

Then: select;end 'each X will be selected and stop here

8 ------- - - - - - - - - - - - - - - - -

goend If: 3 gt db

Then: ky(8,mdy/)="12/31/99" 'this is critical to get to the file end, Use higher dates

'with 4.5

9 ------- - - - - - - - - - - - - - - - -

When the first X is processed this will be true and the lookup - will be executed.

If: goend and xy eq ""

Then: lookup - k=ky i=a -nl

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

That will jump over all the other cyan X's and move to the end date ^

Now all we have to do is let it process that last couple of records and end normally.

10 ------- - - - - - - - - - - - - - - - -

If: goend

Then: xy(1,,g)="Y" 'mark if you already moved to the last record

This will mark that one move to the end was done. Just drop off the end of the index now.

11 ------- - - - - - - - - - - - - - - - -

If:

Then: end

This tip posted on September 23, 1997 by Nancy Palmquist. Your comments and suggestions are gratefully accepted. If no one is reading this, I will direct my efforts elsewhere, so let me know if you are finding this helpful. I will be looking for topics that can be covered in a page or two to use for future tips.