Answered

Creating collection using Filter with two columns - Results returned are wrong?

(0) Share

Report

Posted on by Rob_CTL

Hello!

I have been building an app and I've just hit an issue with the max collection size of 500 items. So to reduce the collection size I am creating it with FILTER to allow me to reduce the 100's of items to a few. The app is using SharePoint as a data source. When I filter on a single item, be that either text column or Yes/No it returns the correct result. When I try and filter by two columns I get completely the wrong information.

Employee = Text Column

IsCurrentItem = Yes/No Column

The result should be just one item returned with the IsCurrentItem column set to True. There are a total of 6 items in the list I am testing against, so the count of 5 in the screenshot shows that it's not including the item with the IsCurrentItem set to True. If I change the formula and exclude either of the columns it returns the right result. Is there some special magic with AND when using with FILTER? This one has me scratching my head!

Any help gratefully received.

Rob

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Ethan_009 4,838 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Rob_CTL ,

Can you try this and let me know if the formula works as intended.

ClearCollect( colGetLastMileageNew, Filter( 'Mileage Record', ( IsBlank(Employee.Text) || Employee.Text = "SharePoint Automation") || ( IsBlank(IsCurrentItem) || IsCurrentItem = true) ) )

Hope this helps

Was this reply helpful? Yes No
Rob_CTL on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Ethan_R

Thanks for the formula, the result was a bit random, it brought back a number of other items that had the IsCurrentItem as True, it didn't filter on the Employee name at all. And even more confusing it also brought back the same records as my screenshot above where the IsCurrentItem is false. Ok, I am now officially very confused! 🙂

Rob

Was this reply helpful? Yes No
Rob_CTL on at

Like
a
(0)

Report
Copy link

Link copied!

Quick update: It is something to do with the Yes/No (boolean), if I change this to a Text column the filtering against both column works as expected. I'd still like to understand if this is a limitation or some other randomness so if anyone has any input?

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Rob_CTL,
Try the below formula and let me know the output;
ClearCollect( colGetLastMileageNew, Filter( 'Mileage Record', Employee.Text = "SharePoint Automation",IsCurrentItem = true ) )

Was this reply helpful? Yes No
Rob_CTL on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous we have a winner! 🙂 so it looks like the And was causing the issue, thanks for the help on this, this one is going in the knowledgebase.

Was this reply helpful? Yes No