set filter to - INLIST - Visual Foxpro 7

Question

I am working on some legacy code, and I have the following wonderful issue. I am hoping some FoxPro experts can help!

The infrastructure of this legacy system is setup so that I have to use the built-in expression engine to return a result set, so no go on the SQL (i know that would be so much easier!)

Here is the issue.

I need to be able to do something like

PUBLIC ARRAY(20) ArrayOfValuesToFilterBy

SELECT dataTable 
SET FILTER TO logicalField = .T. and otherField NOT INLIST(ArrayOfValuesToFilterBy)

However, I know this wont work, I just need the equivalency...not using SQL.

I can generate the list of values to filter by via SQL, just not the final record select due to the legacy infrastructure constraint.

Thanks!

Answer 1

First, a logical field you do not have to do explicit

set filter to Logicalfield = .t.

you can just do

set filter to LogicalField or set filter to NOT LogicalField

Next, on the array. VFP has a function ASCAN() which will scan an array for a value, if found, will return the row number within the array that matches what you are looking for.

As for arrays... ex: DIMENSION MyArray[3] MyArray[1] = "test1" MyArray[2] = "something" MyArray[3] = "anything else"

? ASCAN( MyArray, "else" ) && this will return 0 ? ASCAN( MyArray, "anything else" ) && this will return 3

If you are doing a "set filter", the array needs to be "in scope" for the duration of the filter. If you set filter in a procedure the array exists, leave the procedure and the array is gone, you're done.

So, you could do

set filter to LogicalField and ASCAN( YourArray, StringColumnFromTable ) > 0

Now, if you want a subset to WORK WITH, you can do a SQL-Select and pull the data into a CURSOR (temporary read-write table) that has the same capabilities of the original table (except auto-increment when adding)...

I typically name my temporary cursors prefixed with "C_" for "CURSOR OF" so when I'm working with tables, I know if its production data, or just available for temp purposes for quicker display, presentation, extractions from other origins as needed.

use in select( "C_FinalRecords" )
select * from YourTable ;
   where LogicalField ;
     and ASCAN( YourArray, StringColumnFromTable ) > 0;
   into cursor C_FinalRecords READWRITE

Then, you can just use that...

select C_FinalRecords
scan
   do something with the record, or values of it...
endscan

or.. bind to a grid in a form, etc...

Answer 2

The INLIST() function takes an expression to search for and up to 24 expressions of the same data type to search.

SELECT dataTable 
SET FILTER TO logicalField = .T. AND NOT INLIST(otherField, 'Value1', 'Value2', 'Value3', 'Value4')

Answer 3

I am making some assumptions here, that what you want to do is create a filter with a dynamic in list statement ? If this is correct have a play with this example :-

lcList1="ABCD"
lcList2="EFGH"
lcList3="IJKL"
lcList4="MNOP"
lcList5="QRST"

lcFullList=""
lcFullList=lcFullList+"'"+lcList1+"',"
lcFullList=lcFullList+"'"+lcList2+"',"
lcFullList=lcFullList+"'"+lcList3+"',"
lcFullList=lcFullList+"'"+lcList4+"',"
lcFullList=lcFullList+"'"+lcList5+"'"



lcField="PCode"

lcFilter="SET FILTER TO INLIST ("+lcField+","+lcFullList+")"

The results of the above would create the following filter statement and store it in lcFilter

SET FILTER TO INLIST (PCode,'ABCD','EFGH','IJKL','MNOP','QRST')

you can then use macro substitution

Select dataTable
&lcFilter 

Bear in mind that there is likely to be some limitations on how many items you can define in a INLIST() statement