LinkedObjects on Dynamics 365 Business Central: be careful now!

One of the common (and basic) way to create integrations between systems in Microsoft Dynamics NAV in the past was to directly work at the SQL Server level and create tables linked to SQL objects by using the table’s LinkedObject property.

With Microsoft Dynamics NAV, you can create a SQL Server view, then create a table with the same name as the view and set the LinkedObject property to true. Magically, this table is “linked” to the view and you can see the view’s data from Microsoft Dynamics NAV.

If this code works, why not using it also with Dynamics 365 Business Central on-premise? A lot of partner has moved their solutions to extensions and for these on-premise integrations scenarios they have used LinkedObjects as in the past. But… what happens now?

To explain the scenario and the issue that lots of partners have found, I’ve done exactly this on my environment (Dynamics 365 Business Central on-premise version 15.x). I’ve created a view via T-SQL with the following script:

CREATE VIEW dbo.[Customer Payment View]
AS
SELECT C.No_ as CustomerNo, C.Name, max(CL.[Posting Date]) as MaxPaymentDate
FROM [Cronusit].dbo.[CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] C
left JOIN [Cronusit].dbo.[CRONUS Italia S_p_A_$Cust_ Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] CL
on C.No_ = CL.[Customer No_]
where CL.[Document Type] = 1
group BY C.No_, C.Name
GO

The view returns the following data:

Then I’ve created a new extension with Visual Studio Code and AL. In this extension I’ve defined a table object as following:

The table definition has LinkedObject = true (so it’s linked to a SQL object).

The extension defines also a page object for displaying the data:

When you deploy this extension on your Dynamics 365 Business Central on-premise instance and you try to access the Customer Payment View page, this is what happens:

Data are not displayed and the page is “blocked” on trying to fetch the data until a system error is thrown. And many partners are lost…

Why this problem?

Because LinkedObjects works like every objects inside an extension: when you declare the table object in your extension, it’s created by appending the extension’s id to the object name, and so your SQL view must be created accordingly.

In my extension’s app.json file, the id is as follows:

The SQL view should be created accordingly (so, with the extension’s id in the name). If I execute now the following SQL script:

CREATE VIEW [Customer Payment View$251ca5f1-14b0-4f53-ae71-9dbab145e5f9]
AS
SELECT C.No_ as CustomerNo, C.Name, max(CL.[Posting Date]) as MaxPaymentDate
FROM [Cronusit].dbo.[CRONUS Italia S_p_A_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] C
left JOIN [Cronusit].dbo.[CRONUS Italia S_p_A_$Cust_ Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] CL
on C.No_ = CL.[Customer No_]
where CL.[Document Type] = 1
group BY C.No_, C.Name
GO

this is the created SQL view:

What happens if you deploy the extension now? Magically, the data of the LinkedObject table are shown:

This is a noisy problem, probably something that should be changed or fixed because on lots of situations you have a view created by someone else and its name cannot be changed accordingly to your extension’s id (that normally is not known at the moment where someone creates the SQL view). Be careful on this…

I add to this post also a note: on many scenarios, instead of using LinkedObjects I think you should use ExternalSQL tables.

Compared to the LinkedObject property, ExternalSQL table type permits you to have a table connection that is controlled at runtime. This provides a more dynamic table relationship than creating table definitions from SQL Server objects using linked objects:

In the OnOpenPage trigger of the page, you can handle the connection to the external table as follows:

UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, DATABASENAME);

REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, DATABASENAME, CONNECTIONSTRING);

SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, DATABASENAME);

Much more flexible (and your table cannot have exactly the same field names as the linked table).