Is a Dynamics NAV 2013 Page Based on a SQL View Not Updating to Reflect the Latest Data Changes? Here's a Solution.

I ran into an issue with a Dynamics NAV 2013 Page whose source table was a linked object (a NAV table attached to a SQL View) that didn't reflect the current state of the data.  The only way I could to get the page to display the latest version of the data was to use the refresh button on the action tab.

Here is what I discovered....

NAV 2013 in conjunction with the new data caching features on the service tier don't trigger cache updates of data from the view.  I'd guess since the tables are updated outside of the Service Tier NAV isn't aware of them.

Adding SELECTLATESTVERSION to the OnInit or OnOpenPage trigger will essentially have NAV do the same thing as the Refresh function:

SELECT  TOP (@0) "Key","Table_1_Value","Table_2_Value" FROM "Demo Database NAV (7-0)"."dbo"."View_1" WITH(READUNCOMMITTED)  WHERE ("Key">=@1) ORDER BY "Key" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

 

Using the attached script and NAV objects you can create a couple tables, a simple join view, NAV table based on the view and a Page attached to the table.  The script will load the tables with data.  After viewing the Page in NAV, open one of the tables and delete a row.  Open the NAV page again and the data is the same, all 4 joined rows remain in the view.  Now add the SELECTLATESTVERSION command and open the Page.  The data is now updated.

NAV2013_View Caching Demo NAV Objects - ABC Computers.zip

References

 

Microsoft Dynamics NAV Partner Innovia Consulting

Innovia Consulting

Innovia Consulting

Innovia is a multiple-award-winning Microsoft Dynamics NAV and Business Central consulting firm known for delivering innovative Microsoft ERP systems while providing exceptional client-focused service. With five project teams and a dedicated support team, Innovia has the resources to handle all types of ERP projects.

Related Posts