Back

How to set up a Business Central data source using dynamic date filters

Published: May 08, 2024
Post Author Written by Maksudul Haq

To set up a Dynamics 365 Business Central (BC) data source that filters records modified since the last time the integration ran. The Last modified date field of Items, Customers, Vendors can be used to add an incremental filter condition. I have received several support requests for documented steps to achieve this with SmartConnect.

Configuration

  1. Create two new Global Variables in SmartConnect and set the default date values to an ISO8601-compliant date format (2020-05-07T12:41:29.000Z).
    • GBL_BC_ITEM_DATE 
    • GBL_BC_ITEM_STAGE_DATE
GBL Variables 1
  1. Create a Business Central query data source ( I used ItemCard).
  2. Apply a filter to the query using the “greater than or equal to” condition.
Last_Date_Modified ge GBL_BC_ITEM_DATE
Lastdate modified 1
  1. Create an integration process using the OData query data source.

Note: This process will work with any destination you choose.

  1. Switch to the Tasks tab of the integration.
  2. Create a new Run Script task as a Task that runs Integration Pre Tasks.
  3. Write a script to read the current UTC Datetime and format the date in the required destination system date format. Finally, assign it to the variable GBL_BC_ITEM_STAGE_DATE. You can find examples of using Global Variables in Business Central here.

Integration Pre Tasks run before the data source is queried so we need to temporarily store the map run date in a variable not used in our source query.

//Store current date in a variable
var nd = new Date();
this.GBL_BC_ITEM_STAGE_DATE = nd.toISOString();
return true;
Integration Pre Tasks 1
  1. Save the task.
  1. Add a new Script Task to Tasks that run if the integration Post Success Tasks.
  2. Set the script value to update the GBL_BC_ITEM_DATE variable with the value of the GBL_BC_ITEM_STAGE_DATE.
//assign staging date to variable used in source filter
this.GBL_BC_ITEM_DATE = this.GBL_BC_ITEM_STAGE_DATE
return true;
Integration post success 1
  1. Switch to the Variables tab.
  2. Edit the GBL_BC_ITEM_DATE.
  3. Check the box for Persist Run Value.
GBL Persist error run 1

Enabling this option will Persist the Run Value saved at the end of the map run so it is loaded on the next map run.

Conclusion

This configuration will create a Dynamics 365 Business Central data source that filters records modified since the last time the integration ran.

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

We're on your integration team. Connect with our people and let us know how we can help you.

Get support