Building an app requires capabilities to perform relational modeling between entities that are being used in the app. In the context of virtual entities, there will be scenarios where virtual entities and native entities in Dataverse must work together to enable the desired user experience. In this article, we will see the concept of relational modeling that can be implemented using virtual entities for finance and operations.

Generating virtual entities

By default, virtual entities for finance and operations apps don’t exist in Dataverse. A user must query the catalog entity to view the entities that are available in the linked instance of finance and operations apps. From the catalog, the user can select one or more entities, and then request that Dataverse generate the virtual entities.

Entity fields

When a virtual entity is generated for a finance and operations entity, the system tries to create each field in the finance and operations entity in the corresponding virtual entity in Dataverse. In an ideal case, the total number of fields will be the same in both entities, unless there is a mismatch in supported data types between the finance and operations apps and Dataverse.

Key concept

In finance and operations entities can have one or more fields of various data types as the entity key. An entity key uniquely identifies a record in a finance and operations entity. Additionally, a record in an entity can be uniquely identified by a record ID primary key of the Int64 type.

In Dataverse, the primary key is always a globally unique identifier (GUID). The GUID-based primary key enables a record in an entity in Dataverse to be uniquely identified.

Relations

Finance and operations

Relations in finance and operations entities are modeled as one-to-many (1:n) or many-to-one (n:1) relations. These relations are modeled as relationships in the virtual entity in Dataverse. Note that many-to-many (n:n) relations aren’t supported in finance and operations.

For example, in finance and operations apps, if Entity A has a foreign key to Entity B, this relation will be modeled as an n:1 relationship in virtual entity Entity A in Dataverse. The schema name of this relationship in Dataverse uses the naming convention mserp_FK_<source entity name>_<relation name>. This naming convention has a maximum string length of 92 characters. Any relation where the schema name will produce a name that exceeds 92 characters won’t be generated in the virtual entity in Dataverse.

The external name of this relationship uses the naming convention FK_<relation name>. The external name is used to determine the relation in the finance and operations app when the query that is sent and built.

Dataverse

A relationship in the virtual entity in Dataverse will be generated only if the related entity in the relation already exists as a virtual entity in Dataverse. In the preceding example, if Entity B doesn’t exist as a virtual entity in Dataverse, the relation to Entity B won’t be created in Entity A when Entity A is generated as a virtual entity. This relation will be added to Entity A only when Entity B is generated as a virtual entity. Therefore, when a virtual entity is generated for the finance and operations app, validations are done to ensure that only relationships that can be complete and functional are generated in the virtual entity that is being generated.

In summary, a relationship to another finance and operations virtual entity might not exist in the virtual entity for either of the following reasons:

• The finance and operations entity that is participating in the relationship doesn’t exist as a virtual entity.
• The length of the name of the relationship exceeds 92 characters.

Virtual table–to–native table relationship

The GUID is the only information that is used to uniquely identify a record in a native Dataverse table (including in native entity–to–native entity relationships) or in a finance and operations virtual entity (including in virtual entity–to–virtual entity relationships). However, consider an example where you want to show finance and operations sales orders for Account A in Dataverse. The query that is sent to the finance and operations app for this relationship will have a WHERE clause on the GUID of the entity key of the native accounts entity in Dataverse, because the sales orders must be filtered for a specific account in Dataverse. However, because the finance and operations app doesn’t have any information about the GUID of the entity in Dataverse, the query won’t return any sales orders. The query will be successful only if the WHERE clause has conditions that are based on the fields that finance and operations understands.

Therefore, how can the GUID of the accounts entity in Dataverse be replaced with finance and operations fields in such a way that the query that is sent to the finance and operations app will return the correct list of sales orders?

To solve this issue and enable a rich set of scenarios that allows for virtual entity–to–native entity relationships, relationships can be added to this type of entity. The relation will appear as a relationship when the virtual entity is synced.

Solution

The relationship between the DemoSalesOrderEntity virtual entity and the Account native entity should be based on the Account Number and Company fields. By default, the native account entity in Dataverse does not have a company field. For this example, we will add a company lookup field named new_testcompany to the native Account entity.

Next, we add a new key named new_accountcompanyidx, which specifies that (accountnumber, new_testcompany) together represent a unique row in the account entity in Dataverse.

The following example shows sample X++ code. The names of the fields, index, and mapping information should match the names of the fields and indexes created in Dataverse. In this example, a relationship named “synthaccount” will be created between the virtual SalesorderHeader entity and the native account entity in Dataverse. The mapped fields make up the new_accountcompanyidx index. The display name for the relationship will be @SYS11307. Note the backslash at the start of the display name. This ensures that the label defines the relationship, so that it is appropriately translated.

The field mapping indicates which field on the virtual entity maps to the field on the native entity. In the field mapping, the key is the virtual entity field, and the value is the native entity field.

The next step is to define this relationship in X++. 

 [CDSVirtualEntitySyntheticRelationshipAttribute('synthaccount', 'account', 'msdyn_accountentitykey', 'synthaccount')]
    public static Map syntheticAccountRelationship()
    {
        Map fieldMapping = new Map(Types::String, Types::String);
        // Assumes the Dataverse account entity has a key on [msdyn_accountnumber, msdyn_companyid]
        // Also assumes that the Dataverse cdm_Company entity has a key on [msdyn_companycode]
        fieldMapping.insert(fieldStr(DemoSalesOrderEntity, CustAccount), 'accountnumber');
        fieldMapping.insert(fieldStr(DemoSalesOrderEntity, DataAreaId), 'msdyn_company');
        return fieldMapping;
    }

The next step is to generate or refresh the virtual entity to get the new relationship.

Note that relationships between a virtual entity and a native entity cannot be updated in Dataverse once it is created. The only way to make an update is to physically remove the relationship, refresh the entity, and then physically re-add the relationship in order to resolve the issue.

Generate virtual entity

Make it visible to yes.

Once the entity is visible then you can see a new relation is created in the Dataverse

Go back to the entity and you can see the data for the field synthAccount.

Now, you can use this lookup on PowerApps, Power Pages and PowerApps Portals.

If you like this article, feel free to share it with others who might find it helpful! If you have any questions, feel free to reach out to me.