Here is a simple trick to get around one of the limitations of dropdown controls, the fact that they can’t display data from related tables.

In a dropdown control, only data in columns native to the table specified in the dropdown’s Items property can be displayed

However, a formula column can be created to hold the data you would typically access via a lookup column and formula columns behave as a native table column, so can be displayed in a dropdown control

When might this be useful?

The need to display the contents of another table in a dropdown control can arise in many-to-many table relationships such as the one below

I want to display a gallery of invoices from the Invoice table in a Canvas App. For each invoice, I want to allow the user to access a dropdown control to select from the ledger codes that have been previously used for the same vendor

Here is the Dataverse table structure. The Invoice table acts as an intersect table between the Vendor and Ledger tables. A vendor can issue many invoices but each invoice can only have a single vendor. Each invoice has to have a single ‘Ledger Code allocated prior to payment

dataverse many to many table relationship

Formula Columns to the Rescue!

I want to filter the Invoice table by vendor and display the unique related ledger codes in a dropdown control

With a dropdown control that has its Items property set to the Invoice table, I can’t display the ‘Ledger Code’ from the Ledger table by using the Ledger lookup column, but I can implement a work-around with a formula column

Create a Formula column in the Invoice table called ‘Ledger_Code’ with the following formula

Ledger.'Ledger Code'

Now, whenever a new entry is added to the Invoice table, the formula column is automatically calculated and populated with the ‘Ledger code’ from the Ledger table

To create a dropdown within the gallery containing all the possible ledger codes for the vendor, set the items property of the dropdown to

Distinct(Filter(Invoices,Vendor.’Vendor Name’=ThisItem.Vendor.’Vendor Name’))

And set the Value property of the dropdown control to the formula column


Now the ledger codes related to the vendor are displayed in the dropdown against each invoice in the gallery

The formula column will update automatically if the lookup to the Ledger table is changed so it always remains valid

There is some slight data duplication as the content of the formula column is in addition to the lookup column, but that is a very small price to pay to allow the content of a different table to be displayed in a dropdown control with so little effort

What’s really interesting is the dot notation in formula columns can be used to navigate multiple tables. This means that formula columns can be used to display not only columns in the parent table, but if required, the grandparent table or even great-grandparent tables too. See my recent post on Navigating Dataverse Tables for more details on dot notation navigation


Formula columns can be used to store lookup data from a parent table directly with a child table

Unlike lookup columns, formula columns can be used as the value property of a dropdown control and their content displayed on screen

Formula columns created in this way will update automatically if the lookup data is changed

Not only can parent table data be stored, but also data from the grandparent or even the great-grandparent tables

Leave a Comment

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

Scroll to Top