Retrieving FlowFields by SQL-Server ODBC

JonnyD

Hi everybody!

I was browsing through this forum for some time but I didn't find a helpful hint to a solution concerning my problem.

I have a Navision-SQL-Database and I'm using Microsoft Dynamics NAV 4.0 SP2.

Retrieving the values of FlowFields (SIFTs) by using the "N/ODBC" in a Native-environment is no problem but as I have an SQL-Database I've to use the SQL-Server-ODBC-Driver.

How can I access the value of FlowFields via SQL as those Fields are like "virtual" and can't be seen when I look at the definition of the Table via the SQL Server Enterprise Manager or the SQL Server Managament Studio.

Is there a kind of "how to"-instruction to solve my problem and also get the FlowField-values by the use of the SQL-ODBC-Driver?

Thank you!

Regards,
Jonny

SteveO

Hi JonnyD,

Unfortunately in SQL the fields don't get stored in the table definition. For each Index that has SumIndexFields set to be maintained an extra table is created that stores the values for each combination of IndexKeys. When a flowfield is told to be calculated it reads this SIFT Table and sets filters so it reads from the correct buckets.

You can either copy the behaviour of what Navision does when it calls SQL (ie read the SIFT Table) or you can SUM directly from the table that the flowfield is based upon.

amer

Huh, it is not so simple to do. All info about how SIFT is implemented on SQL Server you can find in eg. "Application designer's guide", Chapter 20(since Navision Attain, I think).What you can see in SQL Server Enterprise Manager is eg. table <Company Name>$17$0 that maintains values for "G/L Entry", key "G/L Account No.,Posting Date". F-fields are part of the key (depending of SIFT level/granularity) and S-fields are the fields that you calculate (eg. Amounts, Quantities). You should do some programming (not only SELECT queries) to implement algorithms for getting/calculate right values.

Regards,
Amer

SteveO

If you run Profiler you can see the SQL that Navision uses (there is a SUM in there somewhere).

I never said it would be simple

But once you know which SIFT Table to read and the fields that make up the table in question, it should be entirely possible to read the correct values. Unless of course the structure of the table is likely to change which could throw a spanner in the works.

kriki

SteveO wrote:

If you run Profiler you can see the SQL that Navision uses (there is a SUM in there somewhere).

I never said it would be simple

But once you know which SIFT Table to read and the fields that make up the table in question, it should be entirely possible to read the correct values. Unless of course the structure of the table is likely to change which could throw a spanner in the works.

I think (didn't check it) it is also possible there is no SUM in it. This because if you search in the SIFT-tables, if the SIFT-index is exactly what you need, SQL must only read 1 record.

amer

Hello!

I think (didn't check it) it is also possible there is no SUM in it. This because if you search in the SIFT-tables, if the SIFT-index is exactly what you need, SQL must only read 1 record.

If you have Case 1 it would be possible to avoid SUM but because of Case 2 when you don't have precalculated rows for every possible key value set, SUM is therefore always used.

GLEntry.SETCURRENTKEY("G/L Account No.", "Posting Date");
//Case 1
GLEntry.SETFILTER("G/L Account No.",'138000');
GLEntry.CALCSUMS(Amount);
//Case 2
GLEntry.SETFILTER("G/L Account No.",'1..5|12345');
GLEntry.CALCSUMS(Amount);

Regards,
Amer

kriki

[Topic moved from Navision Attain forum to SQL General forum]