Requirement
Let’s say we want to fetch incremental data from BC for CRM using Kingswaysoft.

Details
While working with CRM and BC integration using Kingswaysoft, let’s say we want to get incremental data from Navision, for example records modified in last 2 minutes or so. In that case we need to have a datetime field in the NAV table which we want to retrieve. When we have datetime field in nav its data type is datetimeoffset(7) so while we want to set this dates we need to make similarly format to fetch data.

So first we need to declare two string variable which we will be using to store dates specific to nav datetime format. let say we create
1. LastRun – To store last integration run details
2. CurrentRun – To store current integration run details

Based on this variables we will fetch data which is modified between these two datetime. Now using expression (We are using Premium Derived Column) we need to setup date like below

CurrentRun should be setup like below

WriteValueToVariable(@[User::CurrentRun], (DT_STR,4,1252)DATEPART( “dd” , getdate() )+ “-”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mm” , getdate() ), 2) + “-”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “yy” , getdate() ), 2) + ” ”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “Hh” , getdate() ), 2) + “:”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mi” , getdate() ), 2))

LastRun should be setup like below

WriteValueToVariable(@[User::LastRun],(DT_STR,4,1252)DATEPART( “dd” , getdate() )+ “-”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mm” , getdate() ), 2) + “-”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “yy” , getdate() ), 2) + ” ”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “Hh” , getdate() ), 2) + “:”
+ RIGHT(“0” + (DT_STR,4,1252)DATEPART( “mi” , dateadd(“mi”,-02,getdate()) ), 2))

In above example I am taking 2 min gap to fetch data. Now under NAV source editor we can add query like below to fetch our table

Summary
This is how we can fetch incremental data from Navision suing datetime.

Hope it will help someone !!
Keep learning and Keep Sharing !!