At some point in 2022, Microsoft added the Filtered Lookup to the new form designer. It’s simple, here’s how.
In this guide I am using the setup of Continent > Country > City
I want to be able to filter every major city in the world based on what Continent it’s on and what Country it’s in.
First off, I create three tables.
Continent, Country, and City.
(Name your primary columns people. Please!)
On the Countries table, I make a Lookup column to Continents.
Then the same process on the Cities table, create a Lookup to the Countries table.
So far, so good? Great!
Effectively you’re done at this point. You have created a hierarchy of tables now to link them all together on one form.
On the form you want to use this data, for me it’s a Holiday Tracking App, I go to my main form and create 3 new Lookups, one for each of the new tables we created (Continents, Countries, and Cities)
Still in the form designer click on Countries Lookup field and on the right in properties, scroll down to the Filtering Section and enable Filter by related rows.
The first drop down is showing all the lookups on that table, find the one you want to compare against, in this case I want to filter Countries by Continents, so I select the current tables relationship (Continent) and then I select the lookup I created on the Countries Table before. This is finding all the Relationships on the selected columns table. So, I want to choose the continent.
Rinse and repeat the same process for the City column to look up to Country
Save
Publish
And you’re done.
Considerations you can add.
- A Business rule that clears out child items when the parent is deleted, in the above instance, if the Country is removed then the city should go, Continent is removed then the Country and City should be removed.
- Same as above, but you can have a rule that Shows/Hides & Clears the children if the parent is not populated, even makes them read only until they contain data.
- On each of the parents Tables, you can have a subgrid showing all related records.
The country and city data I used is from datahub Countries Cities, Continents are from my brain.
I have included the full excel below so you can try it yourself – is it fully cleansed? No, does it 100% work? No, there’s about 3500 cities that just don’t import and I cba cleaning it.
