Answered

Filter Powerapps gallery using delegable function.

(0) Share

Report

Posted on by ParomitaDas2916

Hi folks ,

I have requirement please help to remove the delegation warning showing for "in" for this below Powerapps code wriiten on a gallery item property

Sort( Filter( Filter( SFB_User_Master, Flag= "Yes" ), Country_Col in varUserCheck ), ID, SortOrder.Ascending )

Where SharePoint list name " SFB_User_Master" having more than 13k records ,contains "User_name"(Single line of text)column,"Country_Col"(single line of text) & "Flag" (single line of text) column.

VarUserCheck : variable can contains more than 2 countries name .

Code for it , written OnVisible property of the screen

Set( varUserCheck, Filter( Dev_SFB_User_Master, User_Email_ID = varloggedinUserEmail ).Country_Col );

VarloggedinUserEmail : user().email

Please help me to solve this issue.

Thanks in Advance.

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

Sort by

Jonathan Manrique 2,695 on at

Like (0)

Report
Copy link

Link copied!

Hi @ParomitaDas2916

By default there are formulas that are delegatable and when you use it you will have the delegation warning.
Perhaps the issue is to change the focus of the formula you are using a little to be able to skip the delegation and above all the fact that it will only return the first 2000 records.
Understand delegation in a canvas app - Power Apps | Microsoft Learn
I share this video that can help you a lot

https://youtu.be/NJaHC5S9Cjo?si=MMQ35XM2Qjbg2aQ1

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution

Was this reply helpful? Yes No
ParomitaDas2916 on at

Like (0)

Report
Copy link

Link copied!

Hello @Jmanriquerios , thanks for quick response.i will check it & get back to you .

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @ParomitaDas2916 ,

Please try below formula:

Sort( UnGroup( ForAll( varUserCheck As UC, Filter( SFB_User_Master, Flag= "Yes" && Country_Col = UC.Country_Col ) ), "Value" ), ID, SortOrder.Ascending )

Best regards,

Was this reply helpful? Yes No
ParomitaDas2916 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni , thanks for the code .If you could let me know what is value of "UC" here.

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @ParomitaDas2916 ,

The As operator is used in the formula for disambiguation. So, the UC is renamed from varUserCheck because it also has the Country_Col column.

Best regards,

Was this reply helpful? Yes No
ParomitaDas2916 on at

Like (0)

Report
Copy link

Link copied!

@v-jefferni , it throwing error as
'Ungroup' is a recognised but not supported function.
The function ' sort' has some invalid arguments.

Please share any other code.

Thanks

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
Hi @ParomitaDas2916 ,

Sorry, I wrote the formula by hand, it should be

Sort( Ungroup( ForAll( varUserCheck As UC, Filter( SFB_User_Master, Flag= "Yes" && Country_Col = UC.Country_Col ) ), "Value" ), ID, SortOrder.Ascending )

Best regards,

Was this reply helpful? Yes No
ParomitaDas2916 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-jefferni it is working now .🥳✨ Thanks for solving my issue.

Was this reply helpful? Yes No
ParomitaDas2916 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni ,sorry for the inconveniences.As I thought this code working fine with no delegation issues & retrieving 4000 showing last record ID 10999records based on filter by country .But after analysing the SharePoint datasource found that after 10999 there is more 1k records based on filter,which is not showing in the gallery.

Please share me any other Code to resolve this issue .

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @ParomitaDas2916 ,

I don't think there is other code. This formula is delegable so you shouldn't lose any entries. Since they're all Text columns, is it possible that some entries have flag/country values with trailing spaces that don't match the filters?

Try below formula in items of a new Gallery, check if the last matching items show on the top of Gallery:

With( {wFilter: Sort(Filter(SFB_User_Master, Flag= "Yes"), ID, SortOrder.Descending)}, Sort( Ungroup( ForAll( varUserCheck As UC, Filter( wFilter, Country_Col = UC.Country_Col ) ), "Value" ), ID, SortOrder.Descending ) )

Best regards,

Was this reply helpful? Yes No