cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
John01
Frequent Visitor

Filter Contacts Entity by 2 lookup fields to 2 other entities

Hello

 

I'm creating a Canvas app to filter the Contacts entity by 2 lookup columns/fields which lookup 2 other entities.

 

Contacts Entity [Fields: First name, Last Name, Branch (lookup to Branch Entity), Category (lookup to category entity), ...]

Branch Entity [Fields: Name, ID, ...]

Category Entity [Fields: Name, ID, ...]

 

The app will have 2 drop-downs, one for Branch & one for Category, and when selecting options from these 2 drop-downs it should bring back the records in the Contacts Entity that match those drop-down selections.

 

I'm using a Data Table instead of a Gallery to show the results (not sure that matters).

 

Problem is I can't figure out what the Items filter should say in order to make this work - have tried a few options but it keeps giving me errors.

 

Would appreciate some help getting the filter right.

7 REPLIES 7
v-jefferni
Community Support
Community Support

Hi @John01 ,

 

Would you like to filter a table by two LookUp columns?

 

If so, try to apply below formula in the Items of the Data Table:

Filter(
    Contacts,
    If(
        IsBlank(ComboBox1.Selected.Name),
        true,
        Branch.Name = ComboBox1.Selected.Name
    ) 
    && 
    If(
        IsBlank(ComboBox2.Selected.Name),
        true,
        Category.Name = ComboBox2.Selected.Name
    )
)

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi @v-jefferni 

 

thanks for the code - this has helped clear the errors I was getting - however, now I have a new issue regarding Delegation. It says the 'Filter' part of the formula might not work correctly on large Data Sets. And I'm not getting any data back in the results table, and I know there should be about 160 records in the results for the selections i made.

 

Do you know how I can fix this delegation issue?

 

[Maybe I need to select a Dynamics View to begin with, so that the drop-downs can do their work on a smaller set of data? Although not sure if this is possible or if this would even solve the delegation issue?? All assistance appreciated.]

 

Regards

John

Prakash4691
Post Prodigy
Post Prodigy

Hi John,

 

Try below formula,

 

if you want to display record based on any one dropdown selected value use or condition.

 

Filter(Contacts, 'lookupfield Branch' = Dropdown1.Selected || 'lookupfield Category' = Dropdown2.Selected)

 

if not then go with and condition.

 

Filter(Contacts, 'lookupfield Branch' = Dropdown1.Selected && 'lookupfield Category' = Dropdown2.Selected)

 

 

Regards,

Prakash

John01
Frequent Visitor

Hi, thanks for replies guys.

 

I have one additional requirement for my filter - i would like to combine the above two dropdowns together with an existing CDS/Dynamics View in my filter - can this be done like below - can't get it to work at the moment?

 

Filter(
    Contacts,
    If(
        IsBlank(ComboBox1.Selected.Name),
        true,
        Branch.Name = ComboBox1.Selected.Name
    ) 
    && 
    If(
        IsBlank(ComboBox2.Selected.Name),
        true,
        Category.Name = ComboBox2.Selected.Name
    )
    && 
    Contacts.ViewName
)

 

v-jefferni
Community Support
Community Support

Hi @John01 ,

 

So you have an entity view, could you please share a bit more about your scenario of that view? Would you like to use that view as data source?

 

If so, you could set below formula in Data Table's Items:

Filter(
    Filter(
           Contacts,
           'Contacts(Views)'.ViewName
    ),
    If(
        IsBlank(ComboBox1.Selected)||IsEmpty(ComboBox1.SelectedItems),
        true,
        Branch.Name = ComboBox1.Selected.Name
    ) 
    && 
    If(
        IsBlank(ComboBox2.Selected)||IsEmpty(ComboBox2.SelectedItems),
        true,
        Category.Name = ComboBox2.Selected.Name
    )
)

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

John01
Frequent Visitor

thanks @v-jefferni 

 

i now have another requirement. I have changed the combobox2 to a multi-select gallery/listbox, so I now need to update the filter to take this into account.

 

I was trying to add the selected items in the gallery to a Collection and then reference the Collection in the Filter, but i can't get it to work - to be honest, I'm not sure the Collection is setup correctly either.

 

Any ideas?

v-jefferni
Community Support
Community Support

Hi @John01 ,

 

Let's ignore the collection first, if you would like to filter in the list box which is allowing multiple selections say ListBox1, the formula would be:

Filter(
    Filter(
           Contacts,
           'Contacts(Views)'.ViewName
    ),
    If(
        IsBlank(ComboBox1.Selected)||IsEmpty(ComboBox1.SelectedItems),
        true,
        Branch.Name = ComboBox1.Selected.Name
    ) 
    && 
    If(
        IsBlank(ListBox1.Selected)||IsEmpty(ListBox1.SelectedItems),
        true,
        Category.Name in ListBox1.SelectedItems.Name     //replace "=" with "in" in this line
    )
)

 

Then consider of the collection you have created. If you created it in OnChange of the list box, then you need to set the list box Default to blank which means no default selections, otherwise when user do not change selections there would be issues. Assuming the collection created by below formula in OnChange of the ListBox:

ClearCollect(colCategory, ListBox1.SelectedItems)

 the whole formula would be:

Filter(
    Filter(
           Contacts,
           'Contacts(Views)'.ViewName
    ),
    If(
        IsBlank(ComboBox1.Selected)||IsEmpty(ComboBox1.SelectedItems),
        true,
        Branch.Name = ComboBox1.Selected.Name
    ) 
    && 
    If(
        IsEmpty(colCategory),
        true,
        Category.Name in colCategory.Name
    )
)

 

Please share a bit more about your collection if above don't work.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,618)