Sumindex/flowfield calculation on SQL Server

geopap

A third party application needs to read data from a Navision database running on SQL server. The data need to be read directly from SQL server without the interference of a Navision client. How can this application compute flowfield values or sums of sumindex fields? I see that for each key that has associated sumindex fields an extra (bucket) table is created. But what is the recommended way of getting the right information out of this table and computing the relevant sums?
I would expect this would be documented somewhere as a common issue when you have a third party reporting app reading Navision Data from SQL. Any ideas / references?

mjrogers

One way you might want to consider is using temp tables to dump the info into from Navision, using a report for example which would do the calc sums etc., then use the 3rd party app to read from that.

NaviTools.com

Hi,

One approach would be to create a view for such a table and add columns for FlowFields. These would be calculated using SQL (like SUM,...).

It might get complicated, but it is an idea. If needed you can build more complex SQL queries dinamicaly.

You need to manually synhronize these views if there is a change in Navision.

Justin

Hi geopap

I came across the same issue when attempting to report on some navision data using Access. I overcame it by reproducing the queries in Access based on the formula used by the flow field.

I know this is a problems when using native access to SQL server.

Cheers,

Justin


PS: A question to anyone using a Native Database and C/ODBC.

Just out of interest if anyone is using the native Navision databaseare flow fields accessible by C/ODBC ? I seem to recall reading that C/ODBC was not available for SQL Server users?

fb

To see how the Navision SQL client gets flowfield sums from the bucket tables, do the following

• Find or create a form that displays the flowfield value of interest
• With the form open, click on Tools, Client Monitor
• Click the options tab, and verify the 'Show SQL' option is selected
• Click the Start button and then minimize the Client Monitor form
• At the form displaying the flowfield, navigate to the next record
• Restore the Client Monitor form and click the Stop button
• Look through the Client Monitor records for CALCSUMS in the 'Function Name' column
• The SQL statement used to retrieve the value will be listed in the 'Data' column.

For example, the SQL statement that retrieves the "Balance (LCY)" field on the customer card (which is the sum of the "Amount (LCY)" field in table 379 - Detailed Cust. Ledg. Entry) will look something like this:

SELECT SUM("s7"),SUM("s8"),SUM("s16"),SUM("s17"),SUM("s18"),SUM("s19")
  FROM "CRONUS$379$0" WHERE (bucket=1 AND f9='10000')

Note that the SQL client retrieves the values for all flowfields in one go, even though only one is requested. This is a 'look-ahead' optimization -- when one value is requested, others are also likely be requested, so get them all at once...

geopap

Thanks, this was quite enlightening, but still, can one be sure that the bucket number that should be used by the SQL query will always be the same constant? And if I produced an SQL query based on a test database, would the same query with the hard-coded bucket constant work properly in the production server?

fb

I recommend that you read section 20.2 of the Application Designer's Guide ('w1w1adg.pdf' on the Product CD). This section presents a very readable description of the SQL SIFT tables, their relationship to the base tables and the naming conventions used for the buckets and columns.

From this section, you will learn that the column names and constant values used in the SIFT tables are stable, as long as the base table field IDs and key definitions remain the same.

geopap

Thanks!