PowerApps 101: Implementing many-to-many relationships between tables

A relationship is the link between one set of data and another.  Let’s take for example a wholesaler that may have a list of Customers, a list of Products, and a list of Orders (one customer, many products). There will be two types of relationship between tables.Each customer can have one or more orders, but one order can only have one customer.  This is called a one-to-many relationship and it is normally implemented by simply having a CustomerId column in the Orders table.  For example in SharePoint this is done for you automatically when you create a lookup type column and you do not allow multiple values.  The SharePoint lookup column will store the ID of the customer, but will show you other properties of the customer by looking them up in the Customers table.

Each order can have one or more products, and one product can be included in more than one order.  This is called a many-to-many relationship, and this is normally implemented by creating a separate junction table (OrderLines).  This is just a normal table which contains a column for the OrderId and a column for the ProductId (as well as any other useful information such as order quantity).  You can also create a many-to-many relationship in SharePoint by allowing multiple values in a lookup type column.  What will happen behind the scenes is that SharePoint will create and manage a virtual junction table, but without exposing this to the user.

In PowerApps you can only connect to data sources that have a primary key, which is unique for all records of the table.  Usually you don’t need to think about the primary key since this will be created by your system, so for a SharePoint list the ID column is the primary key, and for a CDS entity it is the PrimaryId column.  If you create your junction table as a SharePoint list this will also have an ID column, so you will have no issues. However, if you create your junction table in Azure SQL Database for example, then you will need to define a primary key.  The good thing is that compound primary keys (i.e. primary keys defined over more than one column) also work in PowerApps, so you can define a primary key for the junction table with just the two id columns: OrderId and ProductId.

Let’s assume that you have created the following four tables:

Customers: containing a CustomerId column and other customer information

Products: containing a ProductId column and other product information

Orders: containing an OrderId column but also a CustomerId column as well as some information on the order in general (such as OrderDate)

OrderLines: containing an OrderId column, a ProductId column, as well as other order line information such as OrderQuantity

I will now outline how to obtain some common results.   Please leave a comment if you think other common results need to be added.

Total amount of MyProductId ordered by MyCustomerId for all orders

Sum(Filter(OrderLines, ProductId = MyProductId, OrderId in Filter(Orders, CustomerId=MyCustomerId).OrderId), OrderQuantity)

You can add filter conditions for dates etc. to the above expression for further refinement.

List of all customers that have ordered a specific product with an id of MyProductId

Distinct(Filter(Orders, OrderId in Distinct(Filter(OrderLines, ProductId = MyProductId), OrderId).Result), CustomerId)

The above expression will return a one-column table containing the customer ids.  If you want to add a column to show the customer names (say from the CustomerName column in Customers) you can wrap the AddColumns function around the above expression (let’s call it X) like this:

AddColumns( X, "CustomerName", LookUp (Customers, CustomerId = Result, CustomerName))

Multiple types data source

The great thing about PowerApps is that you can concurrently use data from more than one type of source.  For example this means that you can store issues as rows (records) in a SharePoint list and images related to specific issues in either a CDS entity or in an Azure SQL Database table.  So all that I have said above can apply to relationships between tables residing in different types of sources.

Delegation

If any of the expressions you use in obtaining the results I outline are not delegable for your data source, or your data source is not delegable, then you will be limited in receiving the result of querying the first 500 rows of that data source.

2 Replies to “PowerApps 101: Implementing many-to-many relationships between tables”

  1. Fantastic Article. In power apps, how do you setup a multi select field that allows you to relate multiple selections from your child table to the parent table?

    For example if you have a PEOPLE table and a COLORS table, and you want to relate each persons favorite colors. The app would have a list of PEOPLE on the main screen, when you select a PEOPLE record, you would go to the PEOPLE details where a multi select would have all available COLORS from the COLORS table. You could then select multiple colors that relate to this Person. I can’t get this to work. The box turns red and says “value required” and gives an error when trying to save the record.

    1. @Jared, you would need to structure your data as in the article above, so create a junction table (call it FAVOURITECOLORS for example), that would have a Person column and a Color column. Then each choice in the multiselect would correspond to a record in this junction table. You need use the Patch() function to create a record for every choice and a Remove() or RemoveIf() to remove the record when the choice is reversed. Please Skype me on baizini so that I can explain the mechanics in more detail.

Leave a Reply

Your email address will not be published. Required fields are marked *