Unanswered

How to Filter based off of a multi-select combo box?

(0) Share

Report

Posted on by rprahalad

377

I have a column 'StateNames' in a Dataverse table, where I store a comma delimited string of state names like "Texas, California, Virginia" etc.

Now I have a multi-select combo box on the screen which lists out state names, which is coming from a States Master Table.

When the user picks couple of states from the combo box and clicks on the Search button, I am trying to do this:

Filter(myTable, IsEmpty(cboStates.SelectedItems) || StateNames in cboStates.SelectedItems); and it throws an error: "Invalid Schema. Expected a one-column table".

Can you please help me get past this error? I know it is flawed as both are 'many' strings. I just can't figure out how I can search for each state selected in a loop and go look for it "in" the StateNames column. Should I have to use ForAll here?

Thanks

Categories:

Building Power Apps

All responses (7)

Answers (0)

Sort by

R3dKap 1,594 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @rprahalad,

What's in the Items property of your cboStates combo box?
rprahalad 377 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your response @R3dKap . It is coming from another Lookup/Master table called States. I wish I had stored one individual state value in my StateNames column in myTable. But for other reasons, I had to flatten it and store it as a comma delimited string and somehow try to search on it.

So, basically I have to use Split function somehow because I obviously can't search for 'Texas, Virginia' selected in the combo box and do an "in search" with a column that has 'Texas, California, Virginia' for example.

So, I have to literally do a split of each item value in the selectedItems with the "in" clause somehow.
rprahalad 377 on at

Like
a
(0)

Report
Copy link

Link copied!

I was able to solve it. But not sure if its the most efficient way. I did this first

Clear(colStatesSelected);
ForAll(cboStates.SelectedItems, Collect(colStatesSelected, 'State Name'));

Next,

ForAll(colStatesSelected, Collect(colMyTableRecords, Filter(myTable, Value in StateNames)));
R3dKap 1,594 on at

Like
a
(1)

Report
Copy link

Link copied!

I suppose this thread is somehow linked to your other thread here: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-do-I-filter-based-off-of-a-Lookup-Column/td-p/924770/jump-to/first-unread-message where I just answered that there is no workaround for getting rid of the delegation problem in your particular case.

I suppose your IN operator in your last line of code above also has a delegation issue, isn't it?
rprahalad 377 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes! You are absolutely right. This led to the other one:-).
PaulD1 2,914 on at

Like
a
(2)

Report
Copy link

Link copied!

The following may work for you (is delegable) but is long winded to code and has some limitations.

StateName = First(colStatesSelected)'.State Name', Or

StateName = Coalesce(Last(FirstN(colStatesSelected,2)).'State Name',"X") Or

StateName = Coalesce(Last(FirstN(colStatesSelected,3)).'State Name',"X") Or

... and so on

This is taking your list of selected states and turning them into a series of Or expressions. We don't know how many choices the user has made, so we have to coalesce with a value that will not be valid (not a State Name in this case).

You'll need to add an Or expression up to the maximum number of States you think a user might select (you might want to put some code on the OnChange of the combo to give the user a warning if they have exceeded the number of choices you have code for).

You also have to build your filter so that you only reference the above code if the user has selected one or more State Names, so you may need a series of outer IFs that run different filters depending on what options the user has selected.

Also note that the Odata query can only be so long, so if you code for too many choices, you may fall foul of that limitation.

Of course, all this could be avoided if the IN operator were properly delegated or we could write queries directly against the datasource in its native querying language...
rprahalad 377 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @PaulD1. That's another good way indeed. I did use Concat(cboStates.SelectedItems, Name, ",") or something to that effect.