FlowField filtering in MS Dyn Nav 4.0 (Nav database, not using SQL)

Business Central/NAV User Forum
1

Please be informed, I am new to Navision. Please bear with me if I ask any stupid questions or if I wrote wrong code.

I am trying to generate report from “G/L Entry” table for total material cost and sub contract cost.

In G/L Entry table “Document No.” is the key field and “P-Order No.” is the FlowField.
GL_ENTRY_MatC_Revenue and GL_ENTRY_SubC_Revenue are the C/AL Globals “G/L Entry” Record variables

Material cost works fine (Please find below the code). But I am not able to generate sub contract cost. It keeps running, no stopping, without luck. Can anyone HELP me!!!

//***** Material cost calcuation - Begins ****** //

G/L Entry - OnAfterGetRecord()

CLEAR(GL_ENTRY_MatC_Revenue);
CLEAR(mat_Total_cost);

GL_ENTRY_MatC_Revenue.RESET;
GL_ENTRY_MatC_Revenue.SETCURRENTKEY(“Document No.”);
GL_ENTRY_MatC_Revenue.SETRANGE(“Document No.”,“G/L Entry”.“P-Order No.”);
GL_ENTRY_MatC_Revenue.SETFILTER(GL_ENTRY_MatC_Revenue.Amount,’>=0’);

IF GL_ENTRY_MatC_Revenue.FIND(’-’) THEN REPEAT

mat_Total_cost += GL_ENTRY_MatC_Revenue.Amount;
jobno := GL_ENTRY_MatC_Revenue.“Document No.”

UNTIL GL_ENTRY_MatC_Revenue.NEXT=0;

//***** Material cost calcuation - Ends ****** //

//***** Sub Contract cost calcuation - Begins ****** //

CLEAR(GL_ENTRY_SubC_Revenue);
CLEAR(sub_Total_cost);

GL_ENTRY_SubC_Revenue.RESET;
GL_ENTRY_SubC_Revenue.SETCURRENTKEY(“Document No.”);

GL_ENTRY_SubC_Revenue.SETRANGE(“P-Order No.”,jobno); // since “P-Order No.” is the flowfield, I stuck with it.

GL_ENTRY_SubC_Revenue.SETFILTER(GL_ENTRY_SubC_Revenue.Amount,’>=0’);

IF GL_ENTRY_SubC_Revenue.FIND(’-’) THEN REPEAT

sub_Total_cost += GL_ENTRY_SubC_Revenue.Amount;

UNTIL GL_ENTRY_SubC_Revenue.NEXT=0;

//***** Sub Contract cost calcuation - Ends ****** //

2

If you are filtering on a FlowField, it is going to take a while. Especially in the G/L Entry table which probably has the most records of any table in your database. This is because the FlowField is calculated, not actually stored in the database in the G/L Entry table. So when you filter, it has to calculate the value for every single entry to determine which ones fall within the filter. If you let it sit long enough it will finish.

3

[Y]

Thanks Matt. I have got the result, but it took 15 hours to load for just one month transaction.

I am not sure whether MS attends this issue in newer version.

4

There’s not much to deal with, although SIFT / FlowFields have been improved in later versions. They aren’t really designed to be filtered on. If you need filtering like this you should design it as a normal field and set its value when necessary.