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
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
'Ledger_Code'
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
Summary
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