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

All responses (5)

Answers (1)

Rob_CTL on at

Like (1)

Report

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

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

Community Power Pla... on at

Like (1)

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

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

Like (0)

Report

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

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?
Rob_CTL on at

Like (0)

Report

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

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
Ethan_009 4,838 Super User 2025 Season 1 on at

Like (0)

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

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