How to effecively delegate Filter on User().Email attribute of Records Gallery?

(0) Share

Report

Posted on by DD-02090909-0

Hello PA community!

I have a super large SharePoint list (expected to cross 100,000 items)

I want users to be able to view and edit (i.e. provide their response) only to specific portions of the list

So I created a mix using separate PowerApp read-only form and edit form

1) I further want the users to only view and edit list items where there is no response already provided (Col1.Value = Blank())

2) I also need that specific users are able to see only the items relevant to their area. I manage this through a field in the SharePoint column called Col2.This field has email addresses concatenated by semi-colon ;

For example, abc@gmail.com; def@gmail.com; pqr@gmail.com

Some records may have one value only in Col2 field (abc@gmail.com), some may have 2 concatenated values (abc@gmail.com; def@gmail.com) while others may have 3 or more concatenated values (abc@gmail.com; def@gmail.com; pqr@gmail.com)

i.e. this number is variable

The filter that I have set on the records gallery is:

Filter(List_Name, Col1.Value = Blank() && User().Email in ThisRecord.Col2)

Currently my list size is ~45,000 records

For testing purposes, I have updated a few records (<2000) with my own email address

However, I am unable to see any items in the Power App

I expect to see the records with my email ID in Col2

I am 100% certain that this delegation problem due to large dataset size, because the exact filter works on lists with smaller record count.

So my question is : How can I improve my Filter query so that this filtration gets delegated appropriately to SharePoint and I can see the records where Col2 has my email ID in it?

Happy Friday and thanks in advance!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

v-yueyun-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi , @powerautouser20

As for the document, "in" function is not delegable for SharePoint now.

As for the workaround , you can try to create a flexible height gallery.

And you need to put the delegable filter function in Gallery -items:(Here, i directly put the table name in it)

Then you can put the control you need on this gallery. And then we can select all the control in the Gallery and put this code in the Visibile:

Office365Users.MyProfileV2().mail in ThisItem.Persons

Or

User().Email in ThisItem.Persons

You may need to replace your column name in your side.

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yueyun-msft , thanks for your suggestion.
I am trying it at my end.
If I understand correctly, you suggest to:
1) create another object <ObjectName> within my App, i.e. the Fexible Height Gallery
2) transfer delegable filter there i.e. Filter(List_Name, Col1.Value = Blank())
3) Filter on this object using Filter(User.email() in <ObjectName>.<ColName>)

Please let me know if that's correct.
Apologies, but I am not very clear about how you are putting control / code visible, see my screenshot below

Thank you!

I am not

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

Like
a
(0)

Report
Copy link

Link copied!

Hi , @powerautouser20

Yes , i mean you can try to add another control called "Fexible Height Gallery".

You new "Fexible Height Gallery"- items can be:

Filter(List_Name, Col1.Value = Blank())

Then in Gallery , you can add the control you want to add in this "Fexible Height Gallery":

Then you can select all the controls in this Gallery (Here is "Label3" and "Label4"):
Then put this code in the Visible:

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!

Best Regards,

Yueyun Zhang

Was this reply helpful? Yes No
DD-02090909-0 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for your explanation. I see the logic of it.
I got it to work till adding the Flexible height gallery -> Items = Filter(List_Name, Col1.Value = Blank()) -> Add required controls as Text boxes -> Select all boxes -> put email address formula in the Visible property

I still don't see any values, most likely because the underlined part above, is returning more than 2000 items.
I see values when I remove this formula and simply set Visible = True
Moreover, there is no error in how I have implemented the formula, as there are no squiggly lines in the Visible property of any of the controls I have inserted in the Flexible Height Gallery

That makes me strongly suspect that underlined part is returning more than 2000 items.

Is there any workaround for such situation (in bold above)?

Was this reply helpful? Yes No