Are you intrigued by the concept of virtual tables in Power Apps Dynamics 365 but still trying to grasp its essence? Fear not, as we embark on a journey to demystify this powerful feature and explore its functionalities.
Understanding Virtual Tables:
Let’s start with the basics. What exactly is a virtual table? In simple terms, a virtual table is a representation of data sourced from either SharePoint or SQL in the Microsoft Dataverse environment. However, unlike traditional database tables where data physically resides, virtual tables dynamically fetch data from the specified source during runtime.
Key Characteristics of Virtual Tables:
Dynamic Data Association:
Virtual tables dynamically fetch data from external sources such as SharePoint or SQL, ensuring real-time access to the latest information.
No Data Replication:
Unlike data flows where data is replicated to maintain synchronization between sources, virtual tables do not store data in the Dataverse environment. Instead, they maintain a dynamic connection to the external source.
CRUD Operations Support:
Despite not physically storing data, virtual tables support all CRUD (Create, Read, Update, Delete) operations. This means you can seamlessly interact with the data in the virtual table as if it were stored locally.
How Virtual Tables Work:
Let’s delve into a practical example to illustrate the workings of virtual tables. Imagine you need to fetch customer data stored in an SQL database and display it within your Power Apps Dynamics 365 environment. Here’s how you can achieve this:
- Establish Connection: Utilize existing connectors or connection references to establish a connection between your Power Apps environment and the SQL database.
- Define Virtual Table: Create a virtual table within the Dataverse environment, specifying the SQL database as the data source.
- Dynamic Data Retrieval: During runtime, the virtual table dynamically retrieves customer data from the SQL database, ensuring real-time access to the latest information.
- Seamless Interaction: Perform CRUD operations on the virtual table as needed, knowing that changes made will reflect back to the original data source.
You can also read more about the virtual tables from Microsoft learn here
There can be a possibility that the virtual tables are not working in your environment while working with the Power Platform. There can be many reasons but most of them can be one of the following reasons.
- Fields are not mapped properly.
- Virtual Table External is Mapped to the Wrong Place
- Service is Mapped to the Wrong Place
- Not Implemented Error
Fields are not mapped correctly:
This can be most possible error that you may face. The exact error can be Entity could not be retrieved from data source. Please try again or contact your system administrator.”
As you can see, data is not visible in the table here.
But it has many rows in the SharePoint list.
We can see here the field names are not mapped correctly.
We will update the external field name according to the source.
We have updated the field name.
We will do the same with the other fields as well.
Virtual Table External is Mapped to the Wrong Place
The issue can be that the virtual external table is mapped to the wrong place.
We can see that the External name and External collection names are showing incorrect here.
So, we will correct this as the following.
Service is Mapped to the Wrong Place
We need to check if the Data Source connection is correct or not. So, here we will create a new data source connection.
Data Source Not Implemented Correctly
There can be a possibility that the data source is not implemented correctly. We can check this in the entity information tab.
So, please select the correct data source.
Finally, after removing all the issues, we can see the data in the virtual table.
There can be more reasons that data is not displayed in the virtual table. It depends upon the type of data source as well. I am listing here some more possible issues.
Lack of Proper Permissions
Insufficient permissions or access rights to the external data source specified for the virtual table can result in authentication errors or data access restrictions.
Invalid Query Error
You can face this issue if you are connecting with some API or sending a query in your request. It’s possible that the oData source is not supporting $select and $filter in the query.
The error will look like this.
{“error”:{“code”:””,”message”:”The query specified in the URI is not valid. The property ‘Name’ cannot be used in the $select query option.”}}
The OData Service is Expecting a GUID
In this scenario, you may face one error that the ID field should be a GUID instead of the integer. Then you will need to change type from integer to GUID. The exact error you can see will be like this.
Cannot convert a value of type ‘Edm.Int32’ to the expected target type ‘Edm.Guid’.
Invalid Data Source Configuration:
Incorrect configuration of the data source for the virtual table can lead to connectivity issues and data retrieval failures.
Unsupported Data Source:
Attempting to use a data source that is not supported for virtual tables, or using an unsupported connector, can lead to compatibility issues and functionality limitations.
Data Source Connection Errors:
Network connectivity issues or server downtime in the external data source can prevent the virtual table from retrieving data, causing disruptions in functionality.
Data Source Schema Changes:
Modifications to the schema or structure of the external data source without updating the virtual table configuration can result in data mismatch or retrieval errors.
Data Source Authentication Changes:
Changes in authentication methods or credentials for the external data source may invalidate existing connections, requiring updates to the virtual table configuration.
Limitations in Power Platform Licensing:
Certain licensing restrictions may impact the use of virtual tables, such as limitations on the number of connections or data sources allowed in a specific subscription tier.
