Dynamics 365 Business Central on-premise and extensions: what happens to my third-party integrations?

In a real-world Microsoft Dynamics NAV implementations, is quite common to see integrations with third-party external applications. These integrations are normally based on three different technologies:

1. Text file integrations (XMLPort)
2. SQL integrations (direct access to SQL tables)
3. Web Services or APIs integrations

With Microsoft Dynamics 365 Business Central on-premise you have the same types of choice for handling your integrations with external applications and you can easily move all of them from NAV to D365BC on-premise without too much effort. All of them except point 2 if you’re using extensions instead of C/AL 🙂

In the following example, I’ve reproduced (with simplicity) a quite common scenario I see every day on many Microsoft Dynamics NAV implementations: a manufacturing company has production machines that transmit real-time production data to NAV by using connectors that directly writes SQL Server tables defined in the NAV database (in this sample the integration table is the ProductionData table). At the end of a production order, the machine writes a field in the NAV Production Order table to signal that the production order data are completely collected and registered.

When moving such integration to Dynamics 365 Business Central on-premise and the extension-based architecture, what we have to do now is using the Modern Development Environment and:

1. Define the ProductionData data table in AL (and the relative List page if needed).
2. Write the Production Order tableextension object to add the new required fields.

Here I’ve created an extension called ManufacturingIntegration and this is the AL code for the ProductionData table definition:

table 50115 ProductionData

{

  DataClassification = CustomerContent;

  fields

  {

    field(1; ProductionOrder; Code[20])

    {

      Caption = 'Production Order No.';

      DataClassification = CustomerContent;

    }

    field(2; OperationNo; Code[20])

    {

      Caption = 'Operation No.';

      DataClassification = CustomerContent;

    }

    field(3; OutputQty; Decimal)

    {

      Caption = 'Output Qty.';

      DataClassification = CustomerContent;

    }

    field(4; ScrapQty; Decimal)

    {

      Caption = 'Scrap Qty.';

      DataClassification = CustomerContent;

    }

  }

  keys

  {

    key(PK; ProductionOrder, OperationNo)

    {

      Clustered = true;

    }

  }

}

tableextension 50115 ProductionOrderExt extends "Production Order"

{

  fields

  {

    field(50115; ProductionDataRegistered; Boolean)

    {

      Caption = 'Production Data Registered';

      DataClassification = CustomerContent;

    }

  }

}

Now deploy this extension to Dynamics 365 Business Central on-premise:

What happens now on our SQL Server database?

The same external application in the old Microsoft Dynamics NAV actually writes data in the following SQL tables:

• dbo.[YourCompany$ProductionData]
• dbo.[YourCompany$Production Order]

And now?

The SQL tables created by our extension now are the following:

• dbo.[YourCompany$ProductionData$extensionID]
• dbo.[YourCompany$Production Order$extensionID]

where extensionID is the ID of our extension (that we can see in app.json file):

So, what you have to remember? When using AL extensions and you strictly need to maintain the direct SQL Server integration with your third-party application remember that:

• The new tables must be called with the extensionID prefix
• The new added fields on standard tables are in a separate table (same name as the extended table + extensionID).
• If your third party application reads the extended table (in the above example is [YourCompany$Production Order$extensionID], in this table it will find only the primary key of the original table plus the newly added field (in the previous example the field is ProductionDataRegistered). If he needs to perform a SELECT with the newly added data + the original table’s data, now this cannot be done with a single query on the Production Order table but instead you need to perform a JOIN:

What is the conclusion? If you have in place this type of integration and you’re forced to keep it alive, remember that you have to alert your third-party vendor to change the integration queries. As-is, they will not work.

Obviously, if you can recreate the integration by using web services you don’t have to keep in mind this “problem” and you will be SaaS-ready 🙂