Dobule Filter on Gallery (filtering listbox,dropdown fields) giving Cross reference error

[Z_Lukowski]

Hello Powerusers,

In my canvas App I have:

a) Dropdown1 - Orders.'Status'

b) Listbox1 - Orders.'Prefered day'

A Gallery which I want to filter based on Dropdown1 and Listbox1 values I've tried something like this:

Filter(Orders,'Prefered day' in ListBox1.SelectedItems.Result And Status in Dropdown1.Selected.'Status ')

But keep getting Cross reference error:

Would appreciate any kind of help,

Best Regards

Z.

[v-yangar-msft]

Hi @Z_Lukowski ,

I don't know what's the data source you used in your app.

I assume that you use the SharePoint List. Here is my structure of my data source as below:

Firstly, i added a drop down list control and list box control.

Set the items property of drop down to:

Choices(DateList.Status)

Set the items property of list box to:

Distinct(DateList,PreferredDay)

Then, add a gallery and set the items of it to:

Filter(DateList, Dropdown1.Selected.Value in Status.Value,ListBox1.Selected.Result = PreferredDay)

 Finally, we can find that when i choose the status 'Delivery' and PreferredDay'1/3/2021', the gallery will filter the records and show as below:

Hope it helps!

Thanks,

Arrow

[Drrickryp]

@Z_Lukowski 

You could avoid using a choice field altogether by simply hard coding your status items into the dropdown

["New","Delivery","Completed","Cancelled"] as these fields are not likely to change.  You can then change the column type in your SharePoint list to single line of text without losing any data.  This will help you avoid some problems that are inherent in Choice type columns. 

[Nate-PTTP · Accepted solution]

Hi! instead of your current formula of:

Filter(Orders,'Prefered day' in ListBox1.SelectedItems.Result And Status in Dropdown1.Selected.'Status ')

remove that "And" in the middle of your logical statement in the filter formula and try one of the following (both should work for you"

Filter(Orders, And('Prefered day' in ListBox1.SelectedItems.Result, Status in Dropdown1.Selected.'Status '))

Or....

Filter(Filter(Orders,'Prefered day' in ListBox1.SelectedItems.Result), Status in Dropdown1.Selected.'Status ')

As a last word of caution, avoid using the "in" logical operator to filter your data (if possible). Tie your ListBox and dropDown items directly to the specific columns in your data, and use the this = that as opposed to this in that. your future self will thank you.

I recently made a video about filtering based on multiple criteria.... perhaps this may be of use to you!

https://youtu.be/0bRR-Zq407c 

Have a great day 🙂

[Z_Lukowski]

I've found "=" hint in the meantime and of course that fixed the problem also it's easy to miss and refer to Column name in PowerApps instead of Column in the table I was using (my data source was CDS, forgot to mention this).