You will learn to understand and create a Virtual Table or Entity in Power Apps or Dynamics 365 using OData v4 Data Provider. Before we start, make sure to subscribe to CRM Crate so that you can stay up to date in the field of Power Platform.

In Power Apps, a virtual entity is a representation or abstraction of data that doesn’t physically exist within the Dataverse database. Instead, it is a dynamic table that retrieves and displays data from an external data source such as OData v4 in real-time. Virtual tables leverage external data sources through the OData v4 Data Provider, allowing users to interact with and manipulate data seamlessly within the Power Apps environment.

By creating virtual tables, users can integrate and work with data from external systems without the need to store it in the Dataverse. This provides a flexible and efficient way to access and utilize information from diverse sources directly within the Power Apps interface, enhancing the platform’s capabilities for building robust and interconnected applications.

What are the key advantages of using Virtual Tables?

Virtual tables in Power Apps offer several advantages, enhancing the flexibility and capabilities of the platform as given below.

Real-time Data Access

Virtual entities provide real-time access to data from external sources through the OData v4 Data Provider. This ensures that users can work with the latest information without the need for data synchronization.

Cost and Storage Efficiency

By eliminating the need to duplicate data in the CDS, virtual entities contribute to cost and storage efficiency. This is particularly beneficial when dealing with large datasets or when the data source is already well-maintained and doesn’t require additional storage.

No Data Duplication

Since virtual entities don’t store data within the Common Data Service (CDS), there’s no need for redundant storage. This helps in avoiding data duplication and ensures that users interact directly with the original data source.

Fluid Integration with External Systems

Virtual entities enable seamless integration with external systems, allowing users to work with data from diverse sources within the Power Apps environment. This is especially useful when building applications that need to interact with external databases, services, or APIs.

Dynamic Schema Structure

Virtual entities support a dynamic schema, meaning that the structure of the entity is determined at runtime based on the external data source’s schema. This adaptability facilitates working with varied data structures without the need for predefined schema changes.

Enhanced Application Performance

By fetching data on demand from external sources, virtual entities contribute to enhanced application performance. This is especially beneficial when dealing with large datasets, as only the necessary data is retrieved when needed.

What are the limitations of using Virtual Tables?

Keep in mind that Power Platform evolves, and there might be changes or improvements after this date. Below are some potential limitations of using Virtual Tables in Dataverse / Power Apps.

All virtual tables are read-only

Virtual tables are generally read-only. Users might not be able to perform direct write operations or modifications on data within virtual tables.

Entity conversion is not possible

There is no way for existing entities to be converted into virtual entities.

Limited Functionality

Certain features available for standard tables in Power Apps might not be fully supported for virtual entities. This includes functionalities such as cascading deletes and certain types of relationships.

No Offline Support

Virtual entities may not support offline data access. If users require offline capabilities, virtual entities might not be the suitable choice.

Performance Considerations

Depending on the complexity and size of the external data source, performance might be a concern. Retrieving and displaying large datasets may impact the responsiveness of the Power Apps interface.

Complexity in Data Types

Handling certain data types, especially complex or non-primitive types, may pose challenges in virtual entities.

Limited Filtering Capabilities

The filtering capabilities for virtual entities might be limited compared to standard tables. This can affect the ability to create complex queries.

Dependency on External System

Virtual entities are dependent on the availability and accessibility of the external system. If the external data source experiences downtime or undergoes changes, it can affect the functionality of virtual entities.

Metadata Changes

Changes to the metadata structure in the external system might require corresponding updates in the virtual entity configuration. This can lead to additional maintenance efforts.

Understanding OData v4 data provider

OData (Open Data Protocol) is a standardized protocol for building and consuming RESTful APIs. It is designed to facilitate the creation and consumption of interoperable and scalable web services. OData is based on standard HTTP protocols and uses a simple RESTful approach, making it easier for developers to build and consume APIs. OData v4 is the fourth version of the OData protocol. It introduces several improvements and enhancements over its predecessors.

Below are some key points you need to remember when working with OData v4 data provider with Dataverse.

1. In Microsoft Dataverse, it is mandatory for all tables to include an ID attribute, which serves as a unique identifier. This identifier, referred to as a unique GUID (Globally Unique Identifier), must have a value of type guid. It is important to note that only columns with the Edm.Guid data type can be mapped to external columns for the ID attribute.
2. You can’t map an Edm.Int32 data type to a Unique Identifier or Primary Key data type column in Dataverse.
3. When performing multiple retrieval queries, such as loading data into a grid, manage the size of the dataset obtained from the external data source by utilizing the select and filter query parameters.
4. OData tables containing nullable properties need to align with the corresponding columns in the virtual table. For instance, an OData table property marked as Nullable=False must have the associated Dataverse column configured with the Field Requirement attribute set to Business Required.
5. The OData v4 Data Provider cannot be employed to establish a connection with a different environment.

We will use the openly available OData Service (https://services.odata.org/V4/OData/OData.svc) as a data source for our virtual table. This service has multiple collections and we will be using the collection named “Products” for build our virtual table.

Creating a new Virtual Table in Power Apps

Creating a virtual table in Power Apps involves creating a virtual entity, which is a representation of data from an external data source within the Dataverse. Below are general steps to create a virtual table using the OData v4 Data Provider in Power Apps:

Step 1: Add a new Data Source.

1. In-order to create a connection between Dataverse & an external data source, we will setup a new OData v4 Data Provider connection.
2. Login into your Power Apps / Model-Driven App / Dynamics 365 and go to Advance Settings.
3. Go to Settings > Administration > Virtual Entity Data Sources.
4. On the actions toolbar, click on New.
5. Here select “OData v4 Data Provider” and proceed ahead.
6. Now create a record using the information as given below.
   Name: Name of your data source.
   URL: URL of your OData Service
   Timeout: Time in seconds after which Power Apps will timeout
   Pagination Mode: Use client-side if your OData service doesn’t support filtering parameters and if it does set it to server-side
   Return Inline Count: Set it to true if your service returns a number of records retrieved, otherwise, you can set it to false.

Step 2: Create a new Virtual Table in Dataverse

1. Login into Power Apps Maker (New or Legacy).
2. In solution explorer, create a new table. To do this, select Entities in the left navigation pane, and then select New.
3. On the General tab of the Entity Definition, select Virtual Entity, and then in the Data Source drop down list, select the data source that you want.
4. In the Entity Definition, fill in the mandatory columns listed below.

In our demonstration, we will be using the OData collection for “Products” from our OData service. As per the OData definition, we will use “Product” as External Name and “Products ” as External Collection Name.

Step 3: Configure attributes for Virtual Table

1. We will first need to setup the table’s primary field with the equivalent OData Service attribute. In our demo, we will utilize the OData service’s element named “Name ” as a primary field for our table. For achieving this, open the primary field in solution explorer and navigate to the property named “External Name”. Here you will have to define the exact value resembling with the OData Service attribute.
2. Further, we will setup the primary key for our virtual table. If you observe our OData service, you will get to know that the attribute “ID ” of type GUID and can be a perfect match for table’s primary key. For achieving this, open the primary key field in solution explorer and navigate to the property named “External Name”. Here you will have to define the exact value resembling with the OData Service attribute.
3. Additionally we will setup our last Dataverse attribute for OData service attribute named “Description“. For achieving this, create a new field in solution explorer and navigate to the property named “External Name”. Here you will have to define the exact value resembling with the OData Service attribute.

Step 4: Configure the view and query the virtual table data in Power App

After creating a new Virtual Table and defining its fields, now it is the time for defining the table view and querying the data in Power App. Below is the result of querying our newly created virtual table “Products” in Power Apps.

In conclusion, virtual tables in Power Apps present a valuable solution for integrating and interacting with data from external sources seamlessly within the Dataverse environment. These entities, driven by the OData v4 Data Provider, offer real-time access, eliminating the need for data duplication and enhancing cost efficiency. While providing a dynamic and centralized approach to data management, it is essential to consider certain limitations, such as read-only functionality and potential performance considerations. Nevertheless, the ability to create a cohesive user experience, leverage external data without duplication, and streamline application development makes virtual tables a powerful tool in extending the capabilities of Power Apps for building robust and interconnected applications.

• LinkedIn
• Facebook
• Telegram