# The Sort/Select Processing_tables Table (-v processing)

The sort/selection processing provided by filePro is one of its most powerful features. This function allows you to make one pass at the file before running the actual output format. During this first pass, you can select only those records desired based on virtually any criteria or processing. You can also override the default sorting order of the specified output format from within this table. Sorting can be performed on dummy fields, expressions as well. This can not be done in output processing.

Probably the main benefit of Sort/Select tables are their ability to provide users with an ad-hoc look and feel. You can prompt the user for values, and use these values to control all aspects of how the output is completed. This is much different than running an output format with a simple (or even complex) selection set.

Because you are making a first complete pass of the records, you can accumulate values on the sort/selection table and use these "totals" during the actual report pass. For example, you can accumulate the total of a particular field during the sort/select phase and show the percentage of each record against this total on the output phase. This would be impossible (well, difficult at best) using only output processing as you do not know the total of all the records as you are stepping down through the records to produce your output.

Some people call sort/selection tables V tables or -V tables, because this is how you implement these tables. On the command line or menu action line, you would do something like this:

.../dreport filename -f outputformat -v sortselecttable -a u

This tells filePro to run the -v table first against all records and hand the selected records over to the output phase.

The SELECT command is the single unique most important function on any sort/select table. If you want to include any record in the group handed to the output phase, you must SELECT this record on the -v table. This means the process must actually move through a processing element where the "if" condition is TRUE and there is a SELECT on the "then" line. Records not so SELECTED do not show up in the output.

Here is a simple sort/selection table. Enter the code from the following two screens in the "vidrec" file on a new output processing table called "seldates".

In order to use any dummy fields assigned on a sort/selection table on your output processing table, they must be global dummy fields, and they must be placed on the AUTO table as well. The AUTO table makes them available to, or passes them through to the output processing table.

Build a new output table in "vidrec" called "autorep1". Enter the following data on it.

We are going to use the BALDUE report we built earlier in the "vidcust" file. We are going to use it, however, in the "vidrec" file. Here is how to copy that format out of "vidcust" into "vidrec". Just mimic the steps on the next several screens.

First, bring up "vidcust/baldue" in Define Output.

While it is on the screen, press F to change files and choose "vidrec". Press ENTER.

You will be brought into the "vidrec" file carrying this format, but it will have no name.

Press C and [NEW] to Copy this format to a new name in this file.

Give it the name "receipts" and press ENTER. You will see the following. The format is now named and ready for use.

The fields in this file, "vidrec" are different from those in "vidcust" where this format came from, so you will have to modify the sort of this format to be applicable here. Do this, by pressing U to update the format, then F8 for the options dialog, then S for Sort criteria. Enter the following data.

Modify the output format to look like this.

Put this new report on the "video" menu.

Enter the following data.

Use this menu to try the new report.

Choose a date range that will find some of the receipts you've been entering. First, the starting date.

Then, the ending date.

The report should look something like this.

In many cases, there is a powerful way to enhance sort/selection processing. You can speed up the selection of records from large files GREATLY by using the "lookup -" (lookup dash) feature of filePro.

Change the processing for "vidrec/vdates" to look like the following 4 screens.

Change the menu item for this report to read as follows.

Try this code out, however, you will not see ANY speed difference with only a handful of records. When you run such a "lookup dash" table against a file with many thousands of records, you will be astonished at the speed increase in selecting records over conventional methods.

A brief description of how lookup dash works on the sort/select table is this. Lookup dash "moves" you to the record indicated by the lookup key. The first test tells filePro to move you to the first record that meets the criteria or the next highest match. Since we are using an index to run this output, each record will come in order and the process will continue SELECTING records that fall within the criteria. As soon as the first record that does not match the criteria is reached, filePro is told to do a lookup dash to the very end of the index. It does this by using a key that is the highest possible value for the index and then finding an equal or next lowest match to this. By doing this, you are guaranteed to find the very last record in any index. Since this record will NOT match the original criteria, the process just ends and there are no more records to process. By cutting out only the records that match the criteria from an entire index, vast savings in time are obtained.

Let's build another sort/selection table. On this one, we will accumulate a total of the Balance_Due fields selected so it can be used on the output processing table. We will also query the user as to the status of records for which the report is to be pulled, (O)pen or (C)losed.

Enter the code from the next 3 screens onto an output table in "vidrec" called "vstatus".

Add the following to the "vidrec/autorep1" table, so that the variable "st" can be passed to the output processing table.

Put the following on the "video" menu, so we can test this report.

Before running this output, we must modify the format "vidrec/receipts" and its processing table to calculate and display the percentage each record's Balance_Due is of the total.

First, add the following to "vidrec/receipts".

Now modify the report format itself to show the percentage.

Use the menu item we added previously, to try the report.

It should look something like this.