Introduction
In this post I presented a very simple way to import data from Web Service Response into Business Central Wave 2. However, that approach is not a generic one. Let’s now create a Distance Calculator for Business Central that can be used in real life.
The current post presents a better way to Import XML or JSON data received from any Web Service into Business Central Wave 2. At the end you can find a Github link with all files of the extension.
We will use the same example with the Google Maps API. But now we build a more generic solution (Distance Calculator for Business Central) that allows us to use Google Maps and also for example Bing Maps as well. The code will be able to process the response of any web service that provides data about distances, based on a configuration the user does into the system.
The configuration that needs to be done is the creation of “Data Exchange Definitions”:
Configuration Steps
– In the “Data Exchange Definition” we define row and column definitions (the XPaths from the XML/Json Response for what represents a new row and each column for the row):
*Notice that I added also some fields that don’t exist in the Response received from Web Service call: CustomerNo, AddressCode, Location Code, API Setup Line. These are added by code to the initial response we get..
– Then, for the row, we need to go to ‘Manage’ – ‘Field Mapping’ and map each column to a field in the table where we will import the data.
– We select the Table ID where we import the data (Table 50051 “Customer Location Distance”) and the Mapping Codeunit (in this example 50000. I will come back later and explain more about what this codeunit does. For now just remember that you need to have a mapping codeunit that will take the data from a staging table where it is imported initially and copy it to the table specified in ‘Table Id’ based on the logic you write.)
– In the “Data Exchange Definition” we also need to specify the ‘Writing codeunit’. We can use the existing Codeunit 1203 “Import XML File to Data Exch.”. This codeunit, as the name suggests, imports an XML File to standard Business Central Data Exchange Tables (“Data Exch. Field”):
Result:
– You will say: Andrei, but you said we can also import JSON response, this codeunit is called “Import XML File”. How do we handle this? The answer is that in that situation, before calling codeunit 1203, we first convert the JSON response to XML using something like:
IF DataExchangeDef."File Type" = DataExchangeDef."File Type"::Json then begin
TempBlob.CreateOutStream(OutStr);
IF NOT GetJsonStructure.JsonToXML(ResponseInStr, OutStr) then
GetJsonStructure.JsonToXMLCreateDefaultRoot(ResponseInStr, OutStr);
Clear(ResponseInStr);
TempBlob.CreateInStream(ResponseInStr); //Json response converted to Xml
End;
Overview of the Distance Calculator for Business Central’s functionality logic
– First we setup the “Distance Calculator Setup” and “Distance Calculator Setup Lines”, like in the below example:
This is how we store the Base Url, API Key and a “Data Exchange Defintion” for each web service we want to use.
– Then, we have a codeunit that executes the Web Request using the “Setup Line” we want to use, gets the response as XML and first import data, as you previously saw, in standard Data Exchange Tables. In my example, this is codeunit 50001 “Update Distances”. Here we also add more fields that we need to use and we don’t have in the response received from the Web Service (CustomerNo, AddressCode, Location Code, API Setup Line).
– Then we call function “ProcessDataExchange” on table “Data Exchange Defintion” and this will trigger the codeunit setup as “mapping codeunit” (50000 “Map Distances”). This one, is processing the data from “Data Exchange Field” based on the column mappings specified in “Data Exchange Definition”: inserts or modifies the records in table “Customer Location Distance”.
Result in “Customer Location Distance” table:
How to use the Distance Calculator for Business Central in Real Life
The idea of this extension is to have as input the Address of a Customer and the Address of a Location and Calculate the distance and time between the 2 Addresses. Right now the extension is not completed, but my idea is to use this when a Sales Order is created: to have the possibility to automatically select the Location that is the closest to the customer address and ship the products from that Location.
In case someone wants to implement the Distance Calculator for Business Central and collaborate with me in this regard or even collaborate to push this extension to the market place, please contact me by phone or email:
(+40)732-833-213
[email protected]
All source files can be found on GitHub:
https://github.com/andreilungu/NAV_AL/tree/master/DistanceCalculator
