Exercise - Build root FHIR resource tables using Marker portal

• 20 minutes

In the previous exercise, you applied the conventions to build a FHIR based relational data model in Dataverse and created a proposed entity relationship diagram for the root level table. In this exercise, you use the proposed design and build the Dataverse table that represents the ServiceRequest FHIR resource in the Power Apps maker portal.

First you set up a solution in your environment and include the relevant components from the Healthcare data mode for Dataverse and then you construct our new Service Request table. The new solution is used through the remainder of the exercises as you continue to build the Service Request tables.

These exercises don't result in a full representation of each Service Request 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 Dataverse instance

Proposed design

The previous exercise concluded with a proposed design for the root Dataverse table for Service Request. You take the proposed design and implement the table, columns, and relationships.

Entity relationship diagram

The following simplified entity relationship diagram represents the root Service Request table and includes references to existing tables via Lookup fields.

This diagram isn't inclusive of all related elements, but it displays the relationship between all tables so far and provides enough information to begin building in the maker portal.

Set up the working solution

Power Platform solutions provide a common workspace for building model driven apps in Dataverse. In the following section, you set up the solution that is used throughout the rest of the exercises.

Create the publisher

In this task, you create your own publisher, which helps you uniquely identify your application and new components that you create.

1. Sign in to the Power Apps maker portal.

2. Select the environment.

   

3. Select Solutions on the left navigation pane and then select + New solution.

   

4. Select New publisher and enter the publisher details as follows:

   • Display name - Service Request Publisher

   • Name - ServiceRequestPub

   • Description - Adding Service Request to the data model

   • Prefix - fhir

   

5. Select Save.

Create the solution

The solution provides you with a collection of the components that you use when creating your new model-driven application. The solution also provides a method for versioning and deployment between environments.

1. In the New solution panel, select the New solution and then enter details as follows:

   • Display name - Service Request

   • Name - ServiceRequest

   • Publisher - Service Request Publisher (ServiceRequestPub)

   • Version - 1.0.0.0

   

2. Select Create.

Add tables to the solution

In this task, you add tables to the solution that you can use and update as part of the application. These tables are referenced in the list of Lookup columns.

1. From the upper toolbar, select Add existing and then select Table.

   

2. In the Add existing tables panel, search for the schema name Contact and select the Contact table. This table represents the Patient FHIR resource. Select Next.

   

3. In the Selected tables panel, select Include all objects. This step selects Include table metadata automatically.

   

4. Select Add.

5. Repeat the previous steps to add the following tables using the schema name beginning with the msemr prefix:

   • CodeableConcept - msemr_codeableconcept
   • Encounter - msemr_encounter
   • Device - msemr_device
   • Group - msemr_group
   • Location - msemr_location

The solution now contains references that are needed for the new Service Request table.

Add extra components to the solution

The following items are required when creating the tables and setting up the maps for the ingestion APIs.

1. From the upper toolbar, select Add existing and then select More, then select Choice.

2. Search for Codeable Concept.

3. Select Codeable Concept Type from the list.

4. Select Add.

Create the ServiceRequest table

In this section, you create the new Service Request table and provide the column details. You create a Standard table, a Dataverse table where you can store data and add to the navigation in model-driven apps.

1. Sign in to the Power Apps maker portal.

2. Open the ServiceRequest solution.

3. In the left navigation pane, select Tables, select the New table dropdown, and then select Table.

4. 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
   Plural name	Service Requests
   Description	A record of a request for service such as diagnostic investigations, treatments, or operations to be performed.
   Schema name	fhir_servicerequest
   Type	Standard
   Record ownership	User or Team
   Appear in search results	checked

   

5. Select Save.

Create the table columns

Now that the base table details are created, begin adding the columns that represent the ServiceRequest FHIR resource elements. This section provides details on how to create columns for the primitive and complex types.

Column definitions

The following table collects the proposed fields required for the Service Request table, expanding on the details of the diagram at the beginning of the exercise.

Schema Name	Display Name	Data Type	Description
AzureFHIRID	Azure FHIR ID	String 64 characters	Attribute used to store the GUID of the object in Azure FHIR.
AzureFHIRLastUpdatedOn	Azure FHIR Last Updated On	Date and Time	Attribute used to store the date and time that the object was last synced with Azure FHIR.
AzureFHIRVersion	Azure FHIR Version	String 50 characters	Attribute used to store the version of the object in Azure FHIR.
status (required)	Status	Choice values: draft, active, on-hold, revoked, completed, entered-in-error, unknown	Status code for the Service Request
intent (required)	Intent	Choice values: proposal, plan, directive, order, original-order, reflex-order, filler-order, instance-order, option	Intent code for the Service Request
priority	Priority	Choice values: routine, urgent, asap, stat	Priority code for the Service Request
doNotPerform	Do Not Perform	Yes/No	True if service/procedure shouldn't be performed
authoredOn	Authored On	Date and Time	Date request signed
patientInstruction	Patient Instruction	String multi-line, 2,000 characters	Patient or consumer-oriented instructions
quantity[x]: Service amount. One of these
quantityQuantity	Quantity - expand into the following fields
quantityQuantityValue	Quantity Value	Decimal	Numerical value (with implicit precision)
quantityQuantityComparator	Quantity Comparator	Choice values: <, <=, >=, >	How the actual value compares to the given value
quantityQuantityUnit	Quantity Unit	String 50 characters	Unit representation
quantityQuantityUri	Quantity Uri	String 500 characters	System that defines coded unit form
quantityQuantityCode	Quantity Code	String 50 characters	Coded form of the unit
quantityRatio	Ratio - expand into the following fields
quantityRatioNumeratorValue	Quantity Ratio Numerator Value	Decimal	Numerical value (with implicit precision)
quantityRatioNumeratorComparator	Quantity Ratio Numerator Comparator	Choice values: <, <=, >=, >	How the actual value compares to the given value
quantityRatioNumeratorUnit	Quantity Ratio Numerator Unit	String 50 characters	Unit representation
quantityRatioNumeratorUri	Quantity Ratio Numerator Uri	String 500 characters	System that defines coded unit form
quantityRatioNumeratorCode	Quantity Ratio Numerator Code	String 50 characters	Coded form of the unit
quantityRatioDenominatorValue	Quantity Ratio Denominator Value	Decimal	Numerical value (with implicit precision)
quantityRatioDenominatorComparator	Quantity Ratio Denominator Comparator	Choice values: <, <=, >=, >	Comparator symbol
quantityRatioDenominatorUnit	Quantity Ratio Denominator Unit	String 50 characters	Unit representation
quantityRatioDenominatorUri	Quantity Ratio Denominator Uri	String 500 characters	System that defines coded unit form
quantityRatioDenominatorCode	Quantity Ratio Denominator Code	String 50 characters	Coded form of the unit
quantityRange	Range - expand into the following fields
quantityRangeHighValue	Quantity Range High Value	Decimal	Numerical value (with implicit precision)
quantityRangeHighUnit	Quantity Range High Unit	String 50 characters	Unit representation
quantityRangeHighUri	Quantity Range High Uri	String 500 characters	System that defines coded unit form
quantityRangeHighCode	Quantity Range High Code	String 50 characters	Coded form of the unit
quantityRangeLowValue	Quantity Range Low Value	Decimal	Numerical value (with implicit precision)
quantityRangeLowUnit	Quantity Range Low Unit	String 50 characters	Unit representation
quantityRangeLowUri	Quantity Range Low Uri	String 500 characters	System that defines coded unit form
quantityRangeLowCode	Quantity Range Low Code	String 50 characters	Coded form of the unit
asNeeded[x]: Preconditions for service. One of these
asNeededBoolean	As Needed Boolean	Yes/No
asNeededCodeableConcept	As Needed Codeable Concept	Lookup, single CodeableConcept	As needed SNOMED code
code	Code	Lookup, single CodeableConcept	What is being requested/ordered Procedure Codes (SNOMED CT)
encounter	Encounter	Lookup, single Encounter	Encounter in which the request was created
performerType	Performer Type	Lookup, single CodeableConcept	Performer role
subject	Subject	Lookup, multi-table Device, Group, Location, Patient	Individual or Entity the service is ordered for

This list is used in this section when creating the table.

1. Sign in to the Power Apps maker portal.

2. In the left navigation, select Solutions, and open the ServiceRequest solution.

3. In the left navigation, select Tables and then select the Service Request table from the list.

   

4. Once the table is saved, you're taken to the main details pane for the new Service Request table. Under the Schema group, select Columns.

   

5. In the columns view, select New Column.

   

6. In the new column pane, expand the Advanced options section. This field is an example of a standard Text column.

7. If your publisher prefix isn't set to msemr, be sure to update it before creating this column. You can revert the prefix change after creating this column.

8. Enter the following properties for the column:

   Property	Value
   Display name	Azure FHIR ID
   Description	Attribute used to store the GUID of the object in Azure FHIR.
   Data type	Single line of text
   Format	text
   Behavior	Simple
   Required	Optional
   Searchable	checked
   Schema name	msemr_AzureFHIRID
   Maximum character count	64

   

9. Select Save.

10. In the left navigation under the Service Request table, select Keys.

11. Select New Key. Enter Azure FHIR Key as the name and select Azure FHIR ID from the list of Columns.

    

12. Select Save.

13. Select Columns again from the left navigation pane and select New Column.

14. In the new column pane, expand the Advanced options section. This field is an example of a standard Date and Time field. Enter the following properties for the column:

    Property	Value
    Display name	Azure FHIR Last Updated On
    Description	Attribute used to store the date and time that the object was last synced with Azure FHIR.
    Data type	Date and Time
    Format	Date and Time
    Behavior	Simple
    Required	Optional
    Searchable	checked
    Schema name	fhir_AzureFHIRLastUpdatedOn
    Time zone adjustment	User Local

15. Select Save.

16. In the columns view, select New Column.

17. In the new column pane, expand the Advanced options section. This field is an example of a standard Choice field.

18. Next to the Sync this choice with field, select New choice.

    

19. You can select an existing global choice that includes the values required for this column. For example, Value Quality Comparator is an existing global choice that can be selected for the comparator columns. If no choice is available, you can create a new one from this dialog.

20. In the New choice panel, you add new Choice options for each available Status value defined in the FHIR specification. Provide the following values for the new global Choice.

    Property	Value
    Display name	Service Request Status
    Choice Label	Draft
    Choice Label	Active
    Choice Label	On hold
    Choice Label	Revoked
    Choice Label	Completed
    Choice Label	Entered in error
    Choice Label	Unknown

21. Select Save.

    Note

    These status value Labels are reformatted to a more user-friendly display and the value is dependent on the seed value specified in your Publisher. You might change the Labels later with little impact, but the numeric Values may have an impact on code or business rules. Mapping the FHIR string values to the Choice values will be covered in a later exercise.

22. Enter the following properties for the column.

    Property	Value
    Display name	Status
    Description	Status code for the Service Request
    Data type	Choice
    Sync with global choice?	Yes
    Sync this choice with	Service Request Status
    Default choice	Draft
    Behavior	Simple
    Required	Business Required
    Searchable	Checked
    Schema name	fhir_Status

    

23. Select Save.

24. In the columns view, select New Column.

25. In the new column pane, expand the Advanced options section. This field is an example of a standard Multiple lines of text field. Enter the following properties for the column:

    Property	Value
    Display name	Patient Instruction
    Description	Patient or consumer-oriented instructions
    Data type	Multiple lines of text
    Format	Text
    Behavior	Simple
    Required	Optional
    Searchable	Checked
    Schema name	fhir_PatientInstruction

    

26. Select Save.

27. In the columns view, select New Column.

28. In the new column pane, expand the Advanced options section. This field is an example of a standard Yes/No field.

    Property	Value
    Display name	Do Not Perform
    Description	Patient or consumer-oriented instructions
    Data type	Yes/No
    Choices	Yes/No default values
    Default choice	No
    Behavior	Simple
    Required	Optional
    Searchable	Checked
    Schema name	fhir_DoNotPerform

    

29. Select Save.

30. In the columns view, select New Column.

31. In the new column pane, expand the Advanced options section. This field is an example of a standard Numeric Decimal field.

    Property	Value
    Display name	Quantity Value
    Description	Numerical value (with implicit precision)
    Data type	Decimal
    Minimum value	-100,000,000,000
    Maximum value	100,000,000,000
    Decimal places	2
    Behavior	Simple
    Required	Optional
    Searchable	Checked
    Schema name	fhir_quantityQuantityValue

    

32. Select Save.

33. Repeat these steps in the Create the table columns task for the remainder of the primitive and complex types.

Create the Lookup column relationships

The design includes fields on the Service Request table that relate to existing Healthcare data model for Dataverse tables. These fields are implemented as Lookups, or relationship types of Many-to-one.

1. In the main navigation, select Relationships under the Service Request table.

2. In the relationships view, select New Relationship, Many-to-one.

3. In the new Many-to-one relationship pane, from the selector under Related (One), enter Encounter.

   Note

   You may see two entries for Encounter, one being a Standard table and the other a Virtual table. Verify that you are selecting the Standard table by ensuring that the Lookup column name reads msemr_encounter.

4. Expand the General and Advanced options sections.

5. Enter in the following properties for the new Lookup field.

   Property	Value
   Lookup column display name	Encounter
   Lookup column name	fhir_msemr_encounter
   Lookup column requirement	Optional
   Searchable	checked
   Relationship name	fhir_servicerequest_encounter_encounter
   Lookup column description	Encounter in which the request was created
   Related table display settings
   Display option	Use plural name
   Display area	Details
   Display order	10000
   Type of behavior	Referential
   Delete	Remove link

   

6. Select Done.

7. Repeat the steps in the Create the Lookup column relationships section for the remainder of the Lookup, single types: asNeededCodeableConcept, code, performerType.

8. Navigate to the main overview of the Service Request Solution.

   

   Note

   The multi-table Lookup for subject will be covered in a later exercise.

Next

The Service Request table should now include the core fields. In the next exercise, you'll extend the design to include expansion tables that represent the child array elements found in the FHIR specification.

Links

Create tables in Dataverse