Tag: JSON

Posted on

JSON Interface – examples

We have several ways of using the JSON interfaces. I will give few examples with the required C/AL code. I will be using Advania’s Online Banking solution interfaces for examples.

The Advania’s Online Banking solution is split into several different modules. The main module has the general framework. Then we have communication modules and functionality modules.

On/Off Question

A communication module should not work if the general framework does not exist or is not enabled for the current company. Hence, I need to ask the On/Off question

This is triggered by calling the solution enabled Codeunit. 

IF NOT JsonInterfaceMgt.TryExecuteCodeunitIfExists('ADV Bank Services Enabled Mgt.','') THEN BEGIN
  SetupNotification.MESSAGE := NotificationMsg;
  SetupNotification.SEND;
END;

The interface function will search for the Codeunit, check for execution permissions and call the Codeunit with an empty request BLOB.

The “Enabled” Codeunit must respond with a “Success” variable of true or false.

[External] TryExecuteCodeunitIfExists(CodeunitName : Text;ErrorIfNotFound : Text) Success : Boolean
Object.SETRANGE(Type,Object.Type::Codeunit);
Object.SETRANGE(Name,CodeunitName);
IF NOT Object.FINDFIRST THEN
  IF ErrorIfNotFound <> '' THEN
    ERROR(ErrorIfNotFound)
  ELSE
    EXIT;

IF NOT HasCodeunitExecuteLicense(Object.ID,ErrorIfNotFound) THEN EXIT;
CODEUNIT.RUN(Object.ID,TempBlob);
InitializeFromTempBlob(TempBlob);
GetVariableBooleanValue(Success,'Success');

The “Enabled” Codeunit will test for Setup table read permission and if the “Enabled” flag has been set in the default record.

OnRun(VAR Rec : Record TempBlob)
TestEnabled(Rec);

LOCAL TestEnabled(VAR TempBlob : Record TempBlob)
WITH JsonInterfaceMgt DO BEGIN
  Initialize;
  AddVariable('Success',IsServiceEnabled);
  GetAsTempBlob(TempBlob);
END;

IsServiceEnabled() : Boolean
IF NOT Setup.READPERMISSION THEN EXIT;
EXIT(Setup.GET AND Setup.Enabled);

This is how we can make sure that a module is installed and enabled before we start using it or any of the dependent modules.

Table Access Interface

The main module has a standard response table. We map some of the communication responses to this table via Data Exchange Definition. From other modules we like to be able to read the response from the response table.

The response table uses a GUID value for a primary key and has an integer field for the “Data Exchange Entry No.”. From the sub module we ask if a response exists for the current “Data Exchange Entry No.” by calling the interface.

FindResponse(DataExchEntryNo : Integer) Success : Boolean
WITH JsonInterfaceMgt DO BEGIN
  Initialize;
  AddVariable('DataExchEntryNo',DataExchEntryNo);
  GetAsTempBlob(TempBlob);
  ExecuteInterfaceCodeunitIfExists('ADV Bank Serv. Resp. Interface',TempBlob,ResponseInterfaceErr);
  InitializeFromTempBlob(TempBlob);
  GetVariableBooleanValue(Success,'Success');
END;

The Interface Codeunit for the response table will filter on the “Data Exchange Entry No.” and return the RecordID for that record if found.

OnRun(VAR Rec : Record TempBlob)
WITH JsonInterfaceMgt DO BEGIN
  InitializeFromTempBlob(Rec);
  GetVariableIntegerValue(DataExchEntryNo,'DataExchEntryNo');
  Response.SETRANGE("Data Exch. Entry No.",DataExchEntryNo);
  AddVariable('Success',Response.FINDFIRST);
  IF Response.FINDFIRST THEN
    AddRecordID(Response);
  GetAsTempBlob(Rec);
END;

If the response is found we can ask for the value of any field from that record by calling

GetFieldValue(FieldName : Text) FieldValue : Text
WITH JsonInterfaceMgt DO
  IF GetRecordByTableName('ADV Bank Service Response',RecRef) THEN
    IF DataTypeMgt.FindFieldByName(RecRef,FldRef,FieldName) THEN
      IF FORMAT(FldRef.TYPE) = 'BLOB' THEN BEGIN
        TempBlob.Blob := FldRef.VALUE;
        FieldValue := TempBlob.ReadAsTextWithCRLFLineSeparator();
      END ELSE
        FieldValue := FORMAT(FldRef.VALUE,0,9);

Processing Interface

Some processes can be both automatically and manually executed. For manual execution we like to display a request page on a Report. On that request page we can ask for variables, settings and verify before executing the process.

For automatic processing we have default settings and logic to find the correct variables before starting the process. And since one module should be able to start a process in the other then we use the JSON interface pattern for the processing Codeunit.

We also like to include the “Method” variable to add flexibility to the interface. Even if there is only one method in the current implementation. 

OnRun(VAR Rec : Record TempBlob)
WITH JsonInterfaceMgt DO BEGIN
  InitializeFromTempBlob(Rec);
  IF NOT GetVariableTextValue(Method,'Method') OR (Method = '') THEN
    ERROR(MethodNotFoundErr);
  CASE Method OF
    'BankAccountProcessing':
      BankAccountProcessing(JsonInterfaceMgt);
  END;
END;

LOCAL BankAccountProcessing(JsonInterfaceMgt : Codeunit "IS Json Interface Mgt.")
CheckSetup;
CompanyInformation.GET;
WITH JsonInterfaceMgt DO BEGIN
  GetVariableTextValue(ClaimExportImportFormatCode, 'ClaimExportImportFormatCode');
  GetVariableTextValue(BankAccountNo, 'BankAccountNo');
  GetVariableDateValue(StartDate,'StartDate');
  GetVariableDateValue(EndDate,'EndDate');

  ValidateStartDate;
  ValidateEndDate;
  ValidateImportFormat;
  BankAccount.SETRANGE("No.", BankAccountNo);
  ClaimExportImportFormat.GET(ClaimExportImportFormatCode);
  Initialize;
  AddVariable('BankAccNo',BankAccountNo);
  AddVariable('ClaimantID',CompanyInformation."Registration No.");
  AddVariable('StartDate',StartDate);
  AddVariable('EndDate',EndDate);
  GetAsTempBlob(TempBlob);
  Window.OPEN(ImportingFromBank);
  IF BankAccount.FINDSET THEN REPEAT
    DataExchDef.GET(ClaimExportImportFormat."Resp. Data Exch. Def. Code");

    DataExch.INIT;
    DataExch."Related Record" := BankAccount.RECORDID;
    DataExch."Table Filters" := TempBlob.Blob;
    DataExch."Data Exch. Def Code" := DataExchDef.Code;
    DataExchLineDef.SETRANGE("Data Exch. Def Code",DataExchDef.Code);
    DataExchLineDef.FINDFIRST;
    DataExch."Data Exch. Line Def Code" := DataExchLineDef.Code;

    DataExchDef.TESTFIELD("Ext. Data Handling Codeunit");
    CODEUNIT.RUN(DataExchDef."Ext. Data Handling Codeunit",DataExch);

    DataExch.INSERT;
    IF DataExch.ImportToDataExch(DataExchDef) THEN BEGIN

      DataExchMapping.GET(DataExchDef.Code,DataExchLineDef.Code,DATABASE::"ADV Claim Payment Batch Entry");

      IF DataExchMapping."Pre-Mapping Codeunit" <> 0 THEN
        CODEUNIT.RUN(DataExchMapping."Pre-Mapping Codeunit",DataExch);

      DataExchMapping.TESTFIELD("Mapping Codeunit");
      CODEUNIT.RUN(DataExchMapping."Mapping Codeunit",DataExch);

      IF DataExchMapping."Post-Mapping Codeunit" <> 0 THEN
        CODEUNIT.RUN(DataExchMapping."Post-Mapping Codeunit",DataExch);
    END;
    DataExch.DELETE(TRUE);
  UNTIL BankAccount.NEXT = 0;
  Window.CLOSE;
END;

Reading through the code above we can see that we are also using the JSON interface to pass settings to the Data Exchange Framework. We put the JSON configuration into the “Table Filters” BLOB field in the Data Exchange where we can use it later in the data processing.

From the Report we start the process using the JSON interface.

Bank Account - OnPreDataItem()
WITH JsonInterfaceMgt DO BEGIN
  Initialize;
  AddVariable('Method','BankAccountProcessing');
  AddVariable('ClaimExportImportFormatCode', ClaimExportImportFormat.Code);
  AddVariable('BankAccountNo', BankAccount."No.");
  AddVariable('StartDate',StartDate);
  AddVariable('EndDate',EndDate);
  GetAsTempBlob(TempBlob);
  ExecuteInterfaceCodeunitIfExists('ADV Import BCP Interface', TempBlob, '');
END;

The ExecuteInterfaceCodeunitIfExists will also verify that the Interface Codeunit exists and also verify the permissions before executing.

[External] ExecuteInterfaceCodeunitIfExists(CodeunitName : Text;VAR TempBlob : Record TempBlob;ErrorIfNotFound : Text)
Object.SETRANGE(Type,Object.Type::Codeunit);
Object.SETRANGE(Name,CodeunitName);
IF NOT Object.FINDFIRST THEN
  IF ErrorIfNotFound <> '' THEN
    ERROR(ErrorIfNotFound)
  ELSE
    EXIT;

IF NOT HasCodeunitExecuteLicense(Object.ID,ErrorIfNotFound) THEN EXIT;
CODEUNIT.RUN(Object.ID,TempBlob)

Extensible Interface

For some tasks it might be simple to have a single endpoint (Interface Codeunit) for multiple functionality. This can be achieved by combining Events and Interfaces.

We start by reading the required parameters from the JSON and then we raise an event for anyone to respond to the request.

OnRun(VAR Rec : Record TempBlob)
WITH JsonInterfaceMgt DO BEGIN
  InitializeFromTempBlob(Rec);
  IF NOT GetVariableTextValue(InterfaceType,'InterfaceType') THEN
    ERROR(TypeErr);
  IF NOT GetVariableTextValue(Method,'Method') THEN
    ERROR(MethodErr);
  OnInterfaceAccess(InterfaceType,Method,Rec);
END;

LOCAL [IntegrationEvent] OnInterfaceAccess(InterfaceType : Text;Method : Text;VAR TempBlob : Record TempBlob)

We can also pass the JSON Interface Codeunit, as that will contain the full JSON and will contain the full JSON for the response.

OnRun(VAR Rec : Record TempBlob)
WITH JsonInterfaceMgt DO BEGIN
  InitializeFromTempBlob(Rec);
  IF NOT GetVariableTextValue(InterfaceType,'InterfaceType') THEN
    ERROR(TypeErr);
  IF NOT GetVariableTextValue(Method,'Method') THEN
    ERROR(MethodErr);
  OnInterfaceAccess(InterfaceType,Method,JsonInterfaceMgt);
  GetAsTempBlob(Rec);
END;

LOCAL [IntegrationEvent] OnInterfaceAccess(InterfaceType : Text;Method : Text;VAR JsonInterfaceMgt : Codeunit "IS Json Interface Mgt.")

One of the subscribers could look like this

LOCAL [EventSubscriber] OnInterfaceAccess(InterfaceType : Text;Method : Text;VAR JsonInterfaceMgt : Codeunit "IS Json Interface Mgt.")
IF InterfaceType = 'Claim' THEN
  CASE Method OF
    'Register':
      Register(JsonInterfaceMgt);
    'Edit':
      Edit(JsonInterfaceMgt);
    'AddExportImportFormat':
      AddExportImportFormat(JsonInterfaceMgt);
    'GetSetupCodeunitID':
      GetSetupCodeunitID(JsonInterfaceMgt);
    'GetDirection':
      GetDirection(JsonInterfaceMgt);
    'GetServiceUrl':
      GetServiceUrl(JsonInterfaceMgt);
    'GetExportImportFormat':
      GetExportImportFormat(JsonInterfaceMgt);
    'GetServiceMethod':
      GetServiceMethod(JsonInterfaceMgt);
    'ShowAndGetClaimFormat':
      ShowAndGetClaimFormat(JsonInterfaceMgt);
    'GetDataExchangeDefintionWithAction':
      GetDataExchangeDefintionWithAction(JsonInterfaceMgt);
    'GetOperationResultForClaimant':
      GetOperationResultForClaimant(JsonInterfaceMgt);
    'ShowClaimPayment':
      ShowClaimPayment(JsonInterfaceMgt)
    ELSE
      ERROR(MethodErr,Method);
  END;

Registration Interface

This pattern is similar to the discovery pattern, where an Event is raised to register possible modules into a temporary table. Example of that is the “OnRegisterServiceConnection” event in Table 1400, Service Connection.

Since we can’t have Event Subscriber in one module listening to an Event Publisher in another, without having compile dependencies, we have come up with a different solution.

We register functionality from the functionality module and the list of modules in stored in a database table. The table uses a GUID and the Language ID for a primary key, and then the view is filtered by the Language ID to only show one entry for each module.

This pattern gives me a list of possible modules for that given functionality. I can open the Setup Page for that module and I can execute the Interface Codeunit for that module as well. Both the Setup Page ID and the Interface Codeunit ID are object names.

The registration interface uses the Method variable to select the functionality. It can either register a new module or it can execute the method in the modules.

OnRun(VAR Rec : Record TempBlob)
WITH JsonInterfaceMgt DO BEGIN
  InitializeFromTempBlob(Rec);
  IF NOT GetVariableTextValue(Method,'Method') THEN
    ERROR(MethodErr);
  CASE Method OF
    'Register':
      RegisterCollectionApp(JsonInterfaceMgt);
    ELSE
      ExecuteMethodInApps(Rec);
  END;
END;

LOCAL RegisterCollectionApp(JsonInterfaceMgt : Codeunit "IS Json Interface Mgt.")
WITH BankCollectionModule DO BEGIN
  JsonInterfaceMgt.GetVariableGUIDValue(ID,'ID');
  "Language ID" := GLOBALLANGUAGE();
  IF FIND THEN EXIT;
  INIT;
  JsonInterfaceMgt.GetVariableTextValue(Name,'Name');
  JsonInterfaceMgt.GetVariableTextValue("Setup Page ID",'SetupPageID');
  JsonInterfaceMgt.GetVariableTextValue("Interface Codeunit ID",'InterfaceCodeunitID');
  INSERT;
END;

[External] ExecuteMethodInApps(VAR TempBlob : Record TempBlob)
WITH BankCollectionModule DO BEGIN
  SETCURRENTKEY("Interface Codeunit ID");
  IF FINDSET THEN REPEAT
    JsonInterfaceMgt.ExecuteInterfaceCodeunitIfExists("Interface Codeunit ID",TempBlob,'');
    SETFILTER("Interface Codeunit ID",'>%1',"Interface Codeunit ID");
  UNTIL NEXT = 0;
END;

In the “ExecuteMethodInApps” function I use the filters to make sure to only execute each Interface Codeunit once.

The registration is executed from the Setup & Configuration in the other module.

[External] RegisterCollectionApp()
WITH JsonInterfaceMgt DO BEGIN
  Initialize();
  AddVariable('Method','Register');
  AddVariable('ID',GetCollectionAppID);
  AddVariable('Name',ClaimAppName);
  AddVariable('SetupPageID','ADV Claim Setup');
  AddVariable('InterfaceCodeunitID','ADV Claim Interface Access');
  GetAsTempBlob(TempBlob);
  ExecuteInterfaceCodeunitIfExists('ADV Bank Collection App Access',TempBlob,'');
END;

Extend functionality using the Registered Modules.

As we have been taught we should open our functionality for other modules. This is done by adding Integration Events to our code.

LOCAL [IntegrationEvent] OnBeforePaymentPost(ClaimPaymentEntry : Record "ADV Claim Payment Batch Entry";VAR CustLedgEntry : Record "Cust. Ledger Entry";VAR UseClaimPaymentApplication : Boolean;VAR ToAccountType : 'G/L Account,Customer,Vendor,Bank Acco

LOCAL [IntegrationEvent] OnBeforePostGenJnlLine(VAR ClaimPaymentEntry : Record "ADV Claim Payment Batch Entry";VAR GenJournalLine : Record "Gen. Journal Line";VAR AppliedDocType : Option;VAR AppliedDocNo : Code[20];VAR AppliesToID : Code[50])

Where the Subscriber that needs to respond to this Publisher is in another module we need to extend the functionality using JSON interfaces.

First, we create a Codeunit within the Publisher module with Subscribers. The parameters in the Subscribers are converted to JSON and passed to the possible subscriber modules using the “ExecuteMethodInApps” function above.

LOCAL [EventSubscriber] OnBeforeClaimPaymentInsert(VAR ClaimPaymentEntry : Record "ADV Claim Payment Batch Entry")
GetClaimSettings(ClaimPaymentEntry);

LOCAL GetClaimSettings(VAR ClaimPaymentEntry : Record "ADV Claim Payment Batch Entry") Success : Boolean
JsonInterfaceMgt.Initialize;
JsonInterfaceMgt.AddVariable('Method','GetClaimSettings');
JsonInterfaceMgt.AddVariable('ClaimantID',ClaimPaymentEntry."Claimant Registration No.");
JsonInterfaceMgt.AddVariable('ClaimKey',ClaimPaymentEntry."Claim Account No.");
JsonInterfaceMgt.AddVariable('InterestDate',ClaimPaymentEntry."Interest Date");
JsonInterfaceMgt.GetAsTempBlob(TempBlob);
BankCollectionAppAccess.ExecuteMethodInApps(TempBlob);
JsonInterfaceMgt.InitializeFromTempBlob(TempBlob);
IF NOT JsonInterfaceMgt.GetVariableBooleanValue(Success,'Success') THEN EXIT;

ClaimPaymentEntry."Batch Code" := GetJsonProperty('BatchCode');
ClaimPaymentEntry."Template Code" := GetJsonProperty('TemplateCode');
ClaimPaymentEntry."Source Code" := GetJsonProperty('SourceCode');
ClaimPaymentEntry."Customer No." := GetJsonProperty('CustomerNo');
ClaimPaymentEntry."Customer Name" := GetJsonProperty('CustomerName');

The module that is extending this functionality will be able to answer to these request and supply the required response.

OnRun(VAR Rec : Record TempBlob)
IF NOT Setup.READPERMISSION THEN EXIT;
Setup.GET;

WITH JsonInterfaceMgt DO BEGIN
  InitializeFromTempBlob(Rec);
  IF NOT GetVariableTextValue(Method,'Method') THEN
    ERROR(MethodErr);
  CASE Method OF
    'Register':
      RegisterCollectionApp();
    'GetByCustLedgEntryNo':
      ReturnClaimForCustLedgEntryNo(Rec);
    'GetCustLedgEntryLinkInfo':
      ReturnClaimInfoForCustLedgEntryNo(Rec);
    'DisplayCustLedgEntryLinkInfo':
      DisplayClaimInfoForCustLedgEntryNo();
    'GetClaimSettings':
      ReturnClaimSettings(Rec);
    'GetClaimTempateSettings':
      ReturnClaimTemplateSettings(Rec);
    'GetClaimPaymentApplicationID':
      ReturnClaimPaymentApplicationID(Rec);
    'AddToGenDataRequest':
      ReturnGenDataRequest(Rec);
  END;
END;

Azure Function

The last example we will show is the Azure Function. Some functionality requires execution in an Azure Function.

By making sure that our Azure Function understands the same JSON format used in our JSON Interface Codeunit we can easily prepare the request and read the response using the same methods.

We have the Azure Function Execution in that same JSON Codeunit. Hence, easily prepare the request and call the function in a similar way as for other interfaces.

JsonInterfaceMgt.Initialize;
JsonInterfaceMgt.AddVariable('Method',ServiceMethod);
JsonInterfaceMgt.AddVariable('Url',ServiceUrl);
JsonInterfaceMgt.AddVariable('Username',Username);
JsonInterfaceMgt.AddEncryptedVariable('Password',Password);
JsonInterfaceMgt.AddVariable('Certificate',CertificateValueAsBase64);
JsonInterfaceMgt.AddVariable('Xml',TempBlob.ReadAsTextWithCRLFLineSeparator);
Success := JsonInterfaceMgt.ExecuteAzureFunction;
IF JsonInterfaceMgt.GetVariableBLOBValue(TempBlob,'Xml') THEN
  LogMgt.SetIncoming(TempBlob.ReadAsTextWithCRLFLineSeparator,'xml')
ELSE
  LogMgt.SetIncoming(JsonInterfaceMgt.GetJSON,'json');
IF Success THEN
  DataExch."File Content" := TempBlob.Blob;

The request JSON is posted to the Azure Function and the result read with a single function.

[External] ExecuteAzureFunction() Success : Boolean
GetAsTempBlob(TempBlob);
IF (NOT GetVariableTextValue(AzureServiceURL,'AzureServiceURL')) OR (AzureServiceURL = '') THEN
  AzureServiceURL := 'https://<azurefunction>.azurewebsites.net/api/AzureProxy?code=<some access code>';

OnBeforeExecuteAzureFunction(TempBlob,AzureServiceURL,OmmitWebRequest);

IF NOT OmmitWebRequest THEN BEGIN
  HttpWebRequestMgt.Initialize(AzureServiceURL);
  HttpWebRequestMgt.DisableUI;
  HttpWebRequestMgt.SetMethod('POST');
  HttpWebRequestMgt.SetContentType('application/json');
  HttpWebRequestMgt.SetReturnType('application/json');
  HttpWebRequestMgt.AddBodyBlob(TempBlob);

  TempBlob.INIT;
  TempBlob.Blob.CREATEINSTREAM(ResponseInStream,TEXTENCODING::UTF8);
  IF NOT HttpWebRequestMgt.GetResponse(ResponseInStream,HttpStatusCode,ResponseHeaders) THEN
    IF NOT HttpWebRequestMgt.ProcessFaultResponse('http://www.advania.is') THEN BEGIN
      Initialize;
      AddVariable('Exception',GETLASTERRORTEXT);
      EXIT(FALSE);
    END;
END;

InitializeFromTempBlob(TempBlob);
GetVariableBooleanValue(Success,'Success');

We use the “OnBeforeExecuteAzureFunction” event with a manual binding for our Unit Tests.

In the Azure Function we read the request with standard JSON functions

dynamic data = await req.Content.ReadAsAsync<object>();
Newtonsoft.Json.Linq.JArray jRequestArray = Newtonsoft.Json.Linq.JArray.Parse(data.ToString());
string Method = jRequestArray.First().Value<string>("Method") ?? "Undefined";

Then based on the Method we call each functionality with the request and write the response to the response JSON.

Newtonsoft.Json.Linq.JArray jResponseArray = new Newtonsoft.Json.Linq.JArray();
Newtonsoft.Json.Linq.JObject jResponseObject = new Newtonsoft.Json.Linq.JObject();
try
{            
    switch (Method)
    {
        case "Ping":
            success = true;
            response = "Hello " + (jRequestArray.First().Value<string>("Name") ?? "Undefined") + "!";
            break; 
        case "IOBS2005WSE2.GetAccountStatement":
            xml = jRequestArray.First().Value<string>("Xml") ?? "";
            success = IOBS2005WSE2.Helper.GetAccountStatement(
                jRequestArray.First().Value<string>("Url") ?? "",
                jRequestArray.First().Value<string>("Username") ?? "",
                Decrypt(jRequestArray.First().Value<string>("Password") ?? ""),
                jRequestArray.First().Value<string>("Certificate") ?? "",
                ref xml);
            jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("Xml", xml));
            response = "";
            break;
            ...
        default:
            response = "Method not found";
            break;
    }
    jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("Response", response));
}
catch (System.Exception exception)
{
    httpStatusCode = HttpStatusCode.BadRequest;
    jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("Request", request));
    jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("Message", exception.Message));
    jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("StackTrace", exception.StackTrace.ToString()));
    jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("InnerException", exception.InnerException.Message));
}

jResponseObject.Add(new Newtonsoft.Json.Linq.JProperty("Success", success));
jResponseArray.Add(jResponseObject);           
return req.CreateResponse(httpStatusCode, jResponseArray);

Conclusion

Having standard ways of talking between modules and solutions has opened up for a lot of flexibility. We like to keep our solutions as small as possible.

We could mix “Methods” and “Versions” if we at later time need to be able to extend some of the interfaces. We need to honor the contract we have made for the interfaces. We must not make breaking changes to the interfaces, but we sure can extend them without any problems.

By attaching the JSON Interface Codeunit to the post I hope that you will use this pattern in your solutions. Use the Code freely. It is supplies as-is and without any responsibility, obligations or requirements.

COD10000220Download

Posted on

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 images, audio 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.

Posted on

Don’t worry about DotNet version in C/AL

When using DotNet data type in NAV C/AL we normally lookup a sub type to use.  When we do the result can be something like

Newtonsoft.Json.Linq.JObject.'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'

Then, what will happen when moving this code from NAV 2016 to NAV 2017 and NAV 2018.  The Newtonsoft.Json version is not the same and we will get a compile error!

Just remove the version information from the sub type information.

Newtonsoft.Json.Linq.JObject.'Newtonsoft.Json'

And NAV will find the matching Newtonsoft.Json library you have installed and use it.

This should work for all our DotNet variables.

Posted on

Using REST/Json web services from NAV

One of my most popular blog entry is the one about Json.  I have also had some questions outside this website about this topic.

This week I got a task.  We need to communicate with a payment service that uses REST web services and Json file format.

posapi

I got a document describing the service.  Some methods use GET and some use POST.  Here is how I did this.

In the heart of it all I use Codeunit 1297, “Http Web Request Mgt.”.

getaccesstoken

Every time we talk to this POS API we send an Access Token.  If we don’t have the token in memory (single instance Codeunit), we need to get a new one.  That is what the above code does.

The ParameterMgt Codeunit is what I want to focus on.  You can see that I start by inserting my “Authorization Key” into the RequestBodyBlob.  As usual, I use the TempBlob.Blob to get and set my unstructured data.

setapirequest

The interesting part here is that I use an XMLPort to create the data I need to post to the Api.

apiauthenticatexml

A simple one in this example, but nothing says it can’t be complex.  Then I convert the Xml to Json with a single function.

converttojson

The last TRUE variable means the the Document Element will be skipped and the Json will look like it is supposed to.

apikey

The REST service response is Json.

token

And to read the Json response we take a look at the GetAccessToken function.

getaccesstokenfunction

Here I start by converting from Json to Xml.

convertfromjson

And make sure my Document Element name is “posApi”.

apiaccesstokenxml

And I have the result.

As you can see from the documentation some of the Json data is more complex.  This method will work nevertheless.

For more complex date I always create tables that matches the Json structure.  These table I use temporary through the whole process so the don’t need to be licensed tables.  Here is an example where this XMLPORT

getauthorization

will read this Json

getauthorizationjson

I suggest that with our current NAV this is the easiest way to handle REST web services and Json.

 

Posted on

Data Exchange Framework enhancements

I have made several enhancements to the Data Exchange Framework in NAV 2016.  Previously I wrote about text or a comma separated file import and about access to a local data file when creating a xml/json structure.  Both those enhancements are included in the objects attached to this post.

I have needed to handle JSON files where the actual data is not in the node value but in the node name.  To support this I added a boolean field to the Data Exchange Column Definition table (1223).

ConvertNodeNameToValue

To support this I added the same field to the Data Exchange Field Mapping table (1225) as a flow field, calculating the value from the definition table.

Codeunit 1203 is used to import XML and JSON files into the Data Exchange Field table.  I made two changes to the InsertColumn.  First, I added a parameter that accepts the node name and if the above switch is set to true I insert the node name into the column value instead of the node value.  The other change is that instead of finding only the first Data Exchange Column Definition I loop through all of them.  This allows me to create more than one definition based of the same XML node and therefore import into multiple columns from the same node.

To enable this scenario in the Currency Exchange Update Service I made changes to the Data Exchange Field Mapping Buffer table (1265) and the Data Exchange Setup Subform page.  More on that later.

A JSON file without a single root element will cause an error in the standard code.  Inspired my Nikola Kukrika on NAVTechDays 2015 I made changes to Codeunit 1237 utilizing the TryFunction to solve this problem.  To top this of I made a simple change to the Currency Exchange Rate Service Card page (1651) and table (1650) to allow JSON file type.

Having completed these changes I can now use a web service that delivers JSON in my Currency Exchange Rate Service.

CurrencyLayerJSON

Also inspired by Microsoft I added a transformation type to the Transformation Rule table (1237) to convert the Unix Timestamp (seconds since January 1st 1970 UTC) to a date.

UnixTimestamp

All objects and deltas are attached

DEF-Enhancements

Posted on

JSON meets NAV

I have been using SOAP services over the last years.  Only recently the RESTful web services have become more and more popular in my integration work.  Wikipedia says:

In computing, Representational State Transfer (REST) is a software architecture style for building scalable web services. REST gives a coordinated set of constraints to the design of components in a distributed hypermedia system that can lead to a higher performing and more maintainable architecture.

RESTful systems typically, but not always, communicate over the Hypertext Transfer Protocol with the same HTTP verbs (GET, POST, PUT, DELETE, etc.) which web browsers use to retrieve web pages and to send data to remote servers. REST interfaces usually involve collections of resources with identifiers, for example /people/paul, which can be operated upon using standard verbs, such as DELETE /people/paul.

As we are used to XML as the body for our SOAP messages we can also use XML as the body for a RESTful web service.  I just finished writing a code to communicate with Azure from NAV.  This communication was using RESTful web services and XML.

So, what is JSON?  Wikipedia says:

JSON, (canonically pronounced /ˈdʒeɪsən/ JAY-sən; sometimes JavaScript Object Notation), is an open standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is the primary data format used for asynchronous browser/server communication (AJAJ), largely replacing XML (used by AJAX).

Although originally derived from the JavaScript scripting language, JSON is a language-independent data format. Code for parsing and generating JSON data is readily available in many programming languages.

The JSON format was originally specified by Douglas Crockford. It is currently described by two competing standards, RFC 7159 and ECMA-404. The ECMA standard is minimal, describing only the allowed grammar syntax, whereas the RFC also provides some semantic and security considerations. The official Internet media type for JSON is application/json. The JSON filename extension is .json.

With JSON it is possible to deliver similar data structure as with XML.  JSON on the other hand requires a much less metadata.  Here is an example JSON from Wikipedia:

[code lang=”javascript”]{
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021-3100"
},
"phoneNumbers": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "office",
"number": "646 555-4567"
}
],
"children": [],
"spouse": null
}[/code]

There is not a good support for JSON in native .NET from Microsoft.  However, with Visual Studio, Microsoft installs an external DLL in to the folder “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies”

Newtonsoft

With this Json.NET in Dynamics NAV Add-ins folder we now have some way to handle JSON files.  Using this Add-in I created a NAV Codeunit to manage JSON text.

This Codeunit contains functions to build a JSON document, like

[code lang=”csharp”]
StartJSon;
AddToJSon(‘newssn’,CompanyInformation."Registration No.");
AddToJSon(‘billtossn’,BillToCustNo);
AddToJSon(‘newcompanyname’,CompanyInformation.Name);
AddToJSon(‘newemail’,CompanyInformation."E-Mail");
AddToJSon(‘register_einvoice’,EInvoiceEnabled);
AddToJSon(‘register_supdoc’,SupDocEnabled);
AddToJSon(‘register_natreg’,NRLookupEnabled);
EndJSon;
Json := Json.Copy(GetJSon);[/code]

A function to import values from a JSON document to a temporary table, like

[code lang=”csharp”]
ReadJSon(String,TempPostingExchField);

WITH TempPostingExchField DO BEGIN
SETCURRENTKEY("Line No.","Column No.");
IF FIND(‘-‘) THEN REPEAT
SETRANGE("Column No.","Column No.");
InsertFileDetails(TempPostingExchField,WebServiceURL);
FINDLAST;
SETRANGE("Column No.");
UNTIL NEXT = 0;
END;[/code]

Or just a simple way to return a single value from a simple JSON string, like

[code lang=”csharp”]FileName := GetValueFromJsonString(String,’filename’);[/code]

With these functions NAV should be able to handle JSON files without any problems.

Now you can add JSON handling to your arsenal.

Json Codeunit and required add-ins