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

**URL:** <https://www.dynamicsuser.net/t/flowfield-filtering-in-ms-dyn-nav-4-0-nav-database-not-using-sql/31746>\
**Category:** User Forum\
**Created:** [July 7, 2011, 4:31pm UTC](https://www.dynamicsuser.net/t/flowfield-filtering-in-ms-dyn-nav-4-0-nav-database-not-using-sql/31746 "2011-07-07T16:31:28Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![somasundaram](https://avatars.discourse-cdn.com/v4/letter/s/977dab/32.png) [@somasundaram](https://www.dynamicsuser.net/u/somasundaram)\
**Post date:** [July 7, 2011, 4:31pm UTC](https://www.dynamicsuser.net/t/flowfield-filtering-in-ms-dyn-nav-4-0-nav-database-not-using-sql/31746/1 "2011-07-07T16:31:28Z")

</div>

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 \*\*\*\*\*\* //

---

<div class="post-metadata">

**Author:** ![MattTrax](https://sea1.discourse-cdn.com/flex023/user_avatar/www.dynamicsuser.net/matttrax/32/9408_2.png) [@MattTrax](https://www.dynamicsuser.net/u/MattTrax)\
**Post date:** [July 9, 2011, 1:39pm UTC](https://www.dynamicsuser.net/t/flowfield-filtering-in-ms-dyn-nav-4-0-nav-database-not-using-sql/31746/2 "2011-07-09T13:39:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![somasundaram](https://avatars.discourse-cdn.com/v4/letter/s/977dab/32.png) [@somasundaram](https://www.dynamicsuser.net/u/somasundaram)\
**Post date:** [July 11, 2011, 1:21pm UTC](https://www.dynamicsuser.net/t/flowfield-filtering-in-ms-dyn-nav-4-0-nav-database-not-using-sql/31746/3 "2011-07-11T13:21:30Z")

</div>

[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.

---

<div class="post-metadata">

**Author:** ![MattTrax](https://sea1.discourse-cdn.com/flex023/user_avatar/www.dynamicsuser.net/matttrax/32/9408_2.png) [@MattTrax](https://www.dynamicsuser.net/u/MattTrax)\
**Post date:** [July 11, 2011, 1:46pm UTC](https://www.dynamicsuser.net/t/flowfield-filtering-in-ms-dyn-nav-4-0-nav-database-not-using-sql/31746/4 "2011-07-11T13:46:54Z")

</div>

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.
