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

(0) Share

Report

Posted on by Z_Lukowski

216

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

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-yangar-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
Verified answer

NH-05081754-0 on at

Like
a
(1)

Report
Copy link

Link copied!

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 🙂

Was this reply helpful? Yes No
Z_Lukowski 216 on at

Like
a
(1)

Report
Copy link

Link copied!

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).

Was this reply helpful? Yes No