REST Web Services using Json and requiring authentication

But first…

Registration for NAV TechDays 2017 have been opened.  I will do a workshop on web services and json.  I will be using both C/AL and AL with VS Code in this workshop.

Make sure to register for the conference and if possible go to one or two of the workshops.

Now to the topic.  Yesterday I started to develop an integration solution for bokun.io.  Their API is RESTful and uses Json file formats.  It also requires authentication.

In a project like this I usually start by using the OCR Service Setup from standard NAV.  Create a Setup table and a page.

Looking at the API documentation we can see that we need to use HmacSHA1 with both Access Key and Secret Key to authenticate.  In other project I used HmacSHA256 with the Access Key for the Azure API.

First part of the authentication is the time stamp created in UTC.  I find it easy to use the DateTime DotNet variable to solve this.  There are two different formatting I needed to use.

REST service normally just use GET or POST http methods.  The authentication is usually in the request headers.  This is an example from bokun.is

The GetSignature function is

The Secret Key string and the Signature is converted to a byte array.  The Crypto class is constructed with the Secret Key Byte Array and used to compute hash for the Signature Byte Array. That hash is also a byte array that must be converted to a base64 string.  This will give you the HmacSHA1 signature to use in the request header.

My Azure project is using HmacSHA256 but the code is similar.

Azure displays the Access Keys in base64 format while bokun.is has a normal string.

A little further down the line I choose not to use XML Ports, like I did here, but still convert Json to Xml or Xml to Json.

I use the functions from Codeunit “XML DOM Management” to handle the Xml.  This code should give you the general idea.

OBJECT Codeunit 60201 Bokun.is Data Management
{
  OBJECT-PROPERTIES
  {
    Date=;
    Time=;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {
    VAR
      XMLDOMMgt@60200 : Codeunit 6224;

    PROCEDURE ReadCurrencies@1(ResponseString@10035985 : Text;VAR CurrencyBuffer@10035988 : TEMPORARY Record 60201);
    VAR
      XmlDocument@60202 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      ResultXMLNodeList@60201 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList";
      ResultXMLNode@60200 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
    BEGIN
      XmlDocument := XmlDocument.XmlDocument;
      XmlDocument.LoadXml(JsonToXml('{"Currency":' + ResponseString + '}'));
      XMLDOMMgt.FindNodes(XmlDocument.DocumentElement,'Currency',ResultXMLNodeList);
      FOREACH ResultXMLNode IN ResultXMLNodeList DO
        ReadCurrency(ResultXMLNode,CurrencyBuffer);
    END;

    LOCAL PROCEDURE ReadCurrency@60205(ResultXMLNode@60201 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";VAR CurrencyBuffer@60200 : TEMPORARY Record 60201);
    BEGIN
      WITH CurrencyBuffer DO BEGIN
        INIT;
        Code := XMLDOMMgt.FindNodeText(ResultXMLNode,'code');
        "Currency Factor" := ToDecimal(XMLDOMMgt.FindNodeText(ResultXMLNode,'rate'));
        Payment := ToBoolean(XMLDOMMgt.FindNodeText(ResultXMLNode,'payment'));
        INSERT;
      END;
    END;

    PROCEDURE ReadActivities@60201(ResponseString@10035985 : Text);
    VAR
      XmlDocument@60202 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      ResultXMLNodeList@60201 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList";
      ResultXMLNode@60200 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
    BEGIN
      XmlDocument := XmlDocument.XmlDocument;
      XmlDocument.LoadXml(JsonToXml(ResponseString));
    END;

    PROCEDURE GetActivityRequestJson@10035986(NoOfParticipants@60200 : Integer;StartDate@60201 : Date;EndDate@60202 : Date) Json : Text;
    VAR
      XmlDocument@10035987 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      CreatedXMLNode@10035988 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
    BEGIN
      XmlDocument := XmlDocument.XmlDocument;
      XMLDOMMgt.AddRootElement(XmlDocument,GetDocumentElementName,CreatedXMLNode);
      IF NoOfParticipants <> 0 THEN
        XMLDOMMgt.AddNode(CreatedXMLNode,'participants',FORMAT(NoOfParticipants,0,9));
      IF StartDate <> 0D THEN
        XMLDOMMgt.AddNode(CreatedXMLNode,'startDate',FORMAT(StartDate,0,9));
      IF EndDate <> 0D THEN
        XMLDOMMgt.AddNode(CreatedXMLNode,'endDate',FORMAT(EndDate,0,9));
      Json := XmlToJson(XmlDocument.OuterXml);
    END;

    PROCEDURE XmlToJson@94(Xml@10035985 : Text) Json : Text;
    VAR
      JsonConvert@10017292 : DotNet "'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'.Newtonsoft.Json.JsonConvert";
      JsonFormatting@10017296 : DotNet "'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'.Newtonsoft.Json.Formatting";
      XmlDocument@10017291 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
    BEGIN
      XmlDocument := XmlDocument.XmlDocument;
      XmlDocument.LoadXml(Xml);
      Json := JsonConvert.SerializeXmlNode(XmlDocument.DocumentElement,JsonFormatting.Indented,TRUE);
    END;

    PROCEDURE JsonToXml@95(Json@10035985 : Text) Xml : Text;
    VAR
      JsonConvert@10017293 : DotNet "'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed'.Newtonsoft.Json.JsonConvert";
      XmlDocument@10017291 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
    BEGIN
      XmlDocument := JsonConvert.DeserializeXmlNode(Json,GetDocumentElementName);
      Xml := XmlDocument.OuterXml;
    END;

    LOCAL PROCEDURE GetDocumentElementName@97() : Text;
    BEGIN
      EXIT('Bokun.is');
    END;

    LOCAL PROCEDURE ToDecimal@98(InnerText@10035985 : Text) Result : Decimal;
    BEGIN
      IF NOT EVALUATE(Result,InnerText,9) THEN EXIT(0);
    END;

    LOCAL PROCEDURE ToInteger@92(InnerText@10035985 : Text) Result : Decimal;
    BEGIN
      IF NOT EVALUATE(Result,InnerText,9) THEN EXIT(0);
    END;

    LOCAL PROCEDURE ToBoolean@91(InnerText@10035985 : Text) Result : Boolean;
    BEGIN
      IF NOT EVALUATE(Result,InnerText,9) THEN EXIT(FALSE);
    END;

    LOCAL PROCEDURE ToDate@93(InnerText@10035985 : Text) Result : Date;
    BEGIN
      IF NOT EVALUATE(Result,COPYSTR(InnerText,1,10),9) THEN EXIT(0D);
    END;

    LOCAL PROCEDURE ToDateTime@99(InnerText@10035985 : Text) Result : DateTime;
    BEGIN
      IF NOT EVALUATE(Result,InnerText,9) THEN EXIT(0DT);
    END;

    BEGIN
    END.
  }
}

 

 

Minimize C/AL Code for Online Communication

These days I keep busy upgrading Advania solutions to Dynamics NAV 2013.  All the older solutions that have communicated with web services have DOM objects and manually created XML files.  We had to add and remove namespace to be able to use XML ports but in the end this worked flawlessly.

In NAV 2013 it is possible to use DOM objects on the client-side but my mission is to execute as much as possible on the server-side and use dotnet interoperability in all cases.  That left me with two choices; rewrite all the DOM C/AL Code and use dotnet or build a class library and minimize the code as much as possible.

I tried both and for me the choice is simple.  I will go with the class library method.

If I have the WSDL (Web Services Description Language) as a file or supplied by the web service then I will get both the commands and the data types needed for the communication.  The C/AL Code needed can be as little as four lines.

[code]
Greidslur := Greidslur.GreidslurWS; // Construct the SoapHTTPClientProtocol
Greidslur.Url := XMLURLPaymentsUnsigned; // Set the URL to the service
SecurityHelper.SetUserName(Greidslur,WebServiceUserID,WebServicePassword); // Add authentication if needed
DoesExist := Greidslur.ErReikningurTil(BankNo,LedgerNo,AccountNo,OwnerID); // Carry out the communication[/code]

The identical C/AL Code required to do the same query with DOM is a lot longer

[code]DocumentReady := CREATE(DOMDocument);
IF NOT DocumentReady THEN
ERROR(Text003);

IXMLDOMProcessingInstruction := DOMDocument.createProcessingInstruction(‘xml’,’version="1.0" encoding="utf-8"’);
DOMDocument.appendChild(IXMLDOMProcessingInstruction);
IXMLDOMElement := DOMDocument.createElement(‘soap:Envelope’);
IXMLDOMElement.setAttribute(‘xmlns:soap’,’http://schemas.xmlsoap.org/soap/envelope/’);
IXMLDOMElement.setAttribute(‘xmlns:xsi’,’http://www.w3.org/2001/XMLSchema-instance’);
IXMLDOMElement.setAttribute(‘xmlns:xsd’,’http://www.w3.org/2001/XMLSchema’);
CreateHeader(IXMLDOMElement2); // Creates the authentication
IXMLDOMElement3 := DOMDocument.createElement(‘soap:Body’);
IXMLDOMElement4 := DOMDocument.createElement(‘ErReikningurTil’);
IXMLDOMElement4.setAttribute(‘xmlns’,’http://ws.isb.is’);
IXMLDOMElement5 := DOMDocument.createElement(‘banki’);
IXMLDOMElement5.nodeTypedValue(BankNo);
IXMLDOMElement4.appendChild(IXMLDOMElement5);
IXMLDOMElement5 := DOMDocument.createElement(‘hofudbok’);
IXMLDOMElement5.nodeTypedValue(LedgerNo);
IXMLDOMElement4.appendChild(IXMLDOMElement5);
IXMLDOMElement5 := DOMDocument.createElement(‘reikningsnumer’);
IXMLDOMElement5.nodeTypedValue(AccountNo);
IXMLDOMElement4.appendChild(IXMLDOMElement5);
IXMLDOMElement5 := DOMDocument.createElement(‘kennitala’);
IXMLDOMElement5.nodeTypedValue(OwnerID);
IXMLDOMElement4.appendChild(IXMLDOMElement5);
IXMLDOMElement3.appendChild(IXMLDOMElement4);
IXMLDOMElement.appendChild(IXMLDOMElement2);
IXMLDOMElement.appendChild(IXMLDOMElement3);
DOMDocument.appendChild(IXMLDOMElement);

XMLHttp.open( ‘POST’, XMLURLPaymentsUnsigned, FALSE);
XMLHttp.setRequestHeader(‘soapAction’,’http://ws.isb.is/ErReikningurTil’);
XMLHttp.send(DOMDocument);
DOMResponseDocument := XMLHttp.responseXML;

IXMLDOMNode := DOMResponseDocument.selectSingleNode(‘/soap:Envelope/soap:Body’);
IXMLDOMNodeList2 := IXMLDOMNode.childNodes;
FOR j := 1 TO IXMLDOMNodeList2.length DO BEGIN
IXMLDOMNode2 := IXMLDOMNodeList2.nextNode;
CASE IXMLDOMNode2.nodeName OF
‘soap:Fault’:
BEGIN
IXMLDOMNodeList3 := IXMLDOMNode2.childNodes;
FOR k := 1 TO IXMLDOMNodeList3.length DO BEGIN
IXMLDOMNode4 := IXMLDOMNodeList3.nextNode;
CASE IXMLDOMNode4.nodeName OF
‘faultcode’:
BEGIN
END;
‘faultstring’:
BEGIN
ERROR(DecodeText(IXMLDOMNode4.text));
END;
‘faultfactor’:
BEGIN
END;
END;
END;
END;
‘ErReikningurTilResponse’:
BEGIN
IXMLDOMNodeList3 := IXMLDOMNode2.childNodes;
IXMLDOMNode3 := IXMLDOMNodeList3.nextNode;
CASE IXMLDOMNode3.nodeName OF
‘ErReikningurTilResult’:
BEGIN
IF UPPERCASE(IXMLDOMNode3.text) = ‘TRUE’ THEN
DoesExist := TRUE;
END;
END;
END;
END;
END;

CLEAR(DOMDocument);
CLEAR(DOMResponseDocument);[/code]

This example should show you not only that the class library method is simpler but also the potential error in creating the XML is no longer available.

But, there is a but.  There are web services that do not have WSDL.  Just a simple ‘POST’ or ‘GET’ services that requires incoming XML and respond with a XML.  For all the XML files needed there should be a XML Schema Definition or what is normally knows as a XSD file.  If that is the case then there is also a dotnet solution for that.

The first step is to collect all the XSD files needed into a single folder on your local computer and start the Visual Studio Command Prompt.

VisualStudioCommandPrompt

Go to the folder with the XSD files.

VisualStudioCommandPromptStarted

Then you use the command “xsd CollectorEntity.xsd /classes” and repeat for all xsd files.  You should now have C# class file for all XSD files.

ClassesCreated

Next step is to build a class library in the same way that I showed before and add the class files to the solution.

VisualStudioAddFiles

Then build the solution and copy the DLL file to your server Add-ins folder and to your developement environment Add-ins folder and you are good to go.  After adding the new class as a dotnet variable to C/AL I am able to handle the data types from the XSD file the same way as if they were created by a WSDL.

[code]Login := Login.Login;
Login.user := WebServiceUserID;
Login.password := WebServicePassword;
Login.version := 1.1;

Login_answer := Login_answer.Login_answer;
Log."Outgoing Message".CREATEOUTSTREAM(OutStr);
PrepareSend(Login,OutStr);
Log.MODIFY;
COMMIT;

Log."Incoming Message".CREATEOUTSTREAM(OutStr);
Send(OutStr);
Log.MODIFY;
COMMIT;

IF Receive(Login_answer,Login_answer.GetType) THEN
SessionID := Login_answer.sessionid;[/code]

The functions, PrepareSend, Send and Receive will work for all data types

[code]OBJECT Codeunit 50000 Dotnet Send and Receive
{
OBJECT-PROPERTIES
{
Date=09.05.13;
Time=12:06:27;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=BEGIN
END;

}
CODE
{
VAR
HttpWebRequest@10010413 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpWebRequest";
XMLResponseDoc@10010414 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
ServiceURL@10000003 : Text[1024];
GotSetup@10000004 : Boolean;
Text003@10000005 : TextConst ‘ENU=Error: %1\%2;ISL=St”Ðuvilla %1, %2\\%3\%4′;

PROCEDURE GetSetup@3(NewServiceURL@10000000 : Text[1024]);
BEGIN
IF NOT GotSetup THEN BEGIN
ServiceURL := NewServiceURL;
END;
END;

PROCEDURE PrepareSend@10010407(VAR OutObj@1000000000 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Object";VAR RequestStream@10010401 : OutStream) : Boolean;
VAR
MemoryStream@1000000006 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.MemoryStream";
XmlTextWriter@1000000007 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlTextWriter";
XmlSerializer@1000000008 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.Serialization.XmlSerializer";
XMLRequestDoc@1000000013 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
Encoding@1000000010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Text.Encoding";
InStr@10010400 : InStream;
BEGIN
Encoding := Encoding.UTF8;
MemoryStream := MemoryStream.MemoryStream;
XmlTextWriter := XmlTextWriter.XmlTextWriter(MemoryStream,Encoding);
XmlSerializer := XmlSerializer.XmlSerializer(OutObj.GetType);
XmlSerializer.Serialize(XmlTextWriter,OutObj);

XMLRequestDoc := XMLRequestDoc.XmlDocument;
XMLRequestDoc.PreserveWhitespace := TRUE;
MemoryStream.Position := 0;
XMLRequestDoc.Load(MemoryStream);
MemoryStream.Close;
XMLRequestDoc.Save(RequestStream);

HttpWebRequest := HttpWebRequest.Create(ServiceURL); // Live Server
HttpWebRequest.Method := ‘POST’;
HttpWebRequest.ContentType := ‘text/xml; charset=utf-8’;
HttpWebRequest.Accept := ‘text/xml’;
HttpWebRequest.UserAgent := ‘XMLClient 1.0′;
MemoryStream := HttpWebRequest.GetRequestStream;
XMLRequestDoc.Save(MemoryStream);
MemoryStream.Flush;
MemoryStream.Close;
END;

PROCEDURE Send@10010405(VAR ResponseStream@10010400 : OutStream) : Boolean;
VAR
HttpWebException@10010401 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebException";
HttpWebResponse@10010402 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpWebResponse";
HttpStatusCode@1000000012 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpStatusCode";
MemoryStream@1000000006 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.MemoryStream";
XMLResponseDoc@1000000009 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
InStr@10010403 : InStream;
BEGIN
HttpWebResponse := HttpWebRequest.GetResponse;
IF HttpWebResponse.StatusCode.ToString <> HttpStatusCode.OK.ToString THEN
ERROR(Text003,HttpWebResponse.StatusCode.ToString,HttpWebResponse.StatusDescription);

MemoryStream := HttpWebResponse.GetResponseStream;
XMLResponseDoc := XMLResponseDoc.XmlDocument;
XMLResponseDoc.Load(MemoryStream);
MemoryStream.Flush;
MemoryStream.Close;
XMLResponseDoc.Save(ResponseStream);
END;

PROCEDURE Receive@10010406(VAR InObj@1000000001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Object";typeResponse@1000000002 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Type") : Boolean;
VAR
XmlSerializer@1000000008 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.Serialization.XmlSerializer";
XmlNodeReader@1000000015 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNodeReader";
BEGIN
XmlNodeReader := XmlNodeReader.XmlNodeReader(XMLResponseDoc.DocumentElement);
XmlSerializer := XmlSerializer.XmlSerializer(typeResponse);
InObj := XmlSerializer.Deserialize(XmlNodeReader);
EXIT(NOT ISNULL(InObj));
END;

EVENT XMLResponseDoc@10010414::NodeInserting@93(sender@10010401 : Variant;e@10010400 : DotNet "’System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNodeChangedEventArgs");
BEGIN
END;

EVENT XMLResponseDoc@10010414::NodeInserted@94(sender@10010401 : Variant;e@10010400 : DotNet "’System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNodeChangedEventArgs");
BEGIN
END;

EVENT XMLResponseDoc@10010414::NodeRemoving@95(sender@10010401 : Variant;e@10010400 : DotNet "’System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNodeChangedEventArgs");
BEGIN
END;

EVENT XMLResponseDoc@10010414::NodeRemoved@96(sender@10010401 : Variant;e@10010400 : DotNet "’System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNodeChangedEventArgs");
BEGIN
END;

EVENT XMLResponseDoc@10010414::NodeChanging@97(sender@10010401 : Variant;e@10010400 : DotNet "’System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNodeChangedEventArgs");
BEGIN
END;

EVENT XMLResponseDoc@10010414::NodeChanged@98(sender@10010401 : Variant;e@10010400 : DotNet "’System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Xml.XmlNodeChangedEventArgs");
BEGIN
END;

BEGIN
END.
}
}

[/code]

If this i still causing you headaches just contact me and we will figure something out.

Old format or invalid type library Excel error in Rapid Start

As a part of the new localized NAV 2013 we need to configure the Rapid Start package to be used with new installations.  As we started to work with the packages trying to export to Excel we got an error.

ExcelLocalizationError

The cause is known by Microsoft.

You receive this error calling an Excel method when the   following conditions are true:   

  • The method requires an LCID (locale identifier).
  • You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language.

If the client computer runs the English version of Excel and the locale for the current user is configured for a language other   than English, Excel will try to locate the language pack for the configured   language. If the language pack is not found, the error is reported.

We saw the same error in NAV 2009 R2 Role Tailored Client.  The solution is simple, download and install a language pack for Microsoft Office that matches your regional settings.  You should find the language pack on the Office Language Interface Pack (LIP) downloads page.

Link for Office 2016

 

Integration for Sales

I started to use the CRM Integration that was delivered with NAV 2009 R2 for one of my client earlier in this year.  Not for CRM though but for the handheld computers.

One of the functions is to update a parent integration record.  That means that if a user modifies a sales line the sales header is fetched and the modify timestamp for the sales header integration record is updated.

When I update the handheld computers I will search for all modified integration records, update the handheld computers and clear out the modified timestamp from the integration record.

In the standard codeunit 5150 and in InsertUpdateIntegrationRecord function the code always updates the modified time stamp.  This will be executed for every modification for a sales header or a sales line.  I wanted to change this so that the modified time stamp will only be set once.

[code]IF "Modified On" = 0DT THEN BEGIN
"Modified On" := IntegrationLastModified;
MODIFY;
END;[/code]

instead of

[code]"Modified On" := IntegrationLastModified;
MODIFY;[/code]

I think that Microsoft should look into this in the standard code.

Using the CRM Connector integration for other integration

In my work with the employee punching I needed to be able to answer a request on changed employees.  In NAV 2009 R2 we got the CRM Connector and that included database triggers that are executed when the data is modified.  Previously we only had global triggers that where executed when the table triggers where executed.  That meant that a C/AL INSERT, MODIFY, DELETE or RENAME did not fire a global trigger but they do fire the database triggers.

The database trigger handling is based on single instance codeunit 5150, Integration Management.  Microsoft just released a patch for this codeunit.

What I did is to create another single instance codeunit as an add in for this codeunit.  Added my codeunit as a global variable to codeunit 5150 and made a few additions to the code.
[code]GetDatabaseTableTriggerSetup(TableID : Integer;VAR Insert : Boolean;VAR Modify : Boolean;VAR Delete : Boolean;VAR Rename : Boolean)
IF COMPANYNAME = ” THEN
EXIT;

//#PU-
IF PunchIntegrationAddin.GetConnectorEnabled(TableID,Insert,Modify,Delete,Rename) THEN
EXIT;
//#PU+

IF NOT MarketingSetupRead THEN BEGIN
MarketingSetupRead := TRUE;
IF MarketingSetup.GET THEN
IntegrationActivated := MarketingSetup."Enable Connector";
END;

IF NOT IntegrationActivated THEN
EXIT;

IF IsIntegrationRecord(TableID) OR IsIntegrationRecordChild(TableID) THEN BEGIN
Insert := TRUE;
Modify := TRUE;
Delete := TRUE;
Rename := TRUE;
END;

OnDatabaseInsert(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseInsert(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);

OnDatabaseModify(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseModify(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);

OnDatabaseDelete(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseDelete(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETRANGE("Record ID",RecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
// Handle exceptions where "Deleted On" should not be set.
IF RecRef.NUMBER = DATABASE::"Sales Header" THEN BEGIN
FieldRef1 := RecRef.FIELD(58); // Invoice
SkipDeletion := FieldRef1.VALUE;
END;

IF NOT SkipDeletion THEN
IntegrationRecord."Deleted On" := TimeStamp;

CLEAR(IntegrationRecord."Record ID");
IntegrationRecord."Modified On" := TimeStamp;
IntegrationRecord.MODIFY;
END;
END;

OnDatabaseRename(RecRef : RecordRef;XRecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseRename(RecRef,XRecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETRANGE("Record ID",XRecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Record ID" := RecRef.RECORDID;
IntegrationRecord.MODIFY;
END;
END;
InsertUpdateIntegrationRecord(RecRef,TimeStamp);[/code]
I also made a copy of table 5151, Integration Record, just for my employee changes.  This is the code in my single instance integration add in codeunit.
[code]OBJECT Codeunit 10001037 Punch Integration Addin
{
OBJECT-PROPERTIES
{
Date=27.06.12;
Time=14:39:59;
Modified=Yes;
Version List=PUNCH6.00;
}
PROPERTIES
{
Permissions=TableData 5200=r,
TableData 10001021=rimd,
TableData 10010238=r;
SingleInstance=Yes;
OnRun=BEGIN
END;

}
CODE
{
VAR
PunchSetupRead@1200050000 : Boolean;
IntegrationActivated@1200050001 : Boolean;
Text001@1200050002 : TextConst ‘ENU=Error creating %1 – %2. It already exists.;ISL=Villa kom upp við stofnun %1 – %2. Er þegar til.’;

PROCEDURE GetConnectorEnabled@1200050000(TableID@1200050004 : Integer;VAR Insert@1200050003 : Boolean;VAR Modify@1200050002 : Boolean;VAR Delete@1200050001 : Boolean;VAR Rename@1200050000 : Boolean) : Boolean;
VAR
PunchSetup@1005 : Record 10001049;
BEGIN
IF COMPANYNAME = ” THEN
EXIT;

IF NOT PunchSetupRead THEN BEGIN
PunchSetupRead := TRUE;
IF PunchSetup.GET THEN
IntegrationActivated := PunchSetup."Enable Connector";
END;

IF NOT IntegrationActivated THEN
EXIT(FALSE);

IF IsIntegrationRecord(TableID) OR IsIntegrationRecordChild(TableID) THEN BEGIN
Insert := TRUE;
Modify := TRUE;
Delete := TRUE;
Rename := TRUE;
END;

EXIT(Insert OR Modify OR Delete OR Rename);
END;

PROCEDURE OnDatabaseInsert@2(RecRef@1000 : RecordRef);
VAR
TimeStamp@1001 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;

PROCEDURE OnDatabaseModify@3(RecRef@1000 : RecordRef);
VAR
TimeStamp@1001 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;

PROCEDURE OnDatabaseDelete@4(RecRef@1000 : RecordRef);
VAR
IntegrationRecord@1001 : Record 10001021;
TimeStamp@1003 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETCURRENTKEY("Record ID");
IntegrationRecord.SETRANGE("Record ID",RecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Deleted On" := TimeStamp;

CLEAR(IntegrationRecord."Record ID");
IntegrationRecord."Modified On" := TimeStamp;
IntegrationRecord.MODIFY;
END;
END;
END;

PROCEDURE OnDatabaseRename@5(RecRef@1000 : RecordRef;XRecRef@1001 : RecordRef);
VAR
IntegrationRecord@1003 : Record 10001021;
FieldRef1@1200050000 : FieldRef;
TimeStamp@1002 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETCURRENTKEY("Record ID");
IntegrationRecord.SETRANGE("Record ID",XRecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Record ID" := RecRef.RECORDID;
FieldRef1 := RecRef.FIELD(1); // "Employee No."
IntegrationRecord."Employee No." := FieldRef1.VALUE;
IntegrationRecord.MODIFY;
END;
END;
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;

PROCEDURE EnableConnector@7();
VAR
WebServiceLog@1200050000 : Record 10001017;
BEGIN
SetupWebService;
SetupIntegrationTables;
END;

PROCEDURE DisableConnector@10();
VAR
PunchSetup@1000 : Record 10001049;
Company@1002 : Record 2000000006;
MultipleCompanies@1003 : Boolean;
BEGIN
MultipleCompanies := FALSE;
Company.SETFILTER(Name,’&lt;&gt;%1′,COMPANYNAME);
IF Company.FINDSET THEN BEGIN
REPEAT
PunchSetup.CHANGECOMPANY(Company.Name);
IF PunchSetup.GET THEN
IF PunchSetup."Enable Connector" THEN
MultipleCompanies := TRUE;
UNTIL (Company.NEXT = 0) OR MultipleCompanies;
END;

IF NOT MultipleCompanies THEN BEGIN
DeleteWebService;
DeleteIntegrationTables;
END;
END;

LOCAL PROCEDURE SetupWebService@11();
VAR
WebService@1001 : Record 2000000076;
Objects@1002 : Record 2000000038;
IntegrationPage@1004 : Record 5150;
BEGIN
WITH WebService DO BEGIN
INIT;
"Object Type" := "Object Type"::Codeunit;
"Service Name" := ‘NAVPunch’;
"Object ID" := CODEUNIT::"Punch Web Service Mgt.";
Published := TRUE;
IF NOT INSERT THEN
MESSAGE(Text001,TABLECAPTION,"Service Name");
END;
END;

LOCAL PROCEDURE DeleteWebService@1200050001();
VAR
WebService@1001 : Record 2000000076;
Objects@1002 : Record 2000000038;
IntegrationPage@1004 : Record 5150;
BEGIN
WITH WebService DO BEGIN
IF GET("Object Type"::Codeunit,’NAVPunch’) THEN
DELETE;
END;
END;

LOCAL PROCEDURE SetupIntegrationTables@8();
BEGIN
InitializeIntegrationRecords(DATABASE::Employee);
InitializeIntegrationRecords(DATABASE::"Punch Employee Setup");
END;

LOCAL PROCEDURE DeleteIntegrationTables@1200050008();
BEGIN
DeleteIntegrationRecords(DATABASE::Employee);
DeleteIntegrationRecords(DATABASE::"Punch Employee Setup");
END;

LOCAL PROCEDURE InitializeIntegrationRecords@9(TableID@1000 : Integer);
VAR
RecRef@1002 : RecordRef;
BEGIN
WITH RecRef DO BEGIN
OPEN(TableID,FALSE);
IF FINDSET(FALSE) THEN
REPEAT
InsertUpdateIntegrationRecord(RecRef,CURRENTDATETIME);
UNTIL NEXT = 0;
CLOSE;
END;
END;

LOCAL PROCEDURE UpdateParentIntegrationRecord@17(RecRef@1000 : RecordRef;TimeStamp@1008 : DateTime);
VAR
Employee@1200050000 : Record 5200;
FieldRef1@1004 : FieldRef;
ParentRecRef@1006 : RecordRef;
BEGIN
CASE RecRef.NUMBER OF
DATABASE::"Punch Employee Setup":
BEGIN
FieldRef1 := RecRef.FIELD(1); // "Employee No."
IF Employee.GET(FieldRef1.VALUE) THEN BEGIN
ParentRecRef.GETTABLE(Employee);
InsertUpdateIntegrationRecord(ParentRecRef,TimeStamp);
END;
END;
END;
END;

LOCAL PROCEDURE InsertUpdateIntegrationRecord@16(RecRef@1000 : RecordRef;IntegrationLastModified@1001 : DateTime);
VAR
IntegrationRecord@1002 : Record 10001021;
FieldRef1@1200050000 : FieldRef;
BEGIN
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
WITH IntegrationRecord DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
IF FINDFIRST THEN BEGIN
"Modified On" := IntegrationLastModified;
MODIFY;
END ELSE BEGIN
INIT;
"Integration ID" := CREATEGUID;
"Record ID" := RecRef.RECORDID;
"Table ID" := RecRef.NUMBER;
"Modified On" := IntegrationLastModified;
FieldRef1 := RecRef.FIELD(1); // "Employee No."
"Employee No." := FieldRef1.VALUE;
INSERT;
END;
END;
END;
END;

LOCAL PROCEDURE DeleteIntegrationRecords@1200050004(TableID@1200050000 : Integer);
VAR
IntegrationRecord@1200050001 : Record 10001021;
BEGIN
WITH IntegrationRecord DO BEGIN
SETRANGE("Table ID",TableID);
IF NOT ISEMPTY THEN
DELETEALL;
END;
END;

LOCAL PROCEDURE IsIntegrationRecord@6(TableID@1000 : Integer) : Boolean;
BEGIN
IF TableID IN
[DATABASE::Employee]
THEN
EXIT(TRUE);

EXIT(FALSE);
END;

LOCAL PROCEDURE IsIntegrationRecordChild@18(TableID@1000 : Integer) : Boolean;
BEGIN
IF TableID IN
[DATABASE::"Punch Employee Setup"]
THEN
EXIT(TRUE);

EXIT(FALSE);
END;

BEGIN
END.
}
}[/code]
This makes it easy for me to find out if the employee has changed since the last synchronization with this function.
[code]EmployeeModified(VAR Employee : Record Employee;LastModified : DateTime) : Boolean
IF LastModified = 0DT THEN EXIT(TRUE);

IF Integration.ISEMPTY THEN
EXIT(Employee."Last Date Modified" &gt;= DT2DATE(LastModified));

RecRef.GETTABLE(Employee);
Integration.SETCURRENTKEY("Record ID");
Integration.SETRANGE("Record ID",RecRef.RECORDID);
IF Integration.FINDFIRST THEN
EXIT(Integration."Modified On" &gt; LastModified)
ELSE
EXIT(FALSE);[/code]