Comparing two sharepoint columns in filter

(0) Share

Report

Posted on by crispybits1977

195

Hi all - probably a ridiculously simple one and I'm a numpty but...

I want to filter a gallery based on a sharepoint list for only the items where one number column is greater than another. I've tried writing it like this and it cuts out all the items and gives me an empty gallery, despite there being around a dozen in my data that would qualify:

Filter('NCP WEX Jobs','Slots Available' > 'Slots Filled')

I've tried googling around and I just can't see what I'm doing wrong. Intellisense isn't giving me any errors or warnings, it all looks fine, it just doesn't display any items.

Help!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Verified answer

Ami K 15,687 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@crispybits1977 -

For whatever reason I have not found time to understand, when filtering directly onto the data source, you will need to wrap either field with the Value function.

Filter('NCP WEX Jobs','Slots Available' > Value('Slots Filled'))

However, an unfortunate side effect is that this is not delegable.

@WarrenBelz / @timl might have more insights on this behaviour.

Was this reply helpful? Yes No
crispybits1977 195 on at

Like (0)

Report
Copy link

Link copied!

Thanks @Amik - I found out the same thing through google in the end (it wasn't easy to find!) so I ended up adding another column in the sharepoint list to do the sum and extra code in my app on all the patches for that column. It being non-delegable isn't an option as I fully expect this list to exceed 2000 records fairly quickly (within a year)

Was this reply helpful? Yes No
Ami K 15,687 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

@crispybits1977 - hopefully that column you added is not a calculated SharePoint column?

Was this reply helpful? Yes No
crispybits1977 195 on at

Like (1)

Report
Copy link

Link copied!

@Amik no just a normal column, that's why I had to edit all the patch commands in my app to include it

Thankfully as part of my googling around trying to work out a workaround I found the info about calculated columns not playing nicely with power apps

Was this reply helpful? Yes No