How Flow field data stores in SQL?

smc

Can any one help me in,

How Flow field data stores in SQL?. What I mean is I want to see the flow field for customer LCY in SQL? Where can I see?

Slawek_Guzek

Check flowfield definition (at NAV table level in Object Designer) and you'll see from which table data is taken, on which index, and what filters you need to apply.

Then you need to find corresponding table (or View) in SQL - if for example SIFT uses table 379 Detailed Customer Ledger Entry, and field to sum is defined on index 3, you need to search for CompanyName$379$3 table (versions prior 5.0 SP1) , or CompanyName$TableName$VSIFT3 view (in NAV 5.0 SP1 and further).

Regards,
Slawek

David_Singleton

You can search this site for many posts about this. Mostly by Ar3rn if I remember correctly.

But better is to move to 5.00SP1 executables and use views instead.

smc

How we can find the index inside sift table?

Slawek_Guzek

smc wrote:

How we can find the index inside sift table?

FlowField table definition tells you which field is used to calculate Flowfield value:

for example:
Table 21 Customer, field 61 "Net change (LCY)", Flowfield definition is as follows:

Sum("Detailed Cust. Ledg. Entry"."Amount (LCY)" WHERE (Customer No.=FIELD(No.),Initial Entry Global Dim. 1=FIELD(Global Dimension 1 Filter),Initial Entry Global Dim. 2=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))

Which means that system is summing "Amount (LCY)" field from "Detailed Cust. Ledg. Entry" table in order to calculate "Net change (LCY)" value.`

Knowing that you need to open table 379 Detailed Cust. Ledg. Entry in Object Designer, then view keys definition. You can see there on which keys SIFT the field "Amount (LCY)" is defined (in the example above "Amount (LCY)" is defined in keys 3,4,5,6,8,9,10,11,12. )

You can select any key from the above set. Of course selected key must be enabled. Which key to use - it depends on filters you want to use.

Regards,
Slawek