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

[Drrickryp]

This is the 5^th in a series of blog posts regarding database design principles and PowerApps.  It presupposes that the prospective designer has reviewed the first 4 posts in the series and now has constructed the tables and defined the relationships between them.  All data points are reduced to their smallest logical component and each table has a single characteristic with fields that are either a number, currency, text, Boolean, hyperlink, or date/time and are not calculated from other fields.  Each cell of the table contains a single value or fact.  Each table has a primary key and there are no duplications of data between the rows. All tables are connected by Primary and Foreign keys. All Many-to-many relationships have been changed to two One-to-many relationships with a Junction table between them.

In 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 sample database that comes with MS Access.  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.  

 

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 

Coalesce(Parent.Default, First(Sort(Customers,ID,Descending)).ID+1)

The Coalesce function evaluates its arguments in order and returns the first value that isn't blank or an empty string.  For a form in New mode, Parent.Default will be blank while in Edit mode, Parent.Default will have a value. 

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)

After customizationBefore 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 TextInput box 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).