Search code examples
visual-foxprofoxpro

set filter to - INLIST - Visual Foxpro 7

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!

Solution

  • 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...