Filter a lookup column's choices in an edit form based on matching records from other SharePoint Lists

[RH10]

Hello everybody,

I have 3 SharePoint lists:

• List 1 - countries: the title column is renamed to 'Country'.
• List 2 - cities: the title column is renamed to 'City'. There is also a lookup column from List 1 called 'Country'. Naturally, each city is classified by the country it's located in, so there are multiple cities that have the same lookup column value.
• List 3 - a bunch of activity data with a lot of other columns. One of those is a lookup column 'Country' from List 1. Another one is a lookup column 'City' from List 2. This is to classify what activities are taking place in which city and country.

In Power Apps, I have an edit form that is writing data to List 3. I also have a combo box on the home screen of my app, populated from List 1, that allows someone to select a Country before proceeding. For the 'Country' data card in the edit form, I set the DisplayMode to View and the DefaultSelectedItems property to the combo box selection, so users won't touch that card when entering data.

However, I ran into a problem with the 'City' data card in the edit form. Right now, the Items property for the 'City' data card is:

Choices([@'Activities'].'City')

Unfortunately, this is giving me a list of all of the cities in List 2, which there are a lot of. I would like to filter this data card to only show cities that belong to the country selected from the combo box in the home screen (which is also the 'Country' value in the locked data card). I figure I have to use the Filter function to filter these choices based on the lookup columns from Lists 1 and 2, but I can't come up with a formula that will accomplish this.

Thank you in advance for your help!

[AllanDeCastro]

Hello,

To filter the choices in the "City" data card according to the country selected in the drop-down list, you need to use the Filter function with the LookUp function.

Can you try something like this:

Filter(
    [@'Cities'],  // Name of List 2
    Lookup(
        [@'Countries'],  // Name of List 1
        ID = Lookup([@'Activities'], ID = DataCardValue1.Selected.ID),  // DataCardValue1 represents the combo box
        'Country'  // Column in List 1 which holds the country value
    ) = 'Country'  // Column in List 2 which holds the country value
)

[RH10]

Hello Allan,

Thank you for the response!

Unfortunately I'm getting errors for the first and last equal signs.

For the first one "ID = LookUp([@Activities']", etc. it says "Incompatible types for comparison. These types can't be compared: Number, Record."

For the second one ") = 'Country')" it says "Incompatible types for comparison. These types can't be compared: Text, Record."

I tried fiddling with the code that you provided but still can't get it to work. Do you have any other suggestions?

[AllanDeCastro]

Hello,

I don't know exactly what is the data type/data model on your side, but you can normally manage this by ensuring to compare the appropriate field from your target source (leverage . to select the appropriate field):

Filter(
    [@'Cities'],  // Name of List 2 (Cities)
    Lookup(
        [@'Countries'],  // Name of List 1 (Countries)
        ID = Lookup([@'Activities'], ID = DataCardValue1.Selected.ID).ID,  // Lookup the ID in Activities list based on selected ID in DataCardValue1
        'Country'  // Column in List 1 which holds the country value
    ) = 'Country'.ID  // Column in List 2 which holds the country value
)

[RH10]

Hello again Allan,

Thank you for the fix, that cleared up the errors. However, now nothing shows up when I click on the combo box:

Do you have any other ideas?

[AllanDeCastro]

Can you check if you have at least one row from this formula (you can use CountRows for example) ? 
You can also check the field you're displaying within this comboBox.

[RH10]

I made a text label and wrapped the formula you provided within CountRows and I got 257. The total number of cities I have in List 2 is 283. So some of the filtering is working at least.

[AllanDeCastro]

Great 🙂 So ensure to select the correct field to be displayed within the combo box now.

[RH10]

Unfortunately, this is not working. For example, if I select Philippines from the combo box on the home screen, the combo box in the edit form should only give 4 options because I only have 4 cities in the city list that are located in the Philippines. So while the formula is doing some filtering, it doesn't seem to be filtering correctly/accurately. This is all very frustrating.

But thank you for continuing to help me, I really appreciate it.

[RH10 · Accepted solution]

Managed to figure it out thanks to Microsoft CoPilot (I was asking it for help with various prompts before but I finally managed to get a helpful response):

Set the Items property for the lookup column data card in the edit form to:

Filter(
    Choices([@'Activities'].City), // this is the original Items property
    Value in Filter(
        'Cities', // this is List 2
        'Country'.Value = ComboBox1.Selected.Title //Value of the country lookup column is equal to the value of the combobox.
    ).'City' // this is the lookup column from list 2
)