HOW TO: Virtual Entities in Dynamics 365

   Words by CRM Consultancy

   on 03/11/2021 10:36:00

Untitled design (36)

Copying Data is bad – it risks generating duplicate data and increases the level of technical debt in a solution.

But when we need data shared between different systems, how do we avoid it?

Traditional integration involves a one-way or bi-directional push of data between two systems databases; so each system can put that data to use. This is often unavoidable but if we can, avoiding this push of data can produce a better more sustainable solution.

In the Power Platform, this is where Virtual Entities and Virtual Connector Providers come in.

Virtual Entities are a key tool to read data from an external data source without having to import (potentially vast) amounts of values into Microsoft Dataverse.

This guide will show us how to setup a Virtual Entity and use external data in our Power Apps without a more costly and time-consuming integration. We will look at connecting to a SQL Server running in Azure, and will also cover a few of the pit-falls which may occur in this process.


The creation process for a virtual entity is slightly different than the one for setting up a physical entity:

1. You will need to have a solution created in a PowerApps environment in order to create a virtual table. Once you’re in this solution, you will need to switch to the Classic view, as the option for creating a virtual table is currently still only available in Classic view.

2. Once in Classic view, select New to create a new entity. This will open a new window which is show below where you can enter the details for your virtual entity. However, a crucial difference is that you need to check the box highlighted below.

image

3. The important fields which need to be filled are the external name and external collection name fields. These names will need to exactly match the name of the SQL table you are reading data from so that the data is called from the correct location.

4. A key point is also that you will need to create fields in this virtual entity which will be used for mapping to the columns of the SQL table. When these fields are created, you will also need to enter the external name of the column each field is looking up to This field is highlighted below. Once these fields have been created, you can select save and close to create your virtual entity.

image

5. Once the virtual entity has been created, you will need to add it to a model-driven app so you can see the visualisations of the data. You may get an error at this point which looks like the one presented below.

image

6. If you get this error, you can use the advanced find feature and select results to have the option of a log you can download appear. This log will be a txt file which helps you identify the cause of the error. Commonly this will be because the primary field, normally name by default, is not pointing to the correct column in the SQL server. This log is shown below.

image

7.The highlighted section of the log is one of the most important parts as it shows you what operation is being performed and the names of the columns the operation is trying to look at. This name needs to match exactly a unique identifier column, such as Asset Name.

IMPORTANT MISTAKES

There are a few mistakes which can be easy to make while setting up a virtual entity. They can be easy to miss but will cause a variety of errors if unresolved:

  • The first one of these is making sure that the external name of each field in your virtual name matches the name of the column in the SQL table. While the field can be named anything in Dataverse, the external name needs to match the name of the SQL column exactly, or the mapping will not work correctly. N.B External Name can only be set in classic view, fields created in the modern view will have blank external names

  • It’s crucial to make sure that this is specifically correct on the primary name field as this is the field which is used for the Search operation when pulling data from the SQL server.
  • The field types will normally work as fields except for a few notable exceptions. If a column is a decimal or a date in the SQL server it will need to be the matching data type in Dataverse for the field type.
  • Integer columns in SQL can be brought into either String or Whole Number fields in a Dataverse table.
  • Relationships can be established between a virtual entity and a standard Dataverse table, but they can only be a One-to-Many relationship (or a Many-to-One relationship going from a Dataverse table to a virtual entity). This is because of the read-only nature of a virtual entity which prevents a new editable field from being established in the virtual entity.
  • It’s important to note that while you can’t edit a virtual entity, you can still drill-down into them with views and forms created in the same way as they are in regular Dataverse tables.

BENEFITS

Once we have our External Data available in Power Apps – this enables a range of very useful functionality to how we can work with this data.

The most obvious is the ability to Search and Filter the Data from SQL in the common PowerApps interface, giving us a ‘insta-UI’ for whatever data we have ‘brought in’.

image

Using Power Apps to Filter Virtual Entity Records

The key element here is that we are working with the data directly in SQL but using the Power Apps front-end, so using the Filtering to effectively build our own SQL Queries without having forcing our Users to start developing SQL.



FURTHER READING

If you have any further questions for the CRMCS Team, please email us at contact@crmcs.co.uk and we will walk you through the process and answer any questions you may have.

Create or edit a virtual entity with data from an external source

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/create-edit-virtual-entities?view=op-9-1

Get started with virtual tables (entities)

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/virtual-entities/get-started-ve





Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

HOW TO: Integrate SQL Users into Dataverse Teams HOW TO: Prepare to go LIVE on Linked-In! Creating a Homepage Canvas App HOW TO: Virtual Entities in Dynamics 365 Release Wave 2 New Feature: The Columns Button HOW TO: Search a date field in Microsoft Dynamics The Relevance Search COMING SOON to Power Apps Portals HOW TO: Manage Your Dynamics 365 Database Size (Video Included) Dynamics 365 Marketing vs ClickDimensions It’s time to pause, reflect and acknowledge a new era of inclusivity and collaboration. Part 2 - How to get the most from a Technology Expert – Asset Management Hub Property & Asset Management Hub Part 1 – Balancing CRM and Asset Management Scopes - Asset Management Hub Creating a Multi-Lingual PowerApps Portal How to Set Up a Microsoft Teams Site Using DocDrive365 Microsoft Teams - Adding a Microsoft Teams URL to a Dynamics Appointment Dynamics 365 Marketing – Customer Voice Survey Not Appearing In Emails? Using SQL Management Studio to connect to the Dynamics DB Calling a Power Platform AI Builder Model via oData How to use DocDrive365 to integrate permissions between Business Units in Dynamics with Sites in SharePoint Getting started with the Power Platform AI Builder. Power Apps Portal Information Hub DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Part 5 - Power Apps Portals: How To Connect Azure B2C With Linked-In Part 4 – Power Apps Portals: Styling Azure B2C for Power Apps Portals
Contact Us

Want expert advice or a demo?

Get in touch now and see how we can help your business grow.

  • Name
  • Email Address
  • Phone Number
 
Close

Understanding Your Challenges

Our strong understanding of CRM and emerging technologies within the Microsoft environment means we deliver the right solutions for you.

Proven Real-World Solutions

As a leader in the field of Dynamics solutions, our pedigree developing and delivering real-world solutions is unsurpassed.

Long Term Support

We provide support beyond our design, implementation and 'go-live' delivery using Sprints and continual updates to our AppSource apps.

CRMCS | Design by Thinktank Marketing | Citrus-Lime Limited

To improve your experience today and in the future, this site uses cookies. Read our full Privacy Policy & Cookie information here I Understand