3 Replies Latest reply on Mar 20, 2012 9:32 PM by philmodjunk

# Calculation using FilterValues and List

### Title

Calculation using FilterValues and List

### Post

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

• ###### 1. Re: Calculation using FilterValues and List

its faster to only index 2000 records vs 18000

True, but is is noticeably faster? Indexing is a database operation to create or update an index when: a) a new record is added, b) a record is deleted, c) a field is modified or d) when a sort or find is performed with criteria specifying an unindexed field. a, b and c tend to happen in a blink of an eye in most cases, d) can usually be avoided with proper database design.

Thus I'm not sure that your strategy for "faster indexing" is really worth the effort--especially as 18000 records isn't all that huge a number of records and is ony  6x as many as the 2000 records.

As far as setting up what you want here, I can see that you've really tried to spell things out, but we also need to know how table 1 and table 2 are related to each other and posting the actual calculations used by these fields would make it much easier to see if we can make any suggestions or not.

• ###### 2. Re: Calculation using FilterValues and List

Well there were 500 fields that were used to make the field that I named VList, where I indexed all 500 fields.

There is Section 1 with the fields named Record 1-100

Record 1= If(GetNthRecord(Value;Number)=GetNthRecord(Value;Record Number-1);1;0)

There is Section 2 with the fields named Lookup 1-100

Lookup 1= Lookup(GetField(LeftWords (GetFieldName(Self);2)&" "&"2::"&"Record"&" "&RightWords(GetFieldName(Self);1)))

There is Section 3 with the fields named Sum5 1-100

Sum5 1= GetField("Lookup"&" "&RightWords(GetFieldName(Self);1))+GetField("Lookup"&" "&RightWords(GetFieldName(Self);1)+1)+GetField("Lookup"&" "&RightWords(GetFieldName(Self);1)+2)+GetField("Lookup"&" "&RightWords(GetFieldName(Self);1)+3)+GetField("Lookup"&" "&RightWords(GetFieldName(Self);1)+4)

There is Section 4 with fields named IF 1-100

IF 1= If(GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1))=0;"";If(GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "& RightWords(GetFieldName(Self);1))>0  and  GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "& RightWords(GetFieldName(Self);1)-1)=0 or GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1)-1)="?" ;GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1));If(GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1))>0 and GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1)-1)>0 and GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1)-1)= "?";GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1));If(GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1))>0 and GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1)-1)>0 and GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1)-1)≠ "?";GetField("IF"&" "&RightWords(GetFieldName(Self);1)-1)&GetField(LeftWords (GetFieldName(Self);2)&"::"&"Sum5"&" "&RightWords(GetFieldName(Self);1))))))

There was Section 5 with fields named List IF 1-100

List IF 1= List(GetField(LeftWords (GetFieldName(Self);2)&" "&"3::"&"IF"&" "&RightWords(GetFieldName(Self);1)))

And there is Total List A.K.A (VList)

Total list = List(List(GetField(LeftWords(GetFieldName(Self);2)&" "&"3::IF"&" "&1));List(GetField(LeftWords(GetFieldName(Self);2)&" "&"3::IF"&" "&2));List(GetField(LeftWords(GetFieldName(Self);2)&" "&"3::IF"&" "&3));...List(GetField(LeftWords(GetFieldName(Self);2)&" "&"3::IF"&" "&100)))

The two tables are related to each other through a field named Record Number

Without indexing all of it, it cuts 500 fields to 400 and it looks like it will complete in about 3 hours. Which isn't to bad, but the problem is the second part where I need to use the Fields of 18000 records to filter the results. I had the whole thing together but then it would run the calculations on all of the fields for all 18000 records even though I only need the first 2000 of the first part, and after the second part I would then need to export the data. So, not indexing made the original process take close to 24 hours to complete. And the reason why its complex is because this is only 1 proccess of 97. So I made it so that I don't have to go in and retype the calculations for every process.

• ###### 3. Re: Calculation using FilterValues and List

Sorry, but this looks way to complex to sort out in a discussion forum of this nature. I'm not even sure we mean the same thing when we use the term "indexing". I have so many questions about how and why you have used such an unusual design that I don't know where to begin. If had about a day to look at the database while you explain your design and reasons for it, I might be able to suggest something...