FlowField Filter running terribly

amadman114

Howdy,
In the Item Ledger Entry table I have two slow flowfields: "Cost (Actual) Excl. Depr." and "Cost (Expected) Excl. Depr."

They each have their own version of the CalcFormula:

Sum("Value Entry"."Cost Amount (Actual)" WHERE (Revaluation Type=FILTER(<>Depreciation),Item Ledger Entry No.=FIELD(Entry No.)))

The issue is, that the Revaluation Type=FILTER(<>Depreciation) part of this calcformula is slowing the form down to a crawl. I've tried about 50 different ideas to do with the keys in the Value Entry table, to no avail.

Does anyone have any idea what key(s) I need to set to speed up this table? The table loads almost instantly if I remove the filter.

Thanks,
Dan

Miklos_Hollender

The Item Ledger Entry for example, has a field called Positive, even though it is really a redundant information, and you can try doing the same. I.e. whenever this field is set, like, at posting, also set a Boolean field called Depreciation. And use it in the FlowFilter.

I don't know why this happens to you, must be some SQL quirk, but if they do this kind of thing you too can try.

afarr

For that FlowField to work, you must already have a key in the table with fields Item Ledger Entry No., Revaluation Type, and "Cost Amount (Actual)" as a Sum Index Field.

Of course, if the fields are in that order, then the key will not be helpful (because each Item Ledger Entry No. will usually appear on just one or two records), so make sure that "Revaluation Type" is the first field that appears, so the key is:
Revaluation Type, Item Ledger Entry No.