Virtual table

1500+ connectors to other systems (SAP, Salesforce, Amazon, SharePoint...) make Dataverse a vital data hub. With virtual tables and Resco mobile apps, all the data is available on your device, even offline.

What are virtual tables

Virtual tables (also known as virtual entities) enable the integration of data residing in external systems by seamlessly representing that data as tables in Microsoft Dataverse, without replication of data and often without custom coding^{(Microsoft documentation)}.

Since release 16.0, virtual tables can also be used in Resco mobile apps, both online and offline. Since release 16.1, users of the mobile apps can create, update, and delete records for these tables. This function is available for tables with a ModifiedOn column (more precisely, any column containing the record's last modification date).

Configuration in Woodford

Virtual tables can be made available in the app projects using Woodford, just like standard tables.

1. Edit an app project in Woodford.
2. Select the virtual table from the list of entities in the Project menu.
3. Click Enable to add the table to your project.
4. Select the fields that you want to include. Don't forget to enable the "modifiedon" column.
5. Save all changes.

Synchronization strategy

Most tables use "versionnumber" for incremental synchronization. Unfortunately, virtual tables don't have this column. As a fallback, we can use the "modifiedon" column instead. This is the timestamp of the last modification of the record. It must be properly configured in Woodford.

If the table has a column that behaves as "modifiedon", we support create, update, and delete operations on this table, as well as incremental sync. The standard conflict resolution behavior applies.

Without this column, the only supported synchronization strategy is full sync. The table is read-only in the app.

Dynamic filtering of records based on per-user identifiers

Virtual tables in F&O don't have a direct relationship to systemuser in Dataverse, preventing the use of eq-userid filters. Using constants is not possible, as it would require a separate sync filter (and a separate Woodford project) for each user.

Since release 19.0, a fetch macro @@{{currentuser.columnname}}@@ is available to build fetch conditions based on the values of fields on the systemuser record of the current user. This feature is only available for the Dynamics/Dataverse backend.

This can be useful in specific scenarios, such as building sync filters to return only records related to the current user on external (virtual) tables where there is no owner lookup column (e.g., integrations with Business Central, F&O, or SAP). In such cases, admins can define a custom externaluserid column on systemuser table, prefill the column with the user identifier from an external system, and then use it in the sync filter like this:

<fetch>
  <entity name="account">
    <filter>
      <condition attribute="extid"
                 operator="eq"
                 value="@@{currentuser.externaluserid}@@" />
    </filter>
  </entity>
</fetch>

Performance

The performance of virtual tables can be somewhat lower than standard tables. Dataverse acts only as the middleman, involving additional communication with the external data source.

Moreover, synchronization can be slower. More precisely, the upload of virtual tables requires extra requests when checking for possible conflicts.

Was this information helpful? How can we improve?