(Sort/Selection processing is also called Scan/Selection. Sometimes they are referred to as -v tables.)
There are many folks out there using filePro for massive chores, doing amazing processing that would boggle the mind of a 20 year data processing veteran and forever subdue the heart of a mainframe aficionado. But one thing I rarely see used well in the filePro world is the power of "scan/selection" processing (also known as "-v tables" to us less erudite). These tables replace the selection set usually used to grab only the desired records for output. With a "-v" table you can customize your output selection and present a more user friendly interface for your operators. Dumping someone on to a pre-built or blank selection screen and telling them to fill in the field number for "Invoice Date" and "eq" or "gt" for relationship, than the desired date... is a little cumbersome and filePro just simply has a better way to do it.
Using a "-v table" to select only the records you want processed is easy if you understand the basics. Here is an explanation/tutorial.
When you make a "-v" table you will use Define Processing and design a new output processing table. Use naming conventions like "labelsel", "vinvoice" or "seldates". In other words let the name of the processing table indicate to you somehow that this is a "selection" or "-v" table. This way you wont confuse these special tables with other actual "output" processing tables that are connected to some kind of output format. "-v" tables stand alone and are not connected to any output format.
On a "-v" table you can ask questions and based on the answers received, select the records the operator wants. The following table demonstrates the idea:
File Name: Invoices
Processing: vtable
If: aa ne ""
Then: goto sel
ques If:
Then: input aa(8,mdy/,g) "What invoice date do you want? "
If: aa = ""
Then: goto ques
sel If: 1 eq aa
Then: select;end
Then: end
The "-v" table is activated as follows:
dreport Invoices -f invoice -v vtable -a -u
Lets assume "invoice" in this case is your invoice form. By executing this line you can print invoices for records containing a requested date and skip over other records in the file.
The way a "-v" table works is interesting. Before your output format and processing are run, the "-v" processing table is run against every record in the file. Dreport (the Request Output program in filePro) uses a "-v" table by standing on the first record in the file and running it (the -v table) against one record at a time until the entire file is read. Then, the actual output format and processing are loaded and only the records that were "selected" by the "-v" table are processed. During this "first pass" of the file the "-v" table is testing each record to see if it fits your "selection" criteria. In the case of the example table "...does field number 1 match the date entered by the operator..."
The Big Trick on "-v" Tables
Here is the trick to this table (and virtually ALL "-v" tables). The first record of the file is gotten from the disk, the "-v" table starts on its first line by testing the variable "aa". At this time it is equal to nothing so the "if" line tests false and the processing falls through to the next line. The operator is asked "What Date?" and when he answers with a valid date, the processing falls through to the line labeled "sel". Here, Field 1 (well assume it holds the records invoice date) is tested against the operators chosen date as held by "aa". If filed 1 equals "aa" the record is "selected" and the process "ends". This, (hitting the "end" statement) causes the dreport program to get the next record from the disk. This time and for EVERY OTHER record the first "if" line will always test true since "aa" is a global variable. (This means it holds its value from record to record). Because the "if" line tests true the processing will immediately "goto" the line labeled "sel" where the all-important "selection" test is done again. If this records field 1 matches the date held in "aa" it will be "selected", if field 1 is not equal to "aa" the process falls through to the "end" statement standing alone on line 5 and does not perform the "select;end" on line 4. Dreport moves on to the next record and the same thing happens over and over again until every record has been tested. When this first pass is completed only those records "selected" are formatted by the "invoice" format/processing.
You can do more complicated selections like the following with a "-v" table. It can get as sophisticated as your imagination. The processing shown here should be self-explanatory. See if you can figure out what it will do.
File Name: Invoices
Processing: vtable
If: aa ne ""
Then: goto sel
ques If:
Then: input aa(8,mdy/,g) "What invoice date do you want? "
If: aa = ""
Then: goto ques
If:
Then: input bb(6,.0,g) "Enter customer code? (RETURN=ALL) "
sel If: 1 eq aa
Then: goto chkcust
If:
Then: end
chkcust If: bb = ""
Then: select;end
If: 2 = bb
Then: select;end
If:
Then: end
This table will work just like the first "-v" table asking for the desired date and then it will ask for the desired customer code. If no customer code has been entered then only the date match matters to the selection, but if "bb" has been filled with something, both criteria have to be true for a record to be "selected". (Assume field 2 is the customer code stored on each record) You could choose to print invoices for client "USA2" on "02/05/89" and only those would be printed. This example is used to demonstrate the principle of asking lots of questions on a "-v" table and then selecting records based on the response, it isnt meant to be very useful as it exits for your system designs.
These tables will work with ranges of dates, zip codes, client codes etc. In fact any criteria that can be tested on a selection table can be tested this way, with the added advantage that other processing can be done during the "-v" pass. So that you have the ability to do things like get the percentage of one records value to an entire file, or many more wonderful things that are difficult or impossible without this powerful filePro feature.
How It Works
Output makes two passes of the file when generating output. On the first pass, it selects records and sorts the selected records. (Messages appear: "Reading Keys", "Writing Keys", and "Sorting Keys".) Sort/selection processing is done during the first pass for each record
On the second pass, processing works the selected records in sorted order. (Message appears: "Generating Output".) Automatic and output processing are done during the second pass.
Advantages
Sort/selection processing make the sort and selection operations more friendly. You can put in personalized questions rather than standard screens.
The user can specify additional details to the selection criteria at runtime.
The user can define the sort criteria at runtime and override or add to the standard sort.
The user can define the sort or selection based on criteria not in the current record (as in selection sets). Records can be chosen based on lookups, dummy variables, or expressions.
Percentages and other mathematical functions that require two passes of the file can also be performed.
However, instead of selecting Request Output you must generate the output from a user menu or from the command line. The sort/selection table is specified using the -v flag.
The syntax is as follows:
dreport filename -f formatname -v sortselectname -a
Two special commands are used on Sort/Selection processes, SORT and SELECT. These two commands only function on a -v processing table.
SORTn
Where n is the expression that designates the sort field desired.
Overrides the sort as defined in the output format (but, not subtotal or total breaks).
You can define up to eight sort fields.
SORT is used on sort/selection tables only.
Example
Then: sortn=m
where "n" is the level (a number from 1 to 8), and "m" is the field to sort on (real, dummy, or lookup)
SELECT
SELECT command, a record is selected only if it encounters the SELECT command. If the SELECT statement does not have a true if condition or if it is bypassed (jumped around with other redirection) that record will not be included with those sent through to the output pass.
SELECT is used only on sort/selection tables.
Example
If: aa eq ""
Then: input aa(2,.0,g) "Enter Doctor Code To Print:"
If: 13 = aa
Then: select
If:
Then: end
Note that a global dummy field is used since you want the question to only be asked at the very beginning of the run and not on every successive record.
Passing Data From a Sort/Selection Table to the Output Table
It is possible to pass dummy fields from the sort/selection table to the output table via the automatic table.
Example:
Suppose that on your sort/selection table you prompt the user for beginning and ending dates for invoices to be printed on a report. You use input questions with dummy fields "bd" and "ed". Be sure that on your automatic table (or -y table) that you bring the variables into existence as "global" variables. This will ensure that the output format (and its processing table) will see the value of these dummies. Only variables that have been defined as global on the automatic table can pass values from the sort/selection table through to output.
Example (somewhere on the automatic table)
Then: bd(8,mdy/,g) ; ed(8,mdy/,g)
Calculating Percentages
1. Add global dummy field "rt" and local dummy field "ip" to your automatic processing table.
Then: rt(12,.2,g) ; ip(10,.2)
2. Add a totaling function to your sort/selection table.
Then: rt=tot(14)
3. Add a percentage formula to your output table.
Then: ip=(14/rt)*"100"; print
4. Execute the process from a user menu or command-line statement that includes the "-v" flag and the sort/selection processing table and you will see the "individual percentage" each record is out of the entire "report total". If you were running a report without sort/selection processing first, you would be stopping on each record to do your calculation without knowing what the total value of the whole file is. The sort/selection pass of the file is all important for such things.