Let’s say you have a large number of companies and you want to compare data in a specific table to see differences. This script will give you all columns except the timestamp column and display all data in the No. Series table by company.

 
declare @table nvarchar(30) = 'No_ Series'
declare @sql nvarchar(MAX), @CompanyName varchar(30)
declare @columns nvarchar(max)=''
declare @col nvarchar(100) =''
declare @num_rows int =0
declare @dat_type nvarchar(30)
set @CompanyName = (select top(1) Name from Company)
declare cols cursor for
select column_name, data_type
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ''+@CompanyName+'$'+@table+''
open cols
fetch next from cols into @col, @dat_type
while(@@FETCH_STATUS = 0)
begin
set @num_rows +=1
if @dat_type = 'timestamp'
set @columns += 'null'
else 
set @columns += '['+@col+']'
fetch next from cols into @col, @dat_type
if @@FETCH_STATUS=0
set @columns += ','
end
close cols
deallocate cols
SELECT @sql = 'SELECT TOP(0) space(30) AS CompanyName, * INTO ##TempData FROM ['+ @CompanyName +'$'+@table+']'
exec (@sql)
DECLARE CompanyCursor CURSOR FORWARD_ONLY FOR
SELECT REPLACE([Name],'.','_')
FROM [Company]
OPEN CompanyCursor
FETCH NEXT FROM CompanyCursor INTO @CompanyName

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = '
INSERT INTO ##TempData SELECT ''' + @CompanyName + ''', ' + @columns + ' from ['+@CompanyName+'$'+@table+']' 
exec (@sql) 
FETCH NEXT FROM CompanyCursor INTO @CompanyName
END
CLOSE CompanyCursor
DEALLOCATE CompanyCursor
ALTER TABLE ##TempData DROP COLUMN timestamp
select * from ##TempData
DROP TABLE ##TempData

Using basic authentication you store a user name and a web access key in the requesting app and does not have to deale with expiring tokens. Basic authentication does work in production even thoug Microsoft says that Oath shall/should (they actually say both in the same document) be used in production.

AAD authentication

First we take a look at AAD authentication/Oath and how to set that up. The main tasks are:

1. Create an app in Azure AD
2. Get the Access Token in Postman
3. Use the Access Token in Postman to access the API

1. Create the App in Azure AD

The AAD App act’s like a contract between the BC API and the requesting app.

1. Login to Azure Active Directory
2. Select App Registrations
3. Click “New registration”
4. In “Register an application”:
   1. Give the app a name
   2. Select from where your app is supposed to be accessed in “Supported Account Types”
   3. Set “https://businesscentral.dynamics.com” as redirect URL
   4. Click on “Register” to create the app.
5. Click on “Certificates & Secrets”
6. Click on “New Client Secret”
7. In “Add a client secret”:
   1. Select if and when the client secret expires
8. Copy the secret and save it. You will need this in Postman.
9. Click on “API permissions”
10. Click on “Add a permission”
11. In “Request API permissions”:
    1. Click on “Dynamics 365 Business Central”
    2. Click on “Delegated permissions”
    3. Select “user_impersonation”
    4. Click on “Add permissions” in the bottom of the page
12. Back in “API permissions”:
    1. Click on “Grant admin consent for <organization name>”
    2. Click on “Yes” to confirm.
13. Click on “Overview” and copy the “Application (client) ID”. You will need this in Postman.

2. Get the Access Token in Postman

In this step you will create a collection in Postman and request an access token that is used in the requests you create in the collection.

1. Create a new collection in Postman
2. Give the collection a name and select the Authorization tab
3. Select Type = Oauth 2.0
4. Click on “Get New Access Token”
5. Fill in the following:
   1. A Token Name
   2. “Grant Type” = “Authorization Code”
   3. “Callback URL” = “https://businesscentral.dynamics.com/”
   4. “Auth URL” = “https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize”
   5. “Access Token URL” = “https://login.microsoftonline.com/organizations/oauth2/v2.0/token”
   6. “Client ID” = <your client ID>
   7. “Client Secret” = <your client secret>
   8. “Scope” = “https://api.businesscentral.dynamics.com/user_impersonation”
6. Click on “Request Token”
7. Authenticate with a valid user and password.
8. Click on “Use Token”
9. Click on “Create” to create the collection

3. Access the API

In this step we will create requests in the collection. The requests will inheret the authorization from the collection. Our aim is to retrieve a list of customers.

My example is based on the environment “sandbox”. Change that to whatever environment you want to access (e.g. “production”).

1. Expand the collection in the list of collections
2. Create a request to list companies in the BC database
   1. Click on “Add requests” or the three dots at the right and select “Add request”
   2. Give the request a name (e.g. “Companies”)
   3. Click on “Save to <your collection name>”
   4. Select the request in the left pane
   5. Type in “https://api.businesscentral.dynamics.com/v2.0/sandbox/api/v1.0/companies” as the URL
   6. Click on “Send” and you should get a list of companies.
   7. Copy the id of one of your companies. You will need it in the next step
   8. Click on “Save” to save the request
3. Create another request
   1. Give the request the name “Customers”
   2. Click on “Save to <your collection name>”
   3. Type in https://api.businesscentral.dynamics.com/v2.0/sandbox/api/v1.0/companies(<your company id>)/customers
   4. Click on “Send” and you should get a list of customers

Did you make it all the way down here? Good job!

What about using basic authentication? Well, that’s much easier. There’s no need to mess around in AAD.

Basic authentication

1. Create a user in BC with a web access key
2. Create a collection in Postman with basic authentication. The username shall not include the domain name
3. Create a request to list your companies. The URL now follows this syntax: https://api.businesscentral.dynamics.com/v2.0/<domain>/<environment>/api/v1.0/companies

Basic was a bit easier But is it supported by Microsoft in the long run? I don’t know.

Make sure the developers tab is visible in Excel. Then add the tables and permissions in the excel table and click export on the developers tab and you have a permission set.

• Create SOAP request XML
• Do an HTTP Post using httpclient
• Read the XML in the response and do something with that.

I have used a codeunit for XML DOM management created by Divesh Bora which makes things a lot easier.

I hope you find this useful and helps you on your way to move from C/AL to AL.

pageextension 57900 CurrencyListExtension extends Currencies
{
    actions
    {
        addafter(UpdateExchangeRates)
        {
            action(DownloadExchRates)
            {
                ApplicationArea = All;
                CaptionML = ENU = 'Get Exchange Rates from State Bank of Sweden', SVE = 'Hämta valutakurser från Sveriges Riksbank';
                Image = CurrencyExchangeRates;
                Promoted = true;
                PromotedCategory = Category4;

                trigger OnAction();
                var
                    DownloadExchRates: codeunit "SE State Bank Exch Rates";
                    TxtDownloadSucceeded: TextConst ENU = 'Exchange Rates updated successfully', SVE = 'Valutakurserna har uppdateras';
                    JobQueueEntry: Record "Job Queue Entry";
                begin
                    if DownloadExchRates.Run(JobQueueEntry) then
                      message(TxtDownloadSucceeded);
                end;
            }
        }
    }
}

codeunit 57900 "SE State Bank Exch Rates"
{
    // https://swea.riksbank.se/sweaWS/docs/api/call/getLatestInterestAndExchangeRates.htm
    // https://swea.riksbank.se/sweaWS/wsdl/sweaWS_ssl.wsdl

    TableNo = "Job Queue Entry";
    var
      XMLDOMMgmt: Codeunit "XML DOM Mgt.";
      SoapNS : Label 'http://www.w3.org/2003/05/soap-envelope';
      XsdNS : Label 'http://swea.riksbank.se/xsd';
      URL: label 'https://swea.riksbank.se:443/sweaWS/services/SweaWebServiceHttpSoap12Endpoint';
      NsMgr : XmlNamespaceManager;
    
    trigger OnRun();
    var 
      GLSetup : Record "General Ledger Setup";
      XMLDoc : XmlDocument;
      Currency : Record Currency;
      CurrNode : XmlNode;
      CurrNode2 : XmlNode;
      CurrNodeList : XmlNodeList;
      CurrExchRate : Record "Currency Exchange Rate";
      Unit : Decimal;
      DebugFile: File;
      OutStr: OutStream;
      NsMgr: XmlNamespaceManager;
    begin
      GLSetup.GET;
      GLSetup.TESTFIELD("LCY Code", 'SEK');
      if Currency.FINDSET then
        with XMLDOMMgmt do 
          begin
            AddRootElementWithPrefix(XMLDoc, 'Envelope', 'soap', SoapNS, CurrNode2);
            SetUtf8Declaration(XMLDoc);
            CurrNode := CurrNode2;
            AddPrefix(CurrNode, 'xsd', XsdNS);
            CurrNode := CurrNode2;
            AddElement(CurrNode, 'Header', '', SoapNs, CurrNode2);
            CurrNode := CurrNode2;
            AddElement(Currnode, 'Body', '', SoapNs, CurrNode2);
            CurrNode := CurrNode2;
            AddElement(Currnode, 'getLatestInterestAndExchangeRates', '', XsdNS, CurrNode2);
            CurrNode := CurrNode2;
            if GLOBALLANGUAGE = 1053 then
              AddElement(CurrNode, 'languageid', 'sv', '', CurrNode2)
            else
              AddElement(CurrNode, 'languageid', 'en', '', CurrNode2);
            repeat
              AddElement(CurrNode,'seriesid', GLSetup."LCY Code" + Currency.Code + 'PMI', '', CurrNode2);
            until Currency.NEXT = 0;

            HTTPPost(XMLDoc, URL);

            GetRootNode(XMLDoc, CurrNode);
            AddNameSpace(NsMgr, 'soap', SoapNS);
            AddNameSpace(NsMgr, 'ns2', XsdNS);
            if FindNodesWithNamespaceManager(CurrNode, '//soap:Body/ns2:getLatestInterestAndExchangeRatesResponse/return/groups/series', NsMgr, CurrNodeList) then
              foreach CurrNode in CurrNodeList do
                with CurrExchRate do
                  begin
                    INIT;
                    if FindNode(CurrNode, 'seriesid', CurrNode2) then
                      "Currency Code" := COPYSTR(CurrNode2.AsXmlElement.InnerText, 4, 3);
                    if FindNode(CurrNode, 'unit', CurrNode2) then
                      Unit := FormatDecmialTextImport(CurrNode2.AsXmlElement.InnerText);
                    "Relational Exch. Rate Amount" := 1;
                    "Relational Adjmt Exch Rate Amt" := 1;
                    if FindNode(CurrNode, 'resultrows', CurrNode2) then
                      begin
                        CurrNode := CurrNode2;
                        if FindNode(CurrNode, 'date', CurrNode2) then
                          "Starting Date" := FormatDateTextImport(CurrNode2.AsXmlElement.InnerText);
                        if FindNode(CurrNode, 'value', CurrNode2) then
                          begin
                            "Exchange Rate Amount" := FormatDecmialTextImport(CurrNode2.AsXmlElement.InnerText);
                            "Exchange Rate Amount" := 1 / "Exchange Rate Amount" * Unit;
                            "Adjustment Exch. Rate Amount" := "Exchange Rate Amount";
                            if not INSERT then
                              MODIFY;
                          end;
                      end;
                  end;
          end;
    end;  

    local procedure HTTPPost(var XmlDoc : XmlDocument;URL : Text[250]) : Boolean;
    var
        MyHTTPClient: HttpClient;
        RequestMessage: HttpRequestMessage;
        ResponseMessage: HttpResponseMessage;
        Headers: HttpHeaders;
        Content: HttpContent;
        CurrNodeList : XmlNodeList;
        XML_text: text;
        InStr: InStream;
        OutStr: OutStream;
    begin
        RequestMessage.SetRequestUri(URL);
        RequestMessage.Method('POST');
        XmlDoc.WriteTo(XML_text); 
        Content.WriteFrom(XML_text);
        Content.GetHeaders(Headers);
        Headers.Remove('Content-Type');
        Headers.Add('Content-Type', 'application/soap+xml;charset=UTF-8;action="urn:getLatestInterestAndExchangeRates"');
        RequestMessage.Content := Content;
        MyHTTPClient.send(RequestMessage, ResponseMessage);
        if not ResponseMessage.IsSuccessStatusCode then
          error(format(ResponseMessage.HttpStatusCode) +' , ' + ResponseMessage.ReasonPhrase);
        if ResponseMessage.IsSuccessStatusCode then
           begin
             ResponseMessage.Content.ReadAs(InStr);
             XMLDOMMgmt.LoadXMLDocumentFromInStream(InStr, XmlDoc);
             XMLDOMMgmt.SetUtf8Declaration(XmlDoc);
           end
         else
           error(format(ResponseMessage.HttpStatusCode));
    end;

    local procedure FormatDecmialTextImport(TextDec : Text[30]) : Decimal;
    var
        TempDec : Decimal;
    begin
        if TextDec = '' then
          exit(0)
        else
          begin
            TempDec := 0.01;
            EVALUATE(TempDec, CONVERTSTR(TextDec, '.', COPYSTR(FORMAT(TempDec),2,1)));
            exit(TempDec);
          end;
    end;

    local procedure FormatDateTextImport(DateText : Text) : Date;
    var
        MyYear : Integer;
        MyMonth : Integer;
        MyDay : Integer;
    begin
        EVALUATE(MyYear, COPYSTR(DateText, 1, 4));
        EVALUATE(MyMonth, COPYSTR(DateText, 6, 2));
        EVALUATE(MyDay, COPYSTR(DateText, 9, 2));
        exit(DMY2DATE(MyDay, MyMonth, MyYear));;
    end;
}

codeunit 57901 "XML DOM Mgt."
{   
    https://diveshboramsdnavblog.wordpress.com/2018/03/09/vs-code-xml-dom-management-part-2/
    trigger OnRun();
    begin
    end;

    procedure AddElement(var pXMLNode: XmlNode; pNodeName: Text; pNodeText: Text; pNameSpace: Text; var pCreatedNode: XmlNode): Boolean
    begin
        IF pNodeText <> '' then
            pCreatedNode := XmlElement.Create(pNodeName, pNameSpace, pNodeText).AsXmlNode
        else
            pCreatedNode := XmlElement.Create(pNodeName, pNameSpace).AsXmlNode;
        Exit(pXMLNode.AsXmlElement.Add(pCreatedNode));
    end;

    procedure AddRootElement(var pXMLDocument: XmlDocument; pNodeName: Text; var pCreatedNode: XmlNode): Boolean
    begin
        pXMLDocument := XmlDocument.Create;
        pCreatedNode := XmlElement.Create(pNodeName).AsXmlNode;
        Exit(pXMLDocument.Add(pCreatedNode));
    end;

    procedure AddRootElementWithPrefix(var pXMLDocument: XmlDocument; pNodeName: Text; pPrefix: Text; pNameSpace: text; var pCreatedNode: XmlNode): Boolean
    begin
        pXMLDocument := XmlDocument.Create;
        pCreatedNode := XmlElement.Create(pNodeName, pNameSpace).AsXmlNode;
        pCreatedNode.AsXmlElement.Add(XmlAttribute.CreateNamespaceDeclaration(pPrefix, pNameSpace));
        Exit(pXMLDocument.Add(pCreatedNode));
    end;

    procedure AddElementWithPrefix(var pXMLNode: XmlNode; pNodeName: Text; pNodeText: Text; pPrefix: Text; pNameSpace: text; var pCreatedNode: XmlNode): Boolean
    begin
        IF pNodeText <> '' then
            pCreatedNode := XmlElement.Create(pNodeName, pNameSpace, pNodeText).AsXmlNode
        else
            pCreatedNode := XmlElement.Create(pNodeName, pNameSpace).AsXmlNode;
        pCreatedNode.AsXmlElement.Add(XmlAttribute.CreateNamespaceDeclaration(pPrefix, pNameSpace));
        exit(pXMLNode.AsXmlElement.Add(pCreatedNode));
    end;

    procedure AddPrefix(var pXMLNode: XmlNode; pPrefix: Text; pNameSpace: text): Boolean
    begin
        pXMLNode.AsXmlElement.Add(XmlAttribute.CreateNamespaceDeclaration(pPrefix, pNameSpace));
        exit(true);
    end;

    procedure AddAttribute(var pXMLNode: XmlNode; pName: Text; pValue: Text): Boolean
    begin
        pXMLNode.AsXmlElement.SetAttribute(pName, pValue);
        exit(true);
    end;

    procedure AddAttributeWithNamespace(var pXMLNode: XmlNode; pName: Text; pNameSpace: text; pValue: Text): Boolean
    begin
        pXMLNode.AsXmlElement.SetAttribute(pName, pNameSpace, pValue);
        exit(true);
    end;

    procedure FindNode(pXMLRootNode: XmlNode; pNodePath: Text; var pFoundXMLNode: XmlNode): Boolean
    begin
        exit(pXMLRootNode.SelectSingleNode(pNodePath, pFoundXMLNode));
    end;

    procedure GetRootNode(pXmlDoc: XmlDocument; var pFoundXMLNode: XmlNode): Boolean
    var
        lXmlElement : XmlElement;
    begin
        pXMLDoc.GetRoot(lXmlElement);
        pFoundXMLNode := lXmlElement.AsXmlNode;
    end;

    procedure FindNodeWithNameSpace(pXMLRootNode: XmlNode; pNodePath: Text; pPrefix: Text; pNamespace: Text; var pFoundXMLNode: XmlNode): Boolean
    var
        lXmlNsMgr: XmlNamespaceManager;
        lXMLDocument: XmlDocument;
    begin

        if pXMLRootNode.IsXmlDocument then
            lXmlNsMgr.NameTable(pXMLRootNode.AsXmlDocument.NameTable)
        else begin
            pXMLRootNode.GetDocument(lXMLDocument);
            lXmlNsMgr.NameTable(lXMLDocument.NameTable);
        end;
        lXMLNsMgr.AddNamespace(pPrefix, pNamespace);
        exit(pXMLRootNode.SelectSingleNode(pNodePath, lXmlNsMgr, pFoundXMLNode));
    end;

    procedure FindNodesWithNameSpace(pXMLRootNode: XmlNode; pXPath: Text; pPrefix: Text; pNamespace: Text; var pFoundXmlNodeList: XmlNodeList): Boolean
    var
        lXmlNsMgr: XmlNamespaceManager;
        lXMLDocument: XmlDocument;
    begin
        if pXMLRootNode.IsXmlDocument then
            lXmlNsMgr.NameTable(pXMLRootNode.AsXmlDocument.NameTable)
        else begin
            pXMLRootNode.GetDocument(lXMLDocument);
            lXmlNsMgr.NameTable(lXMLDocument.NameTable);
        end;
        lXMLNsMgr.AddNamespace(pPrefix, pNamespace);
        exit(FindNodesWithNamespaceManager(pXMLRootNode, pXPath, lXmlNsMgr, pFoundXmlNodeList));
    end;

    procedure FindNodesWithNamespaceManager(pXMLRootNode: XmlNode; pXPath: Text; pXmlNsMgr: XmlNamespaceManager; var pFoundXmlNodeList: XmlNodeList): Boolean
    begin
        IF not pXMLRootNode.SelectNodes(pXPath, pXmlNsMgr, pFoundXmlNodeList) then
            exit(false);
        IF pFoundXmlNodeList.Count = 0 then
            exit(false);
        exit(true);
    end;

    procedure FindNodeXML(pXMLRootNode: XmlNode; pNodePath: Text): Text
    var
        lFoundXMLNode: XmlNode;
    begin
        IF pXMLRootNode.SelectSingleNode(pNodePath, lFoundXMLNode) then
            Exit(lFoundXMLNode.AsXmlElement.InnerXml);
    end;

    procedure FindNodeText(pXMLRootNode: XmlNode; pNodePath: Text): Text
    var
        lFoundXMLNode: XmlNode;
    begin
        IF pXMLRootNode.SelectSingleNode(pNodePath, lFoundXMLNode) then
            Exit(lFoundXMLNode.AsXmlElement.InnerText);
    end;

    procedure FindNodeTextWithNameSpace(pXMLRootNode: XmlNode; pNodePath: Text; pPrefix: Text; pNamespace: Text): Text
    var
        lXmlNsMgr: XmlNamespaceManager;
        lXMLDocument: XmlDocument;
    begin

        if pXMLRootNode.IsXmlDocument then
            lXmlNsMgr.NameTable(pXMLRootNode.AsXmlDocument.NameTable)
        else begin
            pXMLRootNode.GetDocument(lXMLDocument);
            lXmlNsMgr.NameTable(lXMLDocument.NameTable);
        end;
        lXMLNsMgr.AddNamespace(pPrefix, pNamespace);
        Exit(FindNodeTextNs(pXMLRootNode, pNodePath, lXmlNsMgr));
    end;

    procedure FindNodeTextNs(pXMLRootNode: XmlNode; pNodePath: Text; pXmlNsMgr: XmlNamespaceManager): Text
    var
        lFoundXMLNode: XmlNode;
    begin
        IF pXMLRootNode.SelectSingleNode(pNodePath, pXmlNsMgr, lFoundXMLNode) then
            Exit(lFoundXMLNode.AsXmlElement.InnerText);
    end;

    procedure FindNodes(pXMLRootNode: XmlNode; pNodePath: Text; var pFoundXMLNodeList: XmlNodeList): Boolean
    begin
        IF not pXMLRootNode.SelectNodes(pNodePath, pFoundXmlNodeList) then
            exit(false);
        IF pFoundXmlNodeList.Count = 0 then
            exit(false);
        exit(true);
    end;

    procedure FindAttribute(pXMLNode: XmlNode; var pXmlAttribute: XmlAttribute; pAttributeName: Text): Boolean
    begin
        exit(pXMLNode.AsXmlElement.Attributes.Get(pAttributeName, pXmlAttribute));
    end;

    procedure GetAttributeValue(pXMLNode: XmlNode; pAttributeName: Text): Text
    var
        lXmlAttribute: XmlAttribute;
    begin
        If pXMLNode.AsXmlElement.Attributes.Get(pAttributeName, lXmlAttribute) then
            exit(lXmlAttribute.Value);
    end;

    procedure AddDeclaration(var pXMLDocument: XmlDocument; pVersion: Text; pEncoding: Text; pStandalone: Text)
    var
        lXmlDeclaration: XmlDeclaration;
    begin
        lXmlDeclaration := XmlDeclaration.Create(pVersion, pEncoding, pStandalone);
        pXMLDocument.SetDeclaration(lXmlDeclaration);
    end;

    procedure AddGroupNode(var pXMLNode: XmlNode; pNodeName: Text)
    var
        lXMLNewChild: XmlNode;
    begin
        AddElement(pXMLNode, pNodeName, '', '', lXMLNewChild);
        pXMLNode := lXMLNewChild;
    end;

    procedure AddNode(var pXMLNode: XmlNode; pNodeName: Text; pNodeText: Text)
    var
        lXMLNewChild: XmlNode;
    begin
        AddElement(pXMLNode, pNodeName, pNodeText, '', lXMLNewChild);
    end;

    procedure AddLastNode(var pXMLNode: XmlNode; pNodeName: Text; pNodeText: Text)
    var
        lXMLNewChild: XmlNode;
        lXMLElement: XmlElement;
    begin
        AddElement(pXMLNode, pNodeName, pNodeText, '', lXMLNewChild);
        if pXMLNode.GetParent(lXMLElement) then
            pXMLNode := lXMLElement.AsXmlNode;
    end;

    procedure AddNamespaces(var pXmlNsMgr: XmlNamespaceManager; pXMLDocument: XmlDocument)
    var
        lXmlAttributeCollection: XmlAttributeCollection;
        lXmlAttribute: XmlAttribute;
        lXMLElement: XmlElement;
    begin
        pXmlNsMgr.NameTable(pXMLDocument.NameTable);
        pXMLDocument.GetRoot(lXMLElement);
        lXmlAttributeCollection := lXMLElement.Attributes;
        IF lXMLElement.NamespaceUri <> '' then
            pXmlNsMgr.AddNamespace('', lXMLElement.NamespaceUri);
        Foreach lXmlAttribute in lXmlAttributeCollection do
        begin
            if StrPos(lXmlAttribute.Name, 'xmlns:') = 1 then
                pXmlNsMgr.AddNamespace(DELSTR(lXmlAttribute.Name, 1, 6), lXmlAttribute.Value);
        end;
    end;
    
    procedure AddNameSpace(var pXmlNsMgr: XmlNamespaceManager; pPrefix: text; pNamespace: text);
    begin
        pXmlNsMgr.AddNamespace(pPrefix, pNamespace);
    end;

    procedure XMLEscape(pText: Text): Text
    var
        lXMLDocument: XmlDocument;
        lRootXmlNode: XmlNode;
        lXmlNode: XmlNode;
    begin
        lXMLDocument := XmlDocument.Create;
        AddElement(lRootXmlNode, 'XMLEscape', pText, '', lXmlNode);
        exit(lXmlNode.AsXmlElement.InnerXml);
    end;

    procedure LoadXMLDocumentFromText(pXMLText: Text; var pXMLDocument: XmlDocument)
    begin
        IF pXMLText = '' then
            exit;
        XmlDocument.ReadFrom(pXMLText, pXMLDocument);
    end;

    procedure LoadXMLNodeFromText(pXMLText: Text; var pXMLNode: XmlNode)
    var
        lXmlDocument: XmlDocument;
    begin
        LoadXMLDocumentFromText(pXMLText, lXmlDocument);
        pXMLNode := lXmlDocument.AsXmlNode;
    end;

    procedure LoadXMLDocumentFromInStream(pInStream: InStream; var pXMLDocument: XmlDocument)
    begin
        XmlDocument.ReadFrom(pInStream, pXMLDocument);
    end;

    procedure LoadXMLNodeFromInStream(pInStream: InStream; var pXMLNode: XmlNode)
    var
        lXmlDocument: XmlDocument;
    begin
        LoadXMLDocumentFromInStream(pInStream, lXmlDocument);
        pXMLNode := lXmlDocument.AsXmlNode;
    end;

    procedure RemoveNamespaces(pXmlText: Text): Text
    var
        XMLDOMMgt: Codeunit "XML DOM Management";
    begin
        Exit(XMLDOMMgt.RemoveNamespaces(pXmlText));
    end;

    procedure SetUtf8Declaration(var pXMLDocument: XmlDocument);
    var
        Declaration : XmlDeclaration;
    begin
        Declaration := XmlDeclaration.Create('1.0', 'utf-8', 'yes');
        pXMLDocument.SetDeclaration(Declaration);
    end;
}

 PROCEDURE DT2UnixDTUTC@81237(DtIn@81200 : DateTime) : BigInteger;
 VAR
   DtOffset@81203 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.DateTimeOffset";
   DtUtc@81206 : DateTime;
   Duration@81208 : Duration;
   TempDate@81209 : Date;
   TempTime@81210 : Time;
 BEGIN
   EVALUATE(TempDate, COPYSTR(FORMAT(DtIn, 0, 9), 1, 10));
   EVALUATE(TempTime, COPYSTR(FORMAT(DtIn, 0, 9), 12, 8));
   DtUtc := CREATEDATETIME(TempDate, TempTime);
   Duration := DtIn - DtUtc;
   DtOffset := DtOffset.DateTimeOffset(DtIn);
   EXIT(DtOffset.ToUnixTimeMilliseconds + Duration);
 END;

I soon found Gunnar Gestssons blog post on “JSON meets NAV“. Many thanks to Gunnar for doing the hard work! All I did was some tweaking to make it work with Zendesk.

On the link below you’ll find a textfile with two codeunits (99100 and 99101). 99100 is based on Gunnars work and in 99101 there’s some code which shows how to work with Zendesk. All you need to do is to setup a Zendesk trial account and do some setup there (more details in the documentation trigger in cu 99101). Then change some hard coded parameters in codeunit 99101 and you can create tickets in Zendesk.

I hope this can be useful for some of you guys out there. Enjoy!

Objects: Zendesk demo

• Unblock an individual file by right clicking the file and click the checkbox to unblock.
• Open Windows PowerShell or an elevated Windows PowerShell depending on if your user account has access rights to where the folder containing the blocked file(s) is located and execute this: get-childitem -recurse |Unblock-File
• Completely disable the zone blocking ib the gpe: http://winaero.com/blog/disable-downloaded-files-from-being-blocked-in-windows-10/

Happy updating

I didn’t want to spend more time on this than necessary, so I removed the definition, exported the same definition from a Cronus database and imported it in the customers database and it all started to work.

BTW: When you import a definition you must change field type in the import dialog. The developer at Microsoft forgot to set it to xml. Otherwise you wont find the xml-file.

Let’s take a look at it:

Here you can see how you can use TableType, ExternalName and ExternalSchema instead of LinkedObject. In NAV this table is called “My Table” but in SQL it’s called “mytable”.

You can also set ExternalName for fields. Below I want to use the field name “No.” although it’s called “number” in the SQL table.

The connection to the server, database and the user credentials is controlled with C/AL.

Here’s a sample:

UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName', 'Data Source='MyServerName';Initial Catalog='MyDatabaseName';User ID='MySQLUserID';Password='MyPassword');

SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

This code will first try to unregister any existing connection to the database I want to connect to. Then it will register using a connectionstring (which you of course shouldn’t hard code like this) and then it will set the connection as the default connection.

The connection needs to be established on each login so it would make sense to store server name, database name and credentials in a setup table and register the datasource on login.

According to the MSDN documentation any rollbacks will also take place in the external tables. I haven’t tested that myself though.

I’m on my first project using this feature so I’ll probably update this post later on when I’ve got some more time on this functionality.

This might not be an issue until you try to rename the field that is set as AltSearchField. Then you’ll end up with an error like “You cannot enter ‘YourSearchName’ in FieldNo.”. The only way to solve this is to export the object to a txt file and manually remove the property.