Flowfield Performace Problem

bob_up

I have a report which prints the "Net Change" per "Global Dimension 2" per G/L Account.

I read each G/L account then loop through the Dimension Values table for Dimension 2 and calculate the "Net Change" for each Dimension 2 value code for that G/L Account as follows :

"G/L Account".SETRANGE("Global Dimension 2 Filter", DimensionValues.Code);
"G/L Account".CALCFIELDS("Net Change");

The report crawls saying it is 'Summing the G/L Entry table ...' over and over again. This is on a standalone machine with the native database held locally.

If I take out the "Global Dimension 2 Filter" the report finishes in a few seconds.

Is there any way to resolve this ?

ara3n

I suggest to create An analysis View and run the report based on analysis view table.

ara3n

You can also create a new Key based on filters you are setting with sumindexfield for the amount and use that key.

bob_up

Thanks Rashed.

I have created a key with the relevant fields 'G/L Account No., Posted Date,Global Dimension 2 Code' with 'Amount' in the SumIndexField.

Unfortunately this doesnt seem to have helped. Is there something else I need to do to make CALCFIELDS use this key ?

I find this really confusing. If I am looking at the 'Chart of Accounts' and set the 'Global Dimension 2 Filter', the effect is immediate. In other words, the form recalculates the Net Change for each account with no delay. What is the difference in code ?

ara3n

You should try and hit the GLEntry table directly instead of using the flowfield.

bob_up

Is CALCSUMS the right function to use ? Does it use the current key for the table ? I am thinking of

GLEntry.SETCURRENTKEY("G/L Account No.","Posting Date","Global Dimension 2 Code");
GLEntry.SETRANGE("G/L Account No.","G/L Account"."No.");
"G/L Account".COPYFILTER("Date Filter",GLEntry."Posting Date");

IF DimensionValues.FIND('-') THEN
BEGIN
REPEAT
GLEntry.SETRANGE("Global Dimension 2 Code",DimensionValues.Code);
GLEntry.CALCSUMS(Amount);
// ... DO SOMETHING WITH THE VALUE
UNTIL DimensionValues.NEXT = 0;
END;

ara3n

Yes use calcsums on glEntry.

If you use g/l Account you use calcfields

bob_up

Thanks for your help Rashed. I have used calcsums and the performance is acceptable now.

I added a progress dialog to make the wait more tolerable too.

Bob

kriki

bob_up wrote:

I added a progress dialog to make the wait more tolerable too.

Don't update your dialogbox too often. This can slow down a lot.