cancel
Showing results for 
Search instead for 
Did you mean: 

Relational Database Design fundamentals: Implementing a One to Many relationship in PowerApps

 

customersorders.PNGIn this post,  I will demonstrate how to create an app consisting of two tables with a one to many relationship.  I created two tables from data in the Northwind Traders database.  The two tables were exported to Excel and were then used as datasources for a new PowerApp. I started at  web.powerapps.com and selected the third option, Create a Powerapp with data.  I selected the table from the Many (Child) side of the relationship, Orders and allowed PowerApps to create the three-screen app.  Before doing anything else, I went to the View option and selected Datasources. I then imported the table that is the One (Parent) side of the relationship, Customers.   Each order can only have one customer, but each customer can have many orders. The final app will have 5 screens, a dashboard, two galleries (Customers and Orders) and two Edit forms for each of the galleries.  customergallery.PNGDashboard.PNGCustomersandOrdrers dropdown.PNG

customerOrders after customiztion.PNG CustomerDetails.PNG

 

 

 

 

 

 

 

The final result is shown above. The view screen can be ignored.

After creating the app from the Orders list, there are initially three screens.  The important ones are the Gallery screen and the Edit screen.  When working with One to many relationships in Powerapps, the Lookup()  function is used extensively to look up values from the One side of the relationship and display them in place of the foreign key, in this case, CustomerID.  The dropdown control is also used extensively to select a records from the One side of the relationship.  The value from the Dropdown is used to filter gallery and datatable controls. In the figure, the Dropdown’s Items property is Customers and the gallery is filtered by

 Sort(Filter(Orders, CustomerID = Dropdown1.Selected.ID), OrderDate, Descending)

Note that the ID is selected from the dropdown even though the display in the dropdown is the Customer Name.  A dropdown control contains a single record from the table in its Items property.  However, all  the fields from that record can be referenced by other controls. In this case, we are referencing the ID field from the Customers table.

The set up in the figure is a common way to show a One to many relationship in PowerApps.

The next step is to set up the Edit screens for the two tables.  The Edit screen for the Customers side is straight-forward. Just selecting the Customers table in the datasource box will populate the form. There are no modifications necessary except for adjusting the width of the fields. The ID field should be hidden or disabled to prevent users from modifying it. Sql and SharePoint provide an ID automatically but if the underlying data source does not provide an ID, then one should be created as follows

First(Sort(Customers,ID,Descending)).ID+1

This formula prevents errors caused by delegation limits if the number of items exceeds 2000. The formula in the ID TextInput control in the card is usually Parent.Default but should be replaced with 

If(!IsBlank(Parent.Default), Parent.Default, First(Sort(Customers,ID,Descending)).ID+1)

The Orders form has to be customized to prevent the user from creating an order without a customer This screen is opened by Navigating from either the “+” icon and the NewForm(EditOrderForm) function used to set the Mode to New.

 

NewForm(EditOrderForm);Navigate(EditOrderScreen, None)

 or from the “>” icon inside the Gallery in which case the EditForm(EditOrderForm) function is used to set the form’s mode to Edit.

 

EditForm(EditOrderForm);Navigate(EditOrderScreen, None)

 

 

customerOrders after customiztion.PNGAfter customizationcustomerOrders before customiztion.PNGBefore customization 

 

 

 

 

 

 

 

 

  • Orders Edit Screen
    • This figure shows the uncustomized Edit screen and the customized screen following these changes
    • The datasource has been selected and the fields have been chosen.
    • The Items property has been set to OrdersGallery.Selected
    • The CustomerID, Order date and ID cards have been unlocked.
    • DateOrdered card
    • The MinuteValue, HourValue and Separator in the Order date card have been hidden.
    • CustomerID card
      • The DisplayMode of the Customer ID card is set to Disabled to prevent the user from changing it.
      • The Textbox inside of the CustomerID card is set to Dropdown1.Selected.CustName, referencing the dropdown control on the OrdersGallery screen.
      • The Update Property of the card has been changed to Dropdown1.Selected.ID.
      • ID card
        • The Text property of the TextInput control inside the ID card has been set to First(Sort(Orders, ID, Descending)).ID+1 to avoid problems with delegation should the number of orders exceed the 2000 item limit.  The Last() and Max() functions are not delegatable and will not work correctly if the number of items in the table exceeds 2000. 
        • The ID card is then hidden or disabled to prevent the user from changing it.

 

Because of the changes, the user will not be able to add an order unless a customer has been chosen first and a new customer will have to be added before creating their orders.  This design maintains referential integrity between the tables.  To complete the app, a dashboard screen has been added so the user can choose to work with the Customer data (Adding or Editing Customers) or the Order data (Adding or Editing Orders). 

Meet Our Blog Authors
Labels