Import Tenant Data

My last post was about how I got the customized data out of the tenant database into Xml files. That tenant database was from a NAV 2016 application.

I have updated the tenant database to Business Central and I need to bring in some of the data from these Xml files.

My first issue was that I needed to make these Xml files available to Business Central. I have been using Azure Blob to store files for some years now. I had both AL and C/AL code that was able to connect to the Azure Blob REST Api, but that code used DotNet variables that is no longer an option.

I did some preparation last year, when I requested Microsoft to add some functionality to the BaseApp. Using that BaseApp functionality I was able to redo my Azure Blob AL code as a clean extension.

I also wanted to put the AL code somewhere in a public place for everyone to see. And GitHub is the default code storage place. I created a project for Business Central AL.

I am hoping that this place can be the place where code examples for our Business Central community is shared and maintained. If you want to contribute then I can add you to this project, or I can approve your pull request.

I need to write another blob post about that Azure Blob and the other repositories I have created there. Hope to find time soon.

There is another repository in this project for the Import Tenant Data App. This app has an Azure Blob Connect functionality to utilize the Azure Blob app for data import.

I start by opening the Import Data Source page.

Here I find the Azure Blob Connector that self registered in the Import Data Source table.

I need to go to Process -> Setup to configure my Azure Blob container access.

The information required can be found in the Azure Portal.

Specify the container where you have uploaded all the Xml files.

Then I searched for Import Project List and create a new import project for the General Ledger. The Import Source for Azure Blob was automatically select, since that is the only one available.

Now to import the related Xml files into this project

I get a list of files from the Azure Blob and select the one I need.

The file list will open again if I have more files to import. Close the file list when finished. Back on the Import Project we should now see information from the Xml file.

For each file I need to configure the destination mapping.

If the table exists in my Business Central App then it will be automatically selected.

And I can map fields from the Xml file to the Business Central Table.

There are options to handle different data structure. One is that we can add a transformation rule directly to each field. The other one is using our own custom data upgrade app that subscribes to the events published in this app.

Four events are published, two for each field in the mapping, two before updating or inserting the database record.

Based on the information in the publishers we can do any manual data modification required. In my example the creation time was added to each G/L Entry in NAV, but is added to the G/L Register in Business Central.

From the list of tables we are able to start the data transfer. First we need to make sure that we have the correct configuration for the import. Do we want to commit during the import, do we want to create missing records in our database?

I select to commit after each 1000 records. If my data transfer stops, than I can resume from that position when I start the data transfer again.

We have the option to create a task in the job queue to handle the data transfer.

The job queue can handle multiple concurrent transfers so the import should not take to much time. Looking into the Destination Mapping, we can see the status of the data import.

I will add few more pictures to give you a better idea of what can be done with this import tenant data app. The AL code is in GitHub for you to browse, improve and fix.

JSON Interface – prerequisites

There are two objects we use in all JSON interfaces. We use the TempBlob table and our custom JSON Interface Codeunit.

Abstract

JSON interface uses the same concept as a web service. The endpoint is defined by the Codeunit Name and the caller always supplies a form of request data (JSON) and expects a response data (JSON).

These interface calls therefore are only internal to the Business Central (NAV) server and are very fast. All the data is handled in memory only.

We define these interfaces by Endpoints. Some Endpoints have Methods. We call these Endpoints with a JSON. The JSON structure is predefined and every interface respects the same structure.

We have a single Codeunit that knows how to handle this JSON structure. Passing JSON to an interface requires a data container.

Interface Data

TempBlob is table 99008535. The table is simple but is has a lot of useful procedures.

table 99008535 TempBlob
{
    Caption = 'TempBlob';

    fields
    {
        field(1;"Primary Key";Integer)
        {
            Caption = 'Primary Key';
            DataClassification = SystemMetadata;
        }
        field(2;Blob;BLOB)
        {
            Caption = 'Blob';
            DataClassification = SystemMetadata;
        }
    }

    keys
    {
        key(Key1;"Primary Key")
        {
        }
    }
}

Wikipedia says: A Binary Large OBject (BLOB) is a collection of binary data stored as a single entity in a database management system. Blobs are typically imagesaudio or other multimedia objects, though sometimes binary executable code is stored as a blob. Database support for blobs is not universal.

We use this BLOB for our JSON data when we send a request to an interface and the interface response is also JSON in that same BLOB field.

For people that have been working with web requests we can say that TempBlob.Blob is used both for RequestStream and for ResponseStream.

TempBlob is only used as a form of Stream. We never use TempBlob to store data. We never do TempBlob.Get() or TempBlob.Insert(). And, even if the name indicates that this is a temporary record, we don’t define the TempBlob Record variable as temporary. There is no need for that since we never do any database call for this record.

Interface Helper Codeunit

We use a single Codeunit in all our solutions to prepare both request and response JSON and also to read from the request on the other end.

We have created a Codeunit that includes all the required procedures for the interface communication.

We have three functions to handle the basics;

  • procedure Initialize()
  • procedure InitializeFromTempBlob(TempBlob: Record TempBlob)
  • procedure GetAsTempBlob(var TempBlob: Record TempBlob)

A typical flow of executions is to start by initializing the JSON. Then we add data to that JSON. Before we execute the interface Codeunit we use GetAsTempBlob to write the JSON into TempBlob.Blob. Every Interface Codeunit expects a TempBlob record to be passed to the OnRun() trigger.

codeunit 10008650 "ADV SDS Interface Mgt"
{
    TableNo = TempBlob;

    trigger OnRun()
    var
        Method: Text;
     begin
        with JsonInterfaceMgt do begin
            InitializeFromTempBlob(Rec);
...

Inside the Interface Codeunit we initialize the JSON from the passed TempBlob record. At this stage we have access to all the data that was added to the JSON on the request side.

And, since the interface Codeunit will return TempBlob as well, we must make sure to put the response JSON in there before the execution ends.

with JsonInterfaceMgt do begin
    Initialize();
    AddVariable('Success', true);
    GetAsTempBlob(Rec);
end;

JSON structure

The JSON is an array that contains one or more objects. An JSON array is represented with square brackets.

[]

The first object in the JSON array is the variable storage. This is an example of a JSON that passes two variables to the interface Codeunit.

[
  {
    "TestVariable": "TestVariableValue",
    "TestVariable2": "TestVariableValue2"
  }
]

All variables are stored in the XML format, using FORMAT(<variable>,0,9) and evaluated back using EVALUATE(<variable>,<json text value>,9). The JSON can then have multiple record related objects after the variable storage.

Adding data to the JSON

We have the following procedures for adding data to the JSON;

  • procedure AddRecordID(Variant: Variant)
  • procedure AddTempTable(TableName: Text; Variant: Variant)
  • procedure AddFilteredTable(TableName: Text; FieldNameFilter: Text; Variant: Variant)
  • procedure AddRecordFields(Variant: Variant)
  • procedure AddVariable(VariableName: Text; Value: Variant)
  • procedure AddEncryptedVariable(VariableName: Text; Value: Text)

I will write a more detailed blog about each of these methods and give examples of how we use them, but for now I will just do a short explanation of their usage.

If we need to pass a reference to a database table we pass the Record ID. Inside the interface Codeunit we can get the database record based on that record. Each Record ID that we add to the JSON is stored with the Table Name and we use either of these two procedures to retrieve the record.

  • procedure GetRecord(var RecRef: RecordRef): Boolean
  • procedure GetRecordByTableName(TableName: Text; var RecRef: RecordRef): Boolean

If we need to pass more than one record we can use pass all records inside the current filter and retrieve the result with

  • procedure UpdateFilteredTable(TableName: Text; KeyFieldName: Text; var RecRef: RecordRef): Boolean

A fully populated temporary table with table view and table filters can be passed to the interface Codeunit by adding it to the JSON by name. When we use

  • procedure GetTempTable(TableName: Text; var RecRef: RecordRef): Boolean

in the interface Codeunit to retrieve the temporary table we will get the whole table, not just the filtered content.

We sometimes need to give interface Codeunits access to the record that we are creating. Similar to the OnBeforeInsert() system event. If we add the record fields to the JSON we can use

  • procedure GetRecordFields(var RecRef: RecordRef): Boolean

on the other end to retrieve the record and add or alter any field content before returning it back to the caller.

We have several procedures available to retrieve the variable values that we pass to the interface Codeunit.

  • procedure GetVariableValue(var Value: Variant; VariableName: Text): Boolean
  • procedure GetVariableTextValue(var TextValue: Text; VariableName: Text): Boolean
  • procedure GetVariableBooleanValue(var BooleanValue: Boolean; VariableName: Text): Boolean
  • procedure GetVariableDateValue(var DateValue: Date; VariableName: Text): Boolean
  • procedure GetVariableDateTimeValue(var DateTimeValue: DateTime; VariableName: Text): Boolean
  • procedure GetVariableDecimalValue(var DecimalValue: Decimal; VariableName: Text): Boolean
  • procedure GetVariableIntegerValue(var IntegerValue: Integer; VariableName: Text): Boolean
  • procedure GetVariableGUIDValue(var GuidValue: Guid; VariableName: Text): Boolean
  • procedure GetVariableBLOBValue(var TempBlob: Record TempBlob; VariableName: Text): Boolean
  • procedure GetVariableBLOBValueBase64String(var TempBlob: Record TempBlob; VariableName: Text): Boolean
  • procedure GetEncryptedVariableTextValue(var TextValue: Text; VariableName: Text): Boolean

We use Base 64 methods in the JSON. By passing the BLOB to TempBlob.Blob we can use

TextValue := TempBlob.ToBase64String();

and then

TempBlob.FromBase64String(TextValue);

on the other end to pass a binary content, like images or PDFs.

Finally, we have the possibility to add and encrypt values that we place in the JSON. On the other end we can then decrypt the data to be used. This we use extensively when we pass sensitive data to and from our Azure Function.

Calling an interface Codeunit

As promised I will write more detailed blogs with examples. This is the current list of procedures we use to call interfaces;

  • procedure ExecuteInterfaceCodeunitIfExists(CodeunitName: Text; var TempBlob: Record TempBlob; ErrorIfNotFound: Text)
  • procedure TryExecuteInterfaceCodeunitIfExists(CodeunitName: Text; var TempBlob: Record TempBlob; ErrorIfNotFound: Text): Boolean
  • procedure TryExecuteCodeunitIfExists(CodeunitName: Text; ErrorIfNotFound: Text) Success: Boolean
  • procedure ExecuteAzureFunction() Success: Boolean

The first two expect a JSON to be passed using TempBlob. The third one we use to check for a simple true/false. We have no request data but we read the ‘Success’ variable from the response JSON.

For some of our functionality we use an Azure Function. We have created our function to read the same JSON structure we use internally. We also expect our Azure Function to respond with the sames JSON structure. By doing it that way, we can use the same functions to prepare the request and to read from the response as we do for our internal interfaces.

My Soap Service Proxy Codeunit

Up to now we in Advania have been using the method described here on my blog to connect to most of the Soap web services that we needed to integrate with.

The problem with this method is that we have to manage a lot of DLLs.  This has caused some issues and problems.

Another thing is that we are moving to AL.  And in AL we can’t just throw in a custom DLL to do all the work.

In C/AL We can do this with standard dotnet objects

        DOMDoc := DOMDoc.XmlDocument;
        DOMProcessingInstruction := DOMDoc.CreateProcessingInstruction('xml','version="1.0" encoding="utf-8"');
        DOMDoc.AppendChild(DOMProcessingInstruction);
        DOMElement := DOMDoc.CreateElement('soap:Envelope','http://schemas.xmlsoap.org/soap/envelope/');
        DOMElement.SetAttribute('xmlns:soap','http://schemas.xmlsoap.org/soap/envelope/');
        DOMElement.SetAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
        DOMElement.SetAttribute('xmlns:xsd','http://www.w3.org/2001/XMLSchema');
        DOMElement.SetAttribute('xmlns:ws','http://ws.msggw.siminn');

        DOMElement2 := DOMDoc.CreateElement('soap:Header','http://schemas.xmlsoap.org/soap/envelope/');
        DOMElement.AppendChild(DOMElement2);

        DOMElement2 := DOMDoc.CreateElement('soap:Body','http://schemas.xmlsoap.org/soap/envelope/');
        DOMElement3 := DOMDoc.CreateElement('ws:sendSMS','http://ws.msggw.siminn');
        DOMElement4 := DOMDoc.CreateElement('ws:username','http://ws.msggw.siminn');
        DOMElement4.InnerText := SMSSetup."Service User Name";
        DOMElement3.AppendChild(DOMElement4);
        DOMElement4 := DOMDoc.CreateElement('ws:password','http://ws.msggw.siminn');
        DOMElement4.InnerText := SMSSetup."Service Password";
        DOMElement3.AppendChild(DOMElement4);
        DOMElement4 := DOMDoc.CreateElement('ws:source','http://ws.msggw.siminn');
        DOMElement4.InnerText := SMSSetup.Sender;
        DOMElement3.AppendChild(DOMElement4);
        DOMElement4 := DOMDoc.CreateElement('ws:destination','http://ws.msggw.siminn');
        DOMElement4.InnerText := SendTo;
        DOMElement3.AppendChild(DOMElement4);
        DOMElement4 := DOMDoc.CreateElement('ws:text','http://ws.msggw.siminn');
        DOMElement4.InnerText := SendText;
        DOMElement3.AppendChild(DOMElement4);
        DOMElement4 := DOMDoc.CreateElement('ws:encoding','http://ws.msggw.siminn');
        DOMElement4.InnerText := '0';
        DOMElement3.AppendChild(DOMElement4);
        DOMElement4 := DOMDoc.CreateElement('ws:flash','http://ws.msggw.siminn');
        DOMElement4.InnerText := '0';
        DOMElement3.AppendChild(DOMElement4);
        DOMElement2.AppendChild(DOMElement3);
        DOMElement.AppendChild(DOMElement2);
        DOMDoc.AppendChild(DOMElement);

        HttpWebRequest := HttpWebRequest.Create(SMSSetup."SOAP URL");
        HttpWebRequest.Timeout := 30000;
        HttpWebRequest.UseDefaultCredentials(TRUE);
        HttpWebRequest.Method := 'POST';
        HttpWebRequest.ContentType := 'text/xml; charset=utf-8';
        HttpWebRequest.Accept := 'text/xml';
        HttpWebRequest.Headers.Add('soapAction','urn:sendSMS');
        MemoryStream := HttpWebRequest.GetRequestStream;
        DOMDoc.Save(MemoryStream);
        MemoryStream.Flush;
        MemoryStream.Close;

        NAVWebRequest := NAVWebRequest.NAVWebRequest;
        IF NOT NAVWebRequest.doRequest(HttpWebRequest,HttpWebException,HttpWebResponse) THEN
          ERROR(Text003,HttpWebException.Status.ToString,HttpWebException.Message);

        MemoryStream := HttpWebResponse.GetResponseStream;
        DOMResponseDoc := DOMResponseDoc.XmlDocument;
        DOMResponseDoc.Load(MemoryStream);
        MemoryStream.Flush;
        MemoryStream.Close;

        ReceivedNameSpaceMgt := ReceivedNameSpaceMgt.XmlNamespaceManager(DOMResponseDoc.NameTable);
        ReceivedNameSpaceMgt.AddNamespace('ns','http://ws.msggw.siminn');
        DOMNode := DOMResponseDoc.SelectSingleNode('//ns:return',ReceivedNameSpaceMgt);

        Response := DOMNode.InnerText;
        Success :=  Response = 'SUCCESS';
        IF ShowResult AND Success THEN
          MESSAGE(Text001)
        ELSE IF ShowResult AND NOT Success THEN
          ERROR(Text005,Response);

AL code to do the same with the built in AL objects but that code is not much shorter.

With a custom proxy DLL the code would be

Proxy := Proxy.SMSWS;
Proxy.Url := SMSSetup."SOAP URL";
Response := Proxy.sendSMS(Username,Password,SenderText,SendTo,SendText,'0',FALSE,FALSE,'0');
Success :=  Response = 'SUCCESS';
IF ShowResult AND Success THEN
  MESSAGE(Text001)
ELSE IF ShowResult AND NOT Success THEN
  ERROR(Text005,Response);

With this example we can easily see why we have chosen to create a proxy DLL for most of the Soap services.

I wanted to find a way to make things easier in AL and I remembered having dealt with C/AL objects by Vjeko from some time ago.  I took another look and that code helped me to get started.

The result is a Soap Proxy Client Mgt. Codeunit in C/AL that I have sent to Microsoft’s cal-open-library project asking to have this code put into the standard C/AL library.

Using this Codeunit the code will be like this.

  WITH SoapProxyClientMgt DO BEGIN
    CreateSoapProxy(SMSSetup."SOAP URL");
    InitParameters(9);
    SetParameterValue(Username,1);
    SetParameterValue(Password,2);
    SetParameterValue(SenderText,3);
    SetParameterValue(SendTo,4);
    SetParameterValue(SendText,5);
    SetParameterValue('0',6);
    SetParameterValue(FALSE,7);
    SetParameterValue(FALSE,8);
    SetParameterValue('0',9);
    InvokeMethod('SMSWS','sendSMS',TempBlob);
    XmlBuffer.LoadFromText(TempBlob.ReadAsTextWithCRLFLineSeparator);
    IF XmlBuffer.FindNodesByXPath(XmlBuffer,'/string') THEN
      Response := XmlBuffer.Value;
    Success :=  Response = 'SUCCESS';
    IF ShowResult AND Success THEN
      MESSAGE(Text001)
    ELSE IF ShowResult AND NOT Success THEN
      ERROR(Text005,Response);
  END;

What about AL?

For now this C/AL Codeunit is not in the standard CRONUS database.  I need to import the C/AL code and make sure that AL will be able to use that Codeunit.  You can see how to do this in my last blog post.

This C/AL Code will directly convert to AL and is ready to use.

          with SoapProxyClientMgt do begin
            CreateSoapProxy(SMSSetup."SOAP URL");
            InitParameters(9);
            SetParameterValue(Username,1);
            SetParameterValue(Password,2);
            SetParameterValue(SenderText,3);
            SetParameterValue(SendTo,4);
            SetParameterValue(SendText,5);
            SetParameterValue('0',6);
            SetParameterValue(false,7);
            SetParameterValue(false,8);
            SetParameterValue('0',9);
            InvokeMethod('SMSWS','sendSMS',TempBlob);
            XmlBuffer.LoadFromText(TempBlob.ReadAsTextWithCRLFLineSeparator);
            if XmlBuffer.FindNodesByXPath(XmlBuffer,'/string') then                        
              Response := XmlBuffer.Value;        
            Success :=  Response = 'SUCCESS';
            if ShowResult and Success then
              MESSAGE(Text001)
            else if ShowResult and not Success then
              ERROR(Text005,Response);
          end;

More examples on how to use this Proxy Codeunit will follow.  Stay tuned…

C/AL and AL Side-by-Side Development with AdvaniaGIT

Microsoft supports Side-by-Side development for C/AL and AL.  To start using the Side-by-Side development make sure you have the latest version of AdvaniaGIT add-in for Visual Studio Code and update the PowerShell scripts by using the “Advania: Go!” command.

When the Business Central environment is built use the “Advania: Build C/AL Symbol References for AL” to enable the Side-by-Side development for this environment.  This function will reconfigure the service and execute the Generate Symbol References command for the environment.  From here on everything you change in C/AL on this environment will update the AL Symbol References.

So let’s try this out.

I converted my C/AL project to AL project with the steps described in my previous post.  Then selected to open Visual Studio Code in AL folder.

In my new Visual Studio Code window I selected to build an environment – the Docker Container.

When AdvaniaGIT builds a container it will install the AL Extension for Visual Studio Code from that Container.  We need to read the output of the environment build.  In this example I am asked to restart Visual Studio Code before reinstalling AL Language.  Note that if you are not asked to restart Visual Studio Code you don’t need to do that.

After restart I can see that the AL Language extension for Visual Studio Code is missing.

To fix this I execute the “Advania: Build NAV Environment” command again.  This time, since the Container is already running only the NAV license and the AL Extension will be updated.

Restart Visual Studio Code again and we are ready to go.

If we build new environment for our AL project we must update the environment settings in .vscode\launch.json.  This we can do with a built in AdvaniaGIT command.

We can verify the environment by executing “Advania: Check NAV Environment”.  Everything should be up and running at this time.

Since we will be using Side-by-Side development for C/AL and AL in this environment we need to enable that by executing “Advania: Build C/AL Symbol References for AL”.

This will take a few minutes to execute.

Don’t worry about the warning.  AdvaniaGIT takes care of restarting the service.  Let’s download AL Symbols and see what happens.

We can see that AL now recognizes the standard symbols but my custom one; “IS Soap Proxy Client Mgt.” is not recognized.  I will tell you more about this Codeunit in my next blog post.

I start FinSql to import the Codeunit “IS Soap Proxy Client Mgt.”

Import the FOB file

Close FinSql and execute the “AL: Download Symbols” again.  We can now see that AL recognizes my C/AL Codeunit.

Now I am good to go.

Why do we need Interface Codeunits

And what is an interface Codeunit?

A Codeunit that you can execute with CODEUNIT.RUN to perform a given task is, from my point of view, an interface Codeunit.

An interface Codeunit has a parameter that we put in the

This parameter is always a table object.

We have multiple examples of this already in the application.  Codeunits 12 and 80 are some.  There the parameter is a mixed set of data and settings.  Some of the table fields are business data being pushed into the business logic.  Other fields are settings used to control the business logic.

Table 36, Sales Header, is used as the parameter for Codeunit 80.  Fields like No., Bill-to Customer No., Posting Date and so on are business data.  Fields like Ship, Invoice, Print Posted Documents are settings used to control the business logic but have no meaning as business data.

Every table is then a potential parameter for an interface Codeunit.  Our extension can easily create a table that we use as a parameter table.  Record does not need to be inserted into the table to be passed to the Codeunit.

Let’s look at another scenario.  We know that there is an Interface Codeunit  with the name “My Interface Codeunit” but it is belongs to an Extensions that may and may not be installed in the database.

Here we use the virtual table “CodeUnit Metadata” to look for the Interface Codeunit before execution.

This is all simple and strait forward.  Things that we have been doing for a number of years.

Using TempBlob table as a parameter also gives us flexibility to define more complex interface for the Codeunit.  Tempblob table can store complex data in Json or Xml format and pass that to the Codeunit.

Let’s take an example.  We have an extension that extends the discount calculation for Customers and Items.  We would like to ask this extensions for the discount a given customer will have for a given Item.  Questions like that we can represent in a Json file.

{
    "CustomerNo": "C10000",
    "ItemNo": "1000"
}

And the question can be coded like this.

The Interface Codeunit could be something like

With a Page that contains a single Text variable (Json) we can turn this into a web service.

That we can use from C# with a code like

var navOdataUrl = new System.Uri("https://nav2018dev.westeurope.cloudapp.azure.com:7048/NAV/OData/Company('CRONUS%20International%20Ltd.')/AlexaRequest?$format=json");
var credentials = new NetworkCredential("navUser", "+lppLBb7OQJxlOfZ7CpboRCDcbmAEoCCJpg7cmAEReQ=");
var handler = new HttpClientHandler { Credentials = credentials };

using (var client = new HttpClient(handler))
{
    var Json = new { CustomerNo = "C10000", ItemNo = "1000" };
    JObject JsonRequest = JObject.Parse(Json.ToString());
    JObject requestJson = new JObject();                
    JProperty jProperty = new JProperty("Json", JsonRequest.ToString());
    requestJson.Add(jProperty);
    var requestData = new StringContent(requestJson.ToString(), Encoding.UTF8, "application/json");
    var response = await client.PostAsync(navOdataUrl,requestData);
    dynamic result = await response.Content.ReadAsStringAsync();

    JObject responseJson = JObject.Parse(Convert.ToString(result));
    if (responseJson.TryGetValue("Json", out JToken responseJToken))
    {
        jProperty = responseJson.Property("Json");
        JObject JsonResponse = JObject.Parse(Convert.ToString(jProperty.Value));
        Console.WriteLine(JsonResponse.ToString());
    }
}

This is just scratching the surface of what we can do.  To copy a record to and from Json is easy to do with these functions.

And even if I am showing all this in C/AL there should be no problem in using the new AL in Visual Studio Code to get the same results.