Exercise - Build Expansion tables using Power Apps Maker portal
In the previous exercise, you used the proposed design and built the Dataverse table that represents the ServiceRequest Fast Healthcare Interoperability Resources (FHIR) resource in the Power Apps maker portal. In this exercise, you build the expansion tables that represent the FHIR resource elements of type array.
You design the new tables following the guidelines set in the previous exercises and update the entity relationship diagram. Once complete, you create the new tables in the Power Platform maker portal and add them to the existing Service Request solution.
These exercises don't result in a full representation of each ServiceRequest data element, but it covers the fundamental steps required to extend the Healthcare data model for Dataverse.
Prerequisites
To complete the steps in this exercise, you need to meet the following prerequisites:
Ensure Microsoft Cloud for Healthcare trial with the Data integration toolkit is installed. For more information, see Microsoft Cloud for Healthcare training environment preparation.
Ensure System Administrator rights are granted for the user on a Microsoft Power Platform environment.
Proposed design
In a previous exercise, you developed a proposed design for the root Dataverse table for ServiceRequest. In this exercise, you expand the data model to include expansion tables that allow capture of child elements of the FHIR JSON resource.
Entity relationship diagram
The following simplified entity relationship diagram represents the root ServiceRequest table and includes references to existing tables via Lookup fields.
This diagram includes the first-level references for the ServiceRequest table. The diagram shows standard data types and reference types represented as Dataverse columns and Lookup fields. You focus on the FHIR JSON elements of type array and represent these child collections as related, or child, Dataverse tables.
Referring back to the ServiceRequest FHIR resource specification, you can identify JSON array element types by the cardinality of 0..*.
Based on the specification, the following elements can be represented as expansion tables. The new Dataverse tables allow capturing multiple values that represent the array of child elements defined in the FHIR specification.
| FHIR element | Schema Name | Display Name | Description |
|---|---|---|---|
| replaces | serviceRequestReplaces | Service Request Replaces | What request replaces |
| locationCode | serviceRequestLocationCode | Service Request LocationCode | Requested location, as a Codeable Concept reference |
| locationReference | serviceRequestLocationReference | Service Request Location Reference | Requested location, as Location reference |
| reasonCode | serviceRequestReasonCode | Service Request Reason Code | Explanation/Justification for procedure or service |
| category | serviceRequestCategory | Service Request Category | Classification of service |
Previous exercises provided conventions for creating Dataverse tables representing FHIR resources. Applying the conventions, you can provide an updated list of tables added to the entity data model diagram.
The root level FHIR resource tables follow the convention of tracking three metadata fields for every FHIR resource however child array elements don't include the same metadata. Instead, expansion tables are extra Dataverse tables with a link back to a single ServiceRequest record.
Therefore a common field for each is a Lookup of type ServiceRequest as a link to the parent record.
| Schema Name | Display Name | Data Type | Description |
|---|---|---|---|
| ServiceRequestId | Service Request | Lookup, single ServiceRequest | Parent Service Request. |
For the selected elements in the FHIR ServiceRequest resource, they're defined as either arrays of References or arrays of Codeable concepts. The conventions dictate that we handle CodeableConcept fields as a Lookup to the existing CodeableConcept table. This means streamlined expansion tables with a single extra custom Lookup.
The updated entity relationship diagram with expansion tables:
Create the ServiceRequest Expansion tables
In this section, you create the new Service Request expansion tables using the proposed design in the updated entity relationship diagram, and you create the extra relationships for each.
Sign in to the Power Apps maker portal.
Open the ServiceRequest solution.
In the left navigation pane, select Tables, select the New table dropdown, and then select Table.
In the new Table panel to the right, expand the Advanced options section. Provide the following details for the table in the fields provided:
Property Value Display name Service Request Replaces Plural name Service Request Replaces Description What request replaces Schema name msemr_serviceRequestReplaces Type Standard Record ownership User or Team Appear in search results checked Select Save.
Repeat these steps for the following tables.
Service Request Location Code table
Property Value Display name Service Request Location Code Plural name Service Request Location Code Description Requested location, as a Codeable Concept reference Schema name msemr_servicerequestlocationcode Type Standard Record ownership User or Team Appear in search results checked Service Request Location Reference table
Property Value Display name Service Request Location Reference Plural name Service Request Location References Description Requested location, as Location reference Schema name msemr_serviceRequestLocationReference Type Standard Record ownership User or Team Appear in search results checked Service Request Reason Code table
Property Value Display name Service Request Reason Code Plural name Service Request Reason Code Description Explanation/Justification for procedure or service Schema name msemr_servicerequestreasoncode Type Standard Record ownership User or Team Appear in search results checked Service Request Category table
Property Value Display name Service Request Category Plural name Service Request Categories Description A record of a request for service such as diagnostic investigations, treatments, or operations to be performed Schema name msemr_servicerequestcategory Type Standard Record ownership User or Team Appear in search results checked
In the next section, you'll add the Lookup columns to the new expansion tables.
Create the Lookup column relationships
The design includes fields on the Service Request expansion tables that relate to Service Request and existing Healthcare data model for Dataverse tables. These are implemented as Lookups, or relationship types of Many-to-one.
Open the ServiceRequest solution.
In the left navigation pane, select Tables, select the Service Request Replaces table.
In the main navigation, select Relationships under the Service Request Replaces table.
In the relationships view, select New Relationship, Many-to-one.
In the new Many-to-one relationship pane, from the selector under Related (One), enter Service Request.
Expand the General and Advanced options section and enter these values for the new Lookup field.
Property Value Lookup column display name Service Request Lookup column name msemr_servicerequestid Lookup column requirement Required Searchable checked Relationship name msemr_servicerequest_replaces_parent_servicerequest Lookup column description Parent service request Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Select Done.
In the relationships view, select New Relationship, Many-to-one.
In the new Many-to-one relationship pane, from the selector under Related (One), enter Service Request.
Expand the General and Advanced options sections.
Enter in the following properties for the new Lookup field.
Property Value Lookup column display name Replaces Lookup column name msemr_replacesid Lookup column requirement F Searchable checked Relationship name msemr_servicerequest_replaces_servicerequest Lookup column description What request replaces Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Repeat these steps for the following relationships on the Service Request Location Code table.
Property Value Related (One) Service Request Lookup column display name Service Request Lookup column name msemr_servicerequestid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_locationcode_parent_servicerequest Lookup column description Parent service request Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Property Value Related (One) Codeable Concept Lookup column display name Location Code Lookup column name msemr_locationcodeid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_locationcode_codeableconcept Lookup column description Requested location, as a Codeable Concept reference Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Repeat these steps for the following relationships on the Service Request Location Reference table.
Property Value Related (One) Service Request Lookup column display name Service Request Lookup column name msemr_servicerequestid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_locationref_parent_servicerequest Lookup column description Parent service request Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Property Value Related (One) Location (msemr_location) Lookup column display name Location Lookup column name msemr_locationid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_locationref_locatoion Lookup column description Requested location, as a Location reference Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Repeat these steps for the following relationships on the Service Request Reason Code table.
Property Value Related (One) Service Request Lookup column display name Service Request Lookup column name msemr_servicerequestid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_reasoncode_parent_servicerequest Lookup column description Parent service request Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Property Value Related (One) Codeable Concept Lookup column display name Reason Code Lookup column name msemr_reasoncodeid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_reasoncode_codeableconcept Lookup column description Explanation/Justification for procedure or service Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Repeat these steps for the following relationships on the Service Request Category table.
Property Value Related (One) Service Request Lookup column display name Service Request Lookup column name msemr_servicerequestid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_category_parent_servicerequest Lookup column description Parent service request Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Property Value Related (One) Codeable Concept Lookup column display name Category Lookup column name msemr_categoryid Lookup column requirement Business Required Searchable checked Relationship name msemr_servicerequest_category_codeableconcept Lookup column description Classification of service Related table display settings Display option Use plural name Display area Details Display order 10000 Type of behavior Referential Delete Remove link Navigate to the main overview area of the Service Request solution. In the main menu, select Publish all customizations.
Update Codeable Concept Type global Choice list
The new table Lookup columns reference the shared Codeable Concept table. You need to add a new item to the Codeable Concept Type list of Choices.
In the left navigation, select Objects > Choices.
Select Codeable Concept Type from the list to edit.
Scroll to the bottom of the list and select New choice.
Enter Service Request Category Codes.
Select Save.
Navigate to the main overview under the Service Request solution and in the main menu select Publish all customizations.
Up Next
The expansion tables for Service Request should now be part of the Service Request Solution and all relate to the Parent Service Request table. In the next exercise, you'll update the Service Request table to include multi-column lookup fields.