cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Z_Lukowski
Resolver I
Resolver I

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

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:

Z_Lukowski_0-1612261634706.png

 

Would appreciate any kind of help,

Best Regards

Z.

1 ACCEPTED SOLUTION

Accepted Solutions
Nate-PTTP
New Member

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 🙂

View solution in original post

4 REPLIES 4
v-yangar-msft
Microsoft
Microsoft

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:

v-yangar-msft_0-1612335690617.png

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:

v-yangar-msft_1-1612335995183.png

 

Hope it helps!

 

Thanks,

Arrow

Drrickryp
Super User
Super User

@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
New Member

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 🙂

View solution in original post

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

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.

Top Solution Authors
Top Kudoed Authors
Users online (3,406)