AnsweredAssumed Answered

Calculation using FilterValues and List

Question asked by AlexXander on Mar 20, 2012
Latest reply on Mar 20, 2012 by philmodjunk


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))