Calculation using FilterValues and List
I've been using the Six fields in 2 different tables (Combination / Sequence / VList / CFilter / SFilter / SList)
Table 1 has 2,000 records while Table 2 has 18,000 records
Table 2::Combination = A indexed field of numbers
Table 2::Sequence = A indexed field of numbers
Table 1::VList = A calculated list of numbers using the List Function
Table 2::CFilter = The filter values of the field Combination by the expression FilterValues ( Combination; VList )
Table 2::SFilter = A calculated field returning the field Sequence where Cfilter is not blank, using the IF function IF( CFilter="";"", Sequence)
Table 2::SList = A Calculated list of the field SFilter using List Function using the appropriate X relationship
The reason i have two tables is because I index the fields to speed up the process of looking up the final information without it being slowed down with so many calculations, and its faster to only index 2000 records vs 18000. So what I am looking to do is chop down the need of six fields into 4. So all of the calculation are done on Table 1 and I can use the ValueListItems Function to reference the two indexed fields, Combination and Sequence, from Table 1.
Combination / Sequence / VList / (CFilter, SFilter, SList) =Filtered
So what I would Like to do is be able to use something like:
Filtered = FilterValues(VList;ValueListItems( Get ( FileName );"Combination"))
But add something to it so that it will return the Field Sequence as a List where VList and Combination are a match.
I tried doing it this way but got stuck on the part of returning it as a list, which is important because Table 1 only has 2000 records which this calculation would still need the whole 18000.
Filtered = List( IF( FilterValues( VList; Table 2::Combination)="";"";Table 2::Sequence))