Reading FlowFields or calculated fields from MS Dynamics NAV just got easier
We are happy to announce that we now have support for reading Flowfields or calculated fields from MS Dynamics NAV 2013 or later (actually NAV 2009, NAV 2013, NAV 2015 and NAV 2016 are all supported).
As Microsoft ended their support for the C/Front interface (last version to support c/Front was NAV 2009) it has become more difficult to retrieve the Flowfields from the NAV database. The most performant way to do this, is to create a new view that joins in the corresponding SIFT index directly in the NAV database. Yes - this sounds difficult, but read on - we now got an easier way to read the FlowFields like Balance of a Customer or Item Inventory.
Creating a new view that joins in the corresponding SIFT index will always be the best performing solution, as the view can be tailored to give you all the fields you need in one query to the MS Dynamics NAV database server (SQL server). If you have the skills to do this and you dont mind maintaining an extra view in NAV when upgrading etc. this will remain the best option.
Retrieving calculated fields with RapidiOnline
However, if you want to keep the MS Dynamics NAV database clean and save time, you can achive the same as mentioned above directly inside the RapidiOnline setup. You can now use our DBLookup function to retrieve these FlowField sums from MS Dynamics NAV. There is even support for setting the FlowFilters so that you can calculate the sum for e.g. a specific date interval.
In newer versions of RapidiOnline the formulas and also the DBLookup's are evaluated on the RapidiConnector, so this new solution also performs quite well as all the querying of the SQL database takes place locally.
Below are some examples of the use of this new feature:
Getting the Balance for each Customer
##DBLookup('SOURCEDS','Detailed Cust_ Ledg_ Entry$VSIFT$4','Customer No_',"No_",'SUM$Amount')
In the above DBLookup we asume that you are reading from NAV Customer table and that we want the Balance for each Customer that we read. We use the SIFT index called "Detailed Cust_ Ledg_ Entry$VSIFT$4" and we filter on the field "Customer No_" with the value from each Customer (the value from the "No_"). We retrieve the value from the field "SUM$Amount". Actually the Select statement that we use will be something like SELECT SUM("SUM$Amount") FROM "Detailed Cust_ Ledg_ Entry$VSIFT$4" WHERE "Customer No_" = 'xxx' (xxx being substituted by each specific Customer No), but you just specify it as above.
Get the Total Sales (LCY) for year 2012 for each Customer
##DBLookup('SOURCEDS','Cust_ Ledger Entry$VSIFT$1','Customer No_',"No_",'Posting Date','##"Posting Date">=\'2012-01-01\' and "Posting Date"<=\'2012-12-31\'','SUM$Sales (LCY)')
In this DBLookup we also read from the NAV Customer table and we want to get the Total Sales (LCY) for year 2012 for each Customer that we read. We use a different SIFT index, this time called "Cust_ Ledger Entry$VSIFT$1" and we filter on the field "Customer No_" with the value from each Customer (the value from the "No_"). Additionally we also need to filter on the "Posting Date" field, but not to a specific date but rather an interval. This is done using our special formula filter (the ## triggers that the rest of the string is set as a fixed filter. In side the filter string, we need to "escape" the single quote character using the \ (backslash) - our formula interpreter will remove the \ but keep the ' (single quotes) which are needed for the where clause in SQL to work. The SQL Select statement will look something like: SELECT SUM("SUM$Sales (LCY)") FROM "Cust_ Ledger Entry$VSIFT$1" WHERE "Customer No_" = 'xxx' AND "Posting Date">='2012-01-01' and "Posting Date"<='2012-12-31' (xxx being substituted by each specific Customer No).
Getting the Inventory for each Item
##DBLookup('SOURCEDS','Item Ledger Entry$VSIFT$3','Item No_',"No_",'SUM$Quantity')
In this last example we read from the NAV Item table and we want to get the total Inventory for each Item that we read. We use the SIFT index called "Item Ledger Entry$VSIFT$3" and we filter on the field "Item No_" with the value from each Item (the value from the field "No_"). The resulting SQL Select statement would be something like SELECT SUM("SUM$Quantity") FROM "Item Ledger Entry$VSIFT$3" WHERE "Item No_" = 'xxx' (where xxx would be substituted by each specific Item No).
What SIFT index to use
The actual SIFT index to use to calculate a specific sum cannot be seen in one specific place and might be different between specific NAV installations depending on NAV version and the level of customisation. You can get some clues to this by looking inside NAV in the Object Designer for the specific table and then you can look at the views defined inside the NAV SQL Database itself (look under views).
To use this new feature, you need to have both the central Rapidi service and the RapidiConnector on version 3.2.92v or later. Please contact our support to arrange an upgrade if needed.
