Answered

Filter on Sharepoint Lookup field

(0) Share

Report

Posted on by ruth

212

This seems so fundamental, but I cannot find the solution. I am dealing with multiple Sharepoint lists. I have a Vendor which is Categorized by a field called Category. The Category is a lookup value which is also a list in Sharepoint. The Vendor can be in multiple categories. I'm trying to filter my list of Vendors based on the selection of the Category list box. I thought it was as simple as Filter(Contacts,Category=ddlbCategory.Selected.Value)

Also, my Contacts list contains other fields which I'm not able to access to try to do a different filter. For example, it has a yes/no value for the field PrgPers. I want to filter where all PrgPers = 1 (Yes). The list has this column but it is not availble to filter on?

Categories:

Building Power Apps

All responses (17)

Answers (2)

Sort by

dinusc Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello ruth,
Your formula looks correct, with exception that you could be comparing an object with a value. Try:
Filter(Contacts,Category.Value=ddlbCategory.Selected.Value)
or
Filter(Contacts,Category=ddlbCategory.Selected)
Please note that the above formula should work if the SharePoint Category field does not allow multiple selections (so Category is a record and not a table). Let me know if this is not the case.
In regards to your second SharePoint list (Contacts), you sould be able to access the "Yes/No" values. Those could be true/false values.
ruth 212 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks. Unfortunately, my lookup does allow multiple values. Is that the problem? is multiple values not supported? Would you know of another way to filter if there are multiple values?

With regards to the other issue, the actual field is saying it's invalid. For example, my contacts table has a switch 'PrgPers' which is either 1 or 0 (Yes or No). But when I try to reference it, it is not showing as a valid field to reference. Contacts.PrgPers
dinusc Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

The issue with Multi-select items is that you have to compare 2 tables (as opposed 2 records or even a record and a table).
Is the list of Catories static (predefined)? If so then you can try:
Filter(Contacts, If("MyCategory One" in ComboBox1.SelectedItems.Value, "MyCategory One" in Category.Value) || If("MyCategoryTwo" in ComboBox1.SelectedItems.Value, "MyCategory two" in Category.Value))

You could also create a list of checkboxes, one for each category. Then your formula should be:
Filter(Contacts, If(Checkbox1.Value=true, "MyCategory One" in Category.Value) || If(Checkbox1.Value=true, "MyCategory two" in Category.Value))

As for the second issue, Do you get any subvalues after Contacts.PrgPers? For example, Contacts.PrgPers.Value
ruth 212 on at

Like
a
(0)

Report
Copy link

Link copied!

I'm not understanding. Are you saying that "My Category One" is would be the value of one of the categories? So, I currently have 10 categories. I would have to repeat that logic for all 10 categories?

Filter(Contacts,
If("Attorney" in ddlbCategory.SelectedItems.Value, "Attorney" in Category.Value) ||
If("Director" in ddlbCategory.SelectedItems.Value, "Director" in Category.Value) ||
if....
if....)

So it seems like you have to hard code the 10 conditions?
Is this how you read the logic - If Attorney is selected in the ddlb field then select all Attorney in the contacts table?

there doesn't seem to be much difference between the two solutions
Verified answer

dinusc Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Your understanding is correct. It is trickier than the case of a single selection. The difference between the 2 formulas is that one allows to select categories from a ComboBox and another one selects categories by checkboxes.
ruth 212 on at

Like
a
(0)

Report
Copy link

Link copied!

That worked, thanks! Not sure that I like all the If statements but it worked. Thanks again.
ruth 212 on at

Like
a
(0)

Report
Copy link

Link copied!

I'm having one problem with this solution, now. I have a "Director" and "Director Accounting" category, the Director will show up in the Director Accounting filter. i think it's because I'm using "In". is there a way to do an exact match?
dinusc Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I would suggest using "exactin" then. Alternatively, you could add (... && !( "Director Accounting" in Category.Value)). Exactin should be good however.
ruth 212 on at

Like
a
(0)

Report
Copy link

Link copied!

I tried exactin, it doesn't work.

where am i adding this (... && !( "Director Accounting" in Category.Value))
ruth 212 on at

Like
a
(0)

Report
Copy link

Link copied!

Nevermind, i see what you recommend. o.k.