Practice Lab 1.6 – Relationships

Scenario

You are a Power Platform functional consultant and have been assigned to the Fabrikam project for the next stage of the project.

In this practice lab, you will be modifying the properties of existing relationships, creating new relationships, and creating rollup columns.

You will then export your customizations from the solution and import them into another environment.

Exercise 1 – Change relationship behaviors

In this exercise, you will be modifying the cascade relationships for the project table. The Region to Project relationship will be changed to referential, restricted and the Project to Milestone relationship will be changed to custom.

Task 1.1 – Region-Project relationship

Navigate to the Power Apps Maker portal https://make.powerapps.com
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Project table.
Under Schema, select Relationships.
Scroll down and select the Region relationship.
In the Many-to-one pane that appears on the right-hand side of the screen, expand Advanced options.
Change the Delete drop-down to Restrict.
Select Done.

Task 1.2 – Project-Milestone relationship

In the Project table Relationships, select Project from the table. Note that this relationship uses Project as the Display name and contoso_project_milestone as the Name.
In the One-to-many pane that appears on the right-hand side of the screen, expand Advanced options.
Change the Type of behavior drop-down to Custom.
Change the Delete drop-down to Restrict.
Change the Assign drop-down to Cascade Active.
Change the Share drop-down to Cascade User-Owned.
Change the Unshare drop-down to Cascade User-Owned.
Select Done.

Exercise 2 – Add new relationship

In this exercise, you will create a new relationship between the Resource and Project tables to create a lookup for the Lead Resource on the Project.

Task 2.1 – Create new Project Lead relationship

Navigate to the Power Apps Maker portal https://make.powerapps.com
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Project table.
Under Schema, select Relationships.
Select + New relationship and choose Many-to-one.
In the Many-to-one pane that appears on the right-hand side of the screen, select Resource in the Related (One) drop-down.
Enter Project Lead for Lookup column display name.
Enter projectleadresourceid for Lookup column name.
Expand General.
Enter resource_project_projectlead for Relationship name.
Expand Advanced options.
Change the Delete drop-down to Restrict.
Select Done.
In the Objects pane on the left, select Columns for the Project table.
Select the Project Lead column.
In the Edit column pane that appears on the right-hand side of the screen, expand Advanced options and scroll down.
Check the Enable auditing box.
Select Save.

Exercise 3 – Hierarchical relationship

In this exercise, you will create a new hierarchical relationship on the region table.

Task 3.1 – Create new hierarchical relationship

Navigate to the Power Apps Maker portal https://make.powerapps.com.
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Region table.
Select + New, and then select Relationship and then select + One-to-many.
In the One-to-many pane that appears on the right-hand side of the screen, select Region in the Related (Many) drop-down.
Enter Parent Region for Lookup column display name.
Enter parentregionid for Lookup column name.
Check the Hierarchical box.
Expand General.
Enter parentregion_region for Relationship name.
Expand Advanced options.
Change the Delete drop-down to Restrict.
Select Done.

Exercise 4 – Project Outcome relationships

In this exercise, you will create two many-to-one relationships for the Project Outcome table to the Project and Outcome tables. These relationships will be used to replace the many-to-many relationship between the Project and Outcome tables.

Task 4.1 – Create new relationship to Project

Navigate to the Power Apps Maker portal https://make.powerapps.com
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Project Outcome table.
Under Schema, select Relationships.
Select + New relationship and then select Many-to-one.
In the Many-to-one pane that appears on the right-hand side of the screen, select Project in the Related (One) drop-down.
Enter Project for Lookup column display name.
Enter projectid for Lookup column name.
Expand General.
Enter project_projectoutcome for Relationship name.
Expand Advanced options.
Change the Type of behavior drop-down to Parental.
Select Done.

Task 4.2 – Create new relationship to Outcome

Select + New relationship and then select Many-to-one.
In the Many-to-one pane that appears on the right-hand side of the screen, select Outcome in the Related (One) drop-down.
Enter Outcome for Lookup column display name.
Enter outcomeid for Lookup column name.
Expand General.
Enter outcome_projectoutcome for Relationship name.
Expand Advanced options.
Change the Type of behavior drop-down to Parental.
Select Done.

Note: An error will be shown saying that you cannot create another parental relation with the contoso_outcome table.
Change the Type of behavior drop-down to Referential.
Change the Delete drop-down to Restrict.
Select Done.

Task 4.3 – Set required and enable auditing on new lookup columns

In the Objects pane on the left, select Columns for the Project Outcome table.
Select the Project column.
In the Edit column pane that appears on the right-hand side of the screen, change the Required drop-down to Business required.
Expand Advanced options and scroll down.
Check the Enable auditing box.
Select Save.
Select the Outcome column.
In the Edit column pane that appears on the right-hand side of the screen, change the Required drop-down to Business required.
Expand Advanced options and scroll down.
Check the Enable auditing box.
Select Save.

Task 4.4 – Mappings

In the Fabrikam Environmental solution, select the Objects page and select All.
Select the ellipsis menu … and select Switch to classic.
In the legacy Solution Explorer, expand Entities.
Expand Project.
Select 1:N Relationships.
Select the relationship between Project and Project Outcome.
Select More Actions and select Edit.
Select Mappings.
Select New.
In the left-hand side, select contoso_projecttitle.
In the right-hand side, select contoso_title.
Select OK.
Select New.
In the left-hand side, select contoso_projectsummary.
In the right-hand side, select contoso_comments.
Select OK.
Select Save and Close. Select Publish All Customizations.
Close the Solutions Explorer tab.

Exercise 5 – Connections

In this exercise, you will enable connections for the resource and milestone tables.

Task 5.1 – Enable connections on Resource

Navigate to the Power Apps Maker portal https://make.powerapps.com
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Resource table.
Select Properties.
In the Edit table pane that appears on the right-hand side of the screen, expand Advanced options and scroll down.
Check the Can have connections box.
Select Save.

Task 5.2 – Enable connections on Milestone

Navigate to the Power Apps Maker portal https://make.powerapps.com
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Milestone table.
Select Properties.
In the Edit table pane that appears on the right-hand side of the screen, expand Advanced options and scroll down.
Check the Can have connections box.
Select Save.

Task 5.3 – Publish changes

In the solution, select the Overview page.
Select Publish all customizations.

Task 5.4 – Add connection roles

In the Fabrikam Environmental solution, select the Objects page and select All.
Select + New > More and then select Connection Role.
Enter Contributor for Name.
Select the Only these record types radio button.
Select Resource.
Select the Save icon.
Under Matching Connection Roles, select New.

Note: You may need to disable the pop-up blocker in your browser.
Enter Contributed to for Name.
Select the Only these record types radio button.
Select Milestone.
Select the Save and Close icon.
Select the Save and Close icon.
Select Done.
Select + New > More and then select Connection Role.
Enter Advisor for Name.
Select the Only these record types radio button.
Select Resource.
Select the Save icon.
Under Matching Connection Roles, select Add Existing.
Add Contributed to to the selected records by selecting the row using the Select button.
Select Add.
Select the Save and Close icon.
Select Done.

Exercise 6 – Calculated and rollup columns

In this exercise, you will use relationships to create calculated and rollup fields. The calculated column will add the email address for the approver to the outcome table. The rollup column will sum the funding values of projects.

Task 6.1 – Calculated column for a many-to-one relationship

Navigate to the Power Apps Maker portal https://make.powerapps.com
Make sure you are in the Dev One environment.
Select Solutions.
Open the Fabrikam Environmental solution.
In the Objects pane on the left, expand Tables.
Select the Outcome table.
Select + New > Column.
Enter Approver Email for Display name.
Enter Email address for approver for Description.
Select Single line of text in the Data type drop-down.
Select Text in the Format drop-down.
Change the Behavior drop-down to Calculated.
Select Save and edit.
When the new browser window opens, select + Add condition.
Select Approver for Field.
Select Contains data for Operator.
Select the green check mark.

NOTE: You may need to resize the window to see the check marks.
Select + Add action.
Enter the following formula:

contoso_approveruserid.internalemailaddress

NOTE: You can use intellisense to type and select the elements in the formula.
Select the blue check mark.
Select SAVE AND CLOSE.

Task 6.2 – Rollup column for the project one-to-many relationship

In the Objects pane on the left, expand Tables.
Select the Project table.
Select + New > Column.
Enter Total Project Funding for Display name.
Select Currency in the Data type drop-down.
Change the Behavior drop-down to Rollup.
Select Save and edit.
When the new browser window opens, select + Add related entity.
Select Project Funding (Project) for Related.
Select the blue check mark.
Select + Add condition.
Choose Status for Field.
Choose Equals for Operator.
Verify Active is selected for Value.
Select the green check mark.
Select + Add aggregation.
Select SUM for Aggregate Function.
Select (Project Funding) Funding Amount for Aggregate Related Entity Field.
Select the green check mark.
Select SAVE AND CLOSE.

Task 6.3 – Publish changes

In the solution, select the Overview page.
Select Publish all customizations.

Exercise 7 – Solutions

In this exercise, you will export the solution from the Development environment and import it into the Production environment.

Task 7.1 – Solution checker

Navigate to the Power Apps Maker portal https://make.powerapps.com.
Make sure you are in the Dev One environment.
Select Solutions.
Select the Fabrikam Environmental solution row but do not open it.
Select Solution checker > Run.
Wait a few minutes for the solution checker to complete.
Select Solution checker > View results.
You should see several warnings for the canvas app in the solution.
Select the back arrow to return to solutions.

Task 7.2 – Export managed solution

Select the Fabrikam Environmental solution row but do not open it.
Select Export solution.
Select Next.
The version number should have been incremented to 1.1.11.4.
Change the version number to 1.1.11.6
Select Managed for Export as.
Select Export.
The export will be prepared in the background. When the solution is ready, select the Download button.

Task 7.3 – Export unmanaged solution

Select the Fabrikam Environmental solution row but do not open it.
Select Export Solution.
Select Next.
The version number should have been incremented to 1.1.11.7.
Change the version number to 1.1.11.6
Select Unmanaged for Export as.
Select Export.
The export will be prepared in the background. When the solution is ready, select the Download button.

Task 7.4 – Import managed solution

Switch environments by using the Environment Selector in the upper right corner of the Maker portal.
Select the Production environment from the list.
Select Solutions.
Select Import solution.
Select Browse, change to the Downloads folder and select FabrikamEnvironmental_1_1_11_6_managed.zip and select Open.
Select Next.
Select Next.

Wait for connections to be created for the solution.
Both connections should be selected.
Select Next.
Select Import. The solution will import in the background.

Challenge (Optional)

Under Processes, examine the classic workflow, Project Funding Name, and create a new workflow to set the Title column on the Project Outcome table, when a new record is created or when a project or an outcome lookup column is changed.