Virtual Software Systems - the FilePro Database Applications Specialists

Tip #25 - Date Range Scan/ Select Table

One very common scan select table is used to select items between two dates. This type of table can be saved in a library and used over and over again when this type of selection is needed.

Background: A scan select table can only be used from a User Defined Menu. To create the table select Define Processing > Enter the filename > 3 - Output Processing > Now the filename I like to use would be the same asthe report it will run with with the added letter v. For example, I need a select table for a report called "list" then I will call the scan/select table "listv". Following this same logic, if I need a special automatic table for this report I will use the name "listy". A directory listing will show them together. The items are executed with a menu line that would look like this:

/fp/dreport filename -f list -v listv -y listy -a

getenv("PFCMARK") will give you the year that your hundred years start. For example, if PFCMARK=10 then your hundred year range is from 1910 to 2009. So to get the oldest date possible I used dt(8,mdy/)="01/01/"{getenv("PFCMARK")

To get the latest date that is possible, the expression mod(getenv("PFCMARK")+"99","100") will return the number 9 and the year will be 2009.

MOD function - This function returns the remainer of one number divided by the second. For example, mod("221","10") would equal "1". 221/10=22 remainder 1

So if PFCMARK=10 then mod(10+99,100) is mod(109,100) and the answer is 9. If PFCMARK=50, then mod(50+99,100) is the same as mod(149,100) or 49.

Cut from next line down.

:' written by Nancy Palmquist:'use to select all items between two dates:

:fd ne "":goto pick:

top::input popup fd(10,mdyy/,g) "ENTER FROM DATE (mm/dd/yyyy) (\KZ for Earliest) >":

:@sk="brky":exit; end:

:fd="":dt(8,mdy/)="01/01/"{getenv("PFCMARK");fd=dt:

::input popup td(10,mdyy/,g) "ENTER TO DATE (mm/dd/yyyy) (\KZ for Latest) >":

:@sk="brky":exit; end:

:td eq "":dt="12/31/"{mod(getenv("PFCMARK")+"99","100");td=dt:

pick:3 lt fd or 3 gt td 'field 3 is a date field:end 'do not select anything not in the date range:

: 'other selections can be added here:select:

::end:

Cut to HERE

If you highlight the code, right click and select copy. Then go to a DOS editor, and open a file, right click and Paste. That will put the file down, then save it as a text file. Copy it to the file you created and call it datev.prc. It should then load into Define Processing and you can verify that it passes the syntax test.

Written by Nancy Palmquist