Exercise - Add multi-table references using the SDK
In the previous exercises, you constructed Dataverse tables, columns, and relationships that represent a relational version of the ServiceRequest FHIR resource. You created most columns using standard data types and relationships as single Lookups in Dataverse. However, some ServiceRequest reference elements refer to more than one target FHIR resource. For example, the subject FHIR element is defined as:
"subject" : { Reference(Device|Group|Location|Patient) }, // R! Individual or Entity the service is ordered for
You can represent the Service Request subject column in Dataverse as a Lookup but you need to allow users to select either a Device, Group, Location, or Patient record.
Dataverse provides Multi-table lookups to support this scenario.
Multi-table lookup type columns allow a user to use a specific table that has multiple one-to-many (1:N) relationships to other tables in the environment. A single lookup type column can refer to multiple other tables.
In this exercise, you update the existing data model by adding multi-table lookups to the ServiceRequest table design. The Power Platform maker portal doesn't provide support for creating multi-table lookups, so you use SDK to create the extra columns.
The exercises in the module don't result in a full representation of each ServiceRequest data element, but covers the fundamental steps required to extend the Healthcare data model for Dataverse.
Prerequisites
You need to meet the following prerequisites:
Ensure Microsoft Cloud for Healthcare trial with the Data integration toolkit is installed.
Ensure you have access to the developer tools that allow you to call the REST API endpoints. For more information, see Microsoft Cloud for Healthcare training environment preparation.
Ensure the System Administrator rights are granted for the user on a Microsoft Dataverse instance.
Proposed design
In the previous exercises, you developed a proposed design for the root Dataverse table for the ServiceRequest and then updated it to include the expansion tables. Till now, you only required standard data types like text, datetime, and choice columns, and simple lookups to a single related table. Now, you add multi-table lookups.
Inline multi-table lookups
The ServiceRequest FHIR resource includes fields that can be represented as multi-table lookups. Following is the ServiceRequest FHIR resource specification:
"subject" : { Reference(Device|Group|Location|Patient) }, // R! Individual or Entity the service is ordered for
"requester" : { Reference(Device|(Organization|Patient|Practitioner|PractitionerRole|RelatedPerson) }, // Who/what is requesting service
You add the elements to the ServiceRequest table as new multi-table lookup columns.
For the subject field, you create a multi-table lookup that refers to the following Dataverse tables:
| FHIR resource | Table | Table schema name |
|---|---|---|
| Device | Device | msemr_device |
| Group | Group | msemr_group |
| Location | Location | msemr_location |
| Patient | Contact | contact |
For the requester field, you create a multi-table lookup that refers to the following Dataverse tables:
| FHIR resource | Table | Table schema name |
|---|---|---|
| Device | Device | msemr_device |
| Organization | Account | account |
| Patient | Contact | contact |
| Practitioner | Contact | contact |
| PractitionerRole | PractitionerRole | msemr_practitionerrole |
| RelatedPerson | RelatedPerson | msemr_relatedperson |
Shared Dataverse tables
The current data model includes another instance of multi-table lookups. A common element found in FHIR resources is the identifier:
"identifier" : [{ Identifier }], // Identifiers assigned to this order
Typically, you implement this type of element as an expansion table, that is, a child table relating back to the parent. Instead of creating multiple expansion tables that have the same schema, a single Identifier table is shared by each that require the Identifier details.
The link to the related tables is captured in the Parent Resource multi-table lookup. This allows for a single table to contain all the Identifier values and simplify the data model.
Because the Identifier table already exists, you update the existing Parent Resource column to include an extra reference to the Service Request table.
Entity relationship diagram
The updated entity relationship diagram represents the root ServiceRequest table, references to existing tables via Lookups, the expansion tables, and now the multi-table lookup columns as follows.
Create the multi-column lookups
In this section, you use the SDK tools to create the new Service Request multi-table lookups and update the existing Identifier Parent Resource lookup.
Refer to Multi-table lookups that provides the background on multi-table lookups and code examples on how to create and append lookups. You leverage the code samples for the Web API to create and update the multi-table lookups for Service Request and Identifier.
The APIs that you call:
- CreatePolymorphicLookupAttribute
- RelationshipDefinitions
Each API is accessible from your Dataverse instance and requires a JSON payload.
In the following examples, the API testing tool Insomnia is used to invoke the Web API calls. Note that the calls might take some time, so you should increase the global request timeout value for Insomnia in the Preferences section.
Create new Service Request columns
Open the Insomnia tool.
Create a new HTTP request and name it MultiColumnLookups.
Set the request Method to POST and the URL to {{url}}/api/data/v9.2/CreatePolymorphicLookupAttribute, replacing {{url}} with your Dataverse server URL.
Select the Auth tab and set the following properties:
Property Value Auth type OAuth 2.0. Enabled checked Grant Type Implicit Authorization Url https://login.microsoftonline.com/common/oauth2/authorize?resource={{url}} replacing {{url}} with your Dataverse server URL Client ID 51f81489-12ee-4a9e-aaae-a2591f45987d. This is a reserved client ID that allows you to sign in with your own credentials. This Client ID is a special ID provided by Microsoft that allows for the user authentication. For more information, see Use connection strings in XRM tooling. Select the Headers tab and set the following properties:
Property Value Content-Type application/json User-Agent insomnia/8.6.1 OData-Version 4.0 Accept application/json Select the Body tab and set the type to JSON.
Enter the following JSON as the body payload. This payload defines the subject multi-table Lookup column.
{ "OneToManyRelationships": [ { "SchemaName": "fhir_servicerequest_subject_device", "ReferencedEntity": "msemr_device", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_subject_group", "ReferencedEntity": "msemr_group", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_subject_location", "ReferencedEntity": "msemr_location", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_subject_contact", "ReferencedEntity": "contact", "ReferencingEntity": "fhir_servicerequest" } ], "Lookup": { "AttributeType": "Lookup", "AttributeTypeName": { "Value": "LookupType" }, "Description": { "@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Individual or Entity the service is ordered for", "LanguageCode": 1033 } ], "UserLocalizedLabel": { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Individual or Entity the service is ordered for", "LanguageCode": 1033 } }, "DisplayName": { "@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Subject", "LanguageCode": 1033 } ], "UserLocalizedLabel": { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Subject", "LanguageCode": 1033 } }, "SchemaName": "fhir_subject", "@odata.type": "Microsoft.Dynamics.CRM.ComplexLookupAttributeMetadata" } }Select Send on the request toolbar.
Verify the response. You receive a response JSON as follows:
{ "@odata.context": "{{url}}/api/data/v9.2/$metadata#Microsoft.Dynamics.CRM.CreatePolymorphicLookupAttributeResponse", "RelationshipIds": [ "25b3b272-4f63-ef11-bfe3-6045bda7dbc4", "2db3b272-4f63-ef11-bfe3-6045bda7dbc4", "34b3b272-4f63-ef11-bfe3-6045bda7dbc4", "3db3b272-4f63-ef11-bfe3-6045bda7dbc4" ], "AttributeId": "c06c7d9f-5f3a-4eb4-90a6-c852df2d6261" }Select the Body tab.
Update the body payload with the following JSON. This payload defines the requester multi-table Lookup column.
{ "OneToManyRelationships": [ { "SchemaName": "fhir_servicerequest_requester_device", "ReferencedEntity": "msemr_device", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_requester_account", "ReferencedEntity": "account", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_requester_contact", "ReferencedEntity": "contact", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_requester_practitionerrole", "ReferencedEntity": "msemr_practitionerrole", "ReferencingEntity": "fhir_servicerequest" }, { "SchemaName": "fhir_servicerequest_requester_relatedperson", "ReferencedEntity": "msemr_relatedperson", "ReferencingEntity": "fhir_servicerequest" } ], "Lookup": { "AttributeType": "Lookup", "AttributeTypeName": { "Value": "LookupType" }, "Description": { "@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Who/what is requesting service", "LanguageCode": 1033 } ], "UserLocalizedLabel": { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Who/what is requesting service", "LanguageCode": 1033 } }, "DisplayName": { "@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Requester", "LanguageCode": 1033 } ], "UserLocalizedLabel": { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Requester", "LanguageCode": 1033 } }, "SchemaName": "fhir_requester", "@odata.type": "Microsoft.Dynamics.CRM.ComplexLookupAttributeMetadata" } }Select Send on the request toolbar.
Verify the response. You should receive a response JSON as follows:
{ "@odata.context": "{{url}}/api/data/v9.2/$metadata#Microsoft.Dynamics.CRM.CreatePolymorphicLookupAttributeResponse", "RelationshipIds": [ "2318bce8-5063-ef11-bfe2-000d3a8ba5d8", "3318bce8-5063-ef11-bfe2-000d3a8ba5d8", "3d18bce8-5063-ef11-bfe2-000d3a8ba5d8", "4518bce8-5063-ef11-bfe2-000d3a8ba5d8", "4c18bce8-5063-ef11-bfe2-000d3a8ba5d8" ], "AttributeId": "9298ba9e-f94b-4c1e-82ec-75d47e7c3e18" }Validate the new columns and relationships in Dataverse.
Sign in to the Power Apps maker portal.
Open the ServiceRequest solution.
In the left navigation pane, select Tables, select the Service Request table.
Under Columns, verify two new columns.
Under relationships, verify the new relationships for each new column.
Update Identifier columns
You update the existing Identifier column to include an extra relationship to the Service Request table. This involves calling the RelationshipDefinitions API.
Open the Insomnia tool.
Open the HTTP request named it MultiColumnLookups.
Update the URL value to {{url}}/api/data/v9.2/ RelationshipDefinitions, replacing {{url}} with your Dataverse server URL.
Select the Body tab.
Update the body payload with the following JSON. This payload defines the existing Identifier table multi-table Lookup column named Parent Resource, adding a new relationship to Service Request.
{ "SchemaName": "msemr_identifier_parentresource_service_request", "@odata.type": "Microsoft.Dynamics.CRM.OneToManyRelationshipMetadata", "CascadeConfiguration": { "Assign": "NoCascade", "Delete": "RemoveLink", "Merge": "NoCascade", "Reparent": "NoCascade", "Share": "NoCascade", "Unshare": "NoCascade" }, "ReferencedEntity": "fhir_servicerequest", "ReferencingEntity": "msemr_identifier", "Lookup": { "AttributeType": "Lookup", "AttributeTypeName": { "Value": "LookupType" }, "Description": { "@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "The Parent Resource", "LanguageCode": 1033 } ], "UserLocalizedLabel": { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "The Parent Resource", "LanguageCode": 1033 } }, "DisplayName": { "@odata.type": "Microsoft.Dynamics.CRM.Label", "LocalizedLabels": [ { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Parent Resource", "LanguageCode": 1033 } ], "UserLocalizedLabel": { "@odata.type": "Microsoft.Dynamics.CRM.LocalizedLabel", "Label": "Parent Resource", "LanguageCode": 1033 } }, "SchemaName": "msemr_ParentResourceId", "@odata.type": "Microsoft.Dynamics.CRM.LookupAttributeMetadata" } }Select Send on the request toolbar.
Verify the new relationship is added.
Open the ServiceRequest solution.
In the left navigation pane, select Tables, select Add Existing Table.
Search for msemr_identifier and select Include all objects.
Select the Identifier table and select Relationships.
Verify that the new relationship is added:
All tables are now created with the relevant columns, relationships, and multi-table lookups.
Up Next
The root level Service Request table and its related expansion tables are created following the conventions used for the existing data model. Part of the conventions include adding fields to enable integration using the Dataverse Healthcare APIs. In the next exercise, you configure the relevant entity and attribute maps that allow the Dataverse Healthcare APIs to transform FHIR data into Dataverse records.