By Steve Drake, Director, Technology at TrueNorth IT

Quite often we need to surface data from another system within Microsoft Dynamics. Sometimes the need is simply to display static read-only data, but we may also on occasion require:

  • Users to be able to interact with this data
  • Lists of data items that can be filtered
  • External data that is aware of and can interact with the form within which it is displayed
  • Filtering of external data using security roles of the viewing user.

Common solutions

We often see this type of problem solved by embedding a canvas app into a Dynamics form, as shown below:

lob-1b

Or we may add an IFRAME to a form:

lob-2b

Canvas lends itself well to the display of rich data, such as the images shown in the first example above. Once a developer has used a canvas app, they may be tempted to use this approach for every external data requirement. But what if we really just want data, such as a list of services at a location, that is read from an external web service? Is a canvas app the best approach to displaying data of this form?

The issue with these solutions

IFRAMES can be disregarded for any non-trivial scenario due to the challenges they pose when it comes to authentication.

Canvas apps have different problems. To the user they appear well-integrated, but for the analyst they create a real headache as the data they present is completely cut off from the rest of Dynamics. It cannot be reported on, nor filtered using Dynamics security roles, nor re-used on a different form or entity without having to develop another Canvas app. How can external data be presented to a user in a way which integrates it better with the rest of the Dynamics ecosystem?

Using a virtual table

A virtual table is a way of presenting external data within Dynamics as if it were a normal Dynamics table.  The various components of Dynamics can interact with the data just like any other data table. Dynamics security roles can be used to control who can access the data, just like you can with any other table. You can even use a virtual table from within Power Platform, so it can be part of another business process.  Your users won’t even know that the data comes from an external line-of-business system.

Below you can see the virtual table in action. Can you even tell it’s not a Dynamics table? Well, even Dynamics doesn’t know it’s virtual!

lob-3b

TrueNorth have used this approach to read financial data from external databases and to read lists of hospitals from public websites.  We have even used virtual tables to create complex views over existing data verse tables. We will go into more details about some of these scenarios in future technical blog posts.

To find out more about our work, click here.

Get our latest articles in your inbox

Enjoyed this article? Sign up for our email newsletter and get real-world information on all things Microsoft, cloud and tech. Your information will be shared with MailChimp but no one else, and you can unsubscribe with one click at any time