Filter Power Apps drop downs by other fields

Today I tried to create a form in Power Apps where I needed to filter the options available in the drop down depending on an option selected in a different field.

The initial setup

I have the following plumbing already done.

  1. I created a datasource Contact List_1 which is a list of contacts in SharePoint
  2. I created a datasource Opportunities which uses SharePoint  list called Opportunities
  3. The both Opportunities and Contact List have a lookup column called Client
  4. The Contact list has a single line of text column, Fullname
  5. My Opportunity list has multi select lookup column, OpportunityContacts

I now want my Power App to only show the contacts relevant to the client selected.

Initially Power Apps would use Choices(Opportunities.OpportunityContacts) to get all my contacts in the Opportunities datasource and my contacts appear in my drop down. This is good but if you want to only show contacts related to a specific client then this isn’t useful. On purpose I called my Client column in my contacts list Company. Just so that it becomes easier to analyse the functions following in this post.

Power Apps filter function

To do the filtering we will need to use the Filter function.

So we would be looking at something like this:

Filter(Choices(Opportunities.OpportunityContacts),<Filter>)

So for each of my contacts I’m going to filter by looking in the Contact List 1 datasource if the Company name matches the client name in my opportunity.

A “Value in <List of contacts>” would be the rough construction that I’m going to use.

Filter(Choices(Opportunities.OpportunityContacts), Value in <List of contacts>)

To filter the list of contacts by the Company name I’m having to look at the data coming from the ‘Contact List 1’ datasource and filter by Company name and return the full name of the contact.

The following Filter function will do this: Filter(‘Contact List_1’, Company = DataCardValueClient.Text).FullName

Putting all the different elements together I’m looking at this:

Filter(Choices(Opportunities.OpportunityContacts),Value in Filter('Contact List_1', Company = DataCardValueClient.Text).FullName )

Filter drop down

The complete cascaded drop down

With the above filters in place I’m now getting a drop down that lists only my relevant contacts for a client.

Filtered drop down

16 thoughts on “Filter Power Apps drop downs by other fields

  1. commented on January 15, 2019 by Misty Miller

    I am getting an error “Cannot convert this text value to a record” on the “Value in Filter” in the following. My PlanName is a choice field in the LUPlanContacts list which I created as a Data Source. The “=” sign between Title and PlanNameDataCardValue is giveieng me an error of “Invalid argument type”. I have messed and messed with this and it doesn’t seem to make sense why mine is throwing an error:

    Filter(Choices(LUPlanContacts.PlanName),Value in Filter(‘Plan Directory’,Title=PlanNameDataCardValue.SelectedItems.Value).Authorized_x0020_Signer_x0028_s_)

    • I would need to try this out but the problem could be in : PlanNameDataCardValue.SelectedItems.Value

      you are comparing a title with multiple selected items. That doesn’t look right to me.

      • commented on January 17, 2019 by Misty Miller

        Pointing to a multiple selection field doesn’t make a difference in the error received.

      • HI Misty,

        Can you use both as single selection fields. So that the field you are filtering and the field that is filtered by are both single selection fields only.

  2. commented on January 17, 2019 by Misty Miller

    Thank you. I will try moving that field to a multi-choice, as well, to see if it works, but I am still unsure why the error in Value in Filter.

  3. commented on January 18, 2019 by Misty Miller

    No, they need to be multi-select fields

  4. commented on April 9, 2020 by Simet

    You absolute legend. Solved my filter lookup problem as we needed to limit a sites list by Region. Had a initial issue getting it to work until I realised this has to be a drop down and not a combo box.
    Thankyou again

  5. commented on June 19, 2020 by Grant Peterson

    So, I am having the same issue as above, “Can’t Convert this data type. Powerapps can’t convert this text to a Record”. Did anyone solve that issue? my columns are choice fields, set to not allow multiple selections.

  6. commented on June 19, 2020 by Grant Peterson

    Filter(Choices([@OutOfOfficeRequests].Building_Name), Value in Filter(TakeCoverZoneLeaders, ‘Geographic Region’.Value = DataCardValue13.Selected.Value).Building)

    Power Apps highlights Value and says it can’t convert this data type.

    • commented on June 19, 2020 by Pieter Veenstra

      Okay, your situation is slightly more complicated. What types of data is stored in the Geographic Region field and what is the type of the datacardvalue13?

      My guess is that those two Values don’t match up.

  7. commented on June 19, 2020 by Grant Peterson

    The error I get says it can’t convert this Text to a Record. All of the fields are choice fields in a sharepoint list, which contain text data, for example: Geographic Location – “US – Other”, Building: A, Building_Name : Choices include A, B, C, etc.DataCardValue 13 points back to the sharepoint list field for Geographic location.

    • commented on June 22, 2020 by Pieter Veenstra

      Hi Grant,

      I’ve had a detailed look and the problem is that the first Value is not a text it will be a record of Building Names

      Filter(Choices([@OutOfOfficeRequests].Building_Name), Value in Filter(TakeCoverZoneLeaders, ‘Geographic Region’.Value = DataCardValue13.Selected.Value).Building)

      to run the in operator you will need to have a text to check in records.

  8. commented on July 1, 2020 by Grant Peterson

    Ok, how would I go about doing that? I haven’t been able to find any work around a yet

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: