cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Z_Lukowski
Helper II
Helper II

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 II
Super User II

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (90,691)