Nice inventive workaround (and not being in any way negative as I am always looking for Delegation solutions), but I tested it on a 4.5k list and it took over a minute to finally resolve a couple of hundred records with a four-character string value found in a part of a field throughout the list. I suspect two things - firstly galleries on a Delegable query only return 100 rows at a time and "the rest" needs to be then triggered by the Visible change and also having to resolve the height of 4,500 rows following a change would have some overheads.
Hi Warren, I agree. my computer that I ran this on actually is super fast. With a fiber internet. I have realized it may be able to beat the delegation, but this requires a very fast computer and internet speed to query. With my work laptop it seems to bog down a good bit.
@@andrewhess123 Thanks - I suspected that was the case - I normally resort to big collections. I have one with the newest 4,000 records that performs pretty well on a Search.
The issue is that it is not resolving delegation at all but making PowerApps request for each row as it resolves the visible condition. The best search solution for SP List is a combination of Power Automate and the SP rest API with the substringof operator. I'll be sharing a blog post on this soon
Wow, this was really good. Would like to hear how you figured out to use blank flex gallery, setting visible field (the special way) and the concat stuff. There's a bunch of UA-cams with workarounds to the delegation issue. But this is the most clear-cut and simple I've seen. Good stuff, thanks very much.
Just playing around and messing around, I also literally listen to PowerApps tutorials as I drive, then try out different things. I think somewhere I heard about using flexible galleries this way.
Hi Tigere, I tested over 5000 results, and was successful. I am not which type of data you are querying and the reason you can't query beyond 2000. Do you still get the warning in your PowerApp for delegation? Maybe the best option is to try a simple datasource like just the title field with the 30,000 units and then build from there.
How about filtering with DATE column Filter(SP_Labour,Shift=HeaderShift,LineID=HeaderDeptName, SheetDate = HeaderSelectedDate) this is my code SheetDate = sign has delegation warning your thoughts are appreciated
Hi tuffley, I haven't done this myself but what I've heard is that you can use Flow/PowerAutomate to push the date column to a delegable column. You could push it to a number column like YYYYMMDD and the filter based on that. Instead of trying to filter on the date column. Filter on the date currently is the problem.
Yes, I have the same situation .....My sharepoint list has almost 2000 rows ,after I config "Visible " . But the false items are empty but show area in the gallery , how to I shrink it ??
Hi Giorgio, so I just tried again. With a SharePoint list with over 6500 line items. What I did was made an infinite loop in PowerAutomate real quick to make myself a bunch of line items. Then I used the same PowerApp. The Screenshots shown here(my blog) prove that it works: myspquestions.wordpress.com/over-217-line-items/
@@andrewhess123 First I thank you for making this extra effort for me, your word would have been enough. Second, Incredible!! What an outstanding simple idea. Thank you!!
@@giorgiosurian2190 No problem, you had me questioning myself. I was wondering myself... "does it work right?" "Is it just a way around the warning?" but yeah it worked
That's amazing! Thanks for this trick! Have you tried this with nested flexible galleries? I am trying to emulate a Chained "double lookup" but I get stuck with the flexible gallery inside the main one. Dynamic item visibility does not work. If you still read comments, I am curious to get your feedback 😉
Hi Benjix, My channel is still fairly new, I am responding to all comments. :D I have not tried using nested flexible galleries yet. Feel free to share your experiences with it.
@@andrewhess123 Hi, thanks for your reply. Actually I have three lists chained by one-to-many relationships. Let's say list A, then list B, which as a lookup field pointing to list A, and then list C, which has a lookup field pointing to list B. I am trying to figure out a delegable way to achieve filtering list A on some conditions on list C. Normally you would use SQL-like joins. As list A is roughly modeled to act like a "container" of items from list B, which in turn "contains" items from list C, I typically would like to get items on list A that contain items of list B that comply with a given condition on items of list C. To give an example, suppose items of list A are folders, items of list B are documents and items of list C are document reviews (approval steps). I would like to display in a gallery folders that have documents (nested gallery) that in turn have at least a review step assigned to a specific user. I tried to play on the visibility property of the nested gallery (list B), and added a condition on item visibility of the outer gallery (list A) (the item must have a visible nested gallery to be visible). But this does not work. Besides, unless I have made mistakes, it seems that using the same method you presented in a nested gallery is not working: the gallery is either completely visible or totally hidden, but no filtering happens. Sorry for this long post 😃 I was a little bit hard to explain. I hope I was clear enough.
@@benjixlegaulois5000 Hi Benjix I have done something similar, but I didn't do all the nesting inside nested galleries. What I did was you would select the first gallery option and it would filter the next gallery on a new screen. Then when you selected on the 2nd gallery it would be filtered based on that selection on another gallery on a 3rd screen. Then I would have a search bar at the top of each screen that was specific to which gallery was shown.
@@andrewhess123 I thought about this idea, but I was too afraid that it would cause delegation issues and I did not even dare. I am curious, did you try on large datasets just to know whether the downstream gallery (the final one) would correctly return results without any delegation problems (missed results as it would be that case if we would use collections as intermediate steps)? Anyway I will give it a try and keep you informed 😉
Great idea, but I’m trying to figure why it works 😎 I use collections to get 4000 records, but you have to load all of those records to your app and it’s still limited. Although I haven’t hit the limit
True this was just one of those things i figured out a while back. Now I think Dataverse for Teams may be my new way to beat delegation without premium licenses.
@@jray1429 Well there is, Dataverse, and Dataverse for Teams, I would say SharePoint has only advantages of simplicity. Dataverse requires premium licenses, Dataverse for Teams does not, like SharePoint.
Yes, that is the purpose. Use the visibility in a Flexible Gallery instead of using a Search Query in the items function of a regular gallery. As long as there are no other issues that are causing delegation. This just solves the search issue.
@@MizanNet Hi Mizan I just tested with 116 items and it worked. I could test a 2000 item SharePoint list, but I will have to set that up, since I don't have one set up on my Dev Environment.
Nice inventive workaround (and not being in any way negative as I am always looking for Delegation solutions), but I tested it on a 4.5k list and it took over a minute to finally resolve a couple of hundred records with a four-character string value found in a part of a field throughout the list.
I suspect two things - firstly galleries on a Delegable query only return 100 rows at a time and "the rest" needs to be then triggered by the Visible change and also having to resolve the height of 4,500 rows following a change would have some overheads.
Hi Warren, I agree. my computer that I ran this on actually is super fast. With a fiber internet. I have realized it may be able to beat the delegation, but this requires a very fast computer and internet speed to query. With my work laptop it seems to bog down a good bit.
@@andrewhess123 Thanks - I suspected that was the case - I normally resort to big collections. I have one with the newest 4,000 records that performs pretty well on a Search.
The issue is that it is not resolving delegation at all but making PowerApps request for each row as it resolves the visible condition. The best search solution for SP List is a combination of Power Automate and the SP rest API with the substringof operator. I'll be sharing a blog post on this soon
Wow, this was really good. Would like to hear how you figured out to use blank flex gallery, setting visible field (the special way) and the concat stuff. There's a bunch of UA-cams with workarounds to the delegation issue. But this is the most clear-cut and simple I've seen. Good stuff, thanks very much.
Just playing around and messing around, I also literally listen to PowerApps tutorials as I drive, then try out different things. I think somewhere I heard about using flexible galleries this way.
@@andrewhess123 Cool, I'll have to try listening while I'm driving. Might catch on to things quicker. Thanks!
very cool, I like this idea! FYI you can achieve a cleaner Concat using "Concat(ComboBoxSample,Value1," ,")"
My data source has 30k unique units.i thought this might work for me but it's not finding the results past 2000
Hi Tigere, I tested over 5000 results, and was successful. I am not which type of data you are querying and the reason you can't query beyond 2000. Do you still get the warning in your PowerApp for delegation? Maybe the best option is to try a simple datasource like just the title field with the 30,000 units and then build from there.
Hello! would you have a workaround for countrows to display more than 2k records in a sharepoint list?
Hi @fibonacci not sure about that one.
How about filtering with DATE column
Filter(SP_Labour,Shift=HeaderShift,LineID=HeaderDeptName, SheetDate = HeaderSelectedDate)
this is my code SheetDate = sign has delegation warning
your thoughts are appreciated
Hi tuffley, I haven't done this myself but what I've heard is that you can use Flow/PowerAutomate to push the date column to a delegable column. You could push it to a number column like YYYYMMDD and the filter based on that. Instead of trying to filter on the date column. Filter on the date currently is the problem.
Maybe even a powerautomate isnt required at all, just convert the date to a number.
Awesome video! Thanks for sharing!
Thanks for watching!
Hi, my flexible gallery doesn't shrink the invisible items.
What's wrong?
Thanks alot
Hi Mario, Are you make all the text boxs, labels, icons, etc.. not visible inside on the gallery items you want to shrink? Did you group them?
@@andrewhess123 I have the same issue. They are not visible inside the gallery and group them. Please help :)
Yes, I have the same situation .....My sharepoint list has almost 2000 rows ,after I config "Visible " .
But the false items are empty but show area in the gallery , how to I shrink it ??
Did u try on large datasets?
There are so many ways to cheat on warnings but actually not solving them, that I have to ask..
Yes I tried on a large dataset in SharePoint.
Hi Giorgio, so I just tried again. With a SharePoint list with over 6500 line items. What I did was made an infinite loop in PowerAutomate real quick to make myself a bunch of line items. Then I used the same PowerApp. The Screenshots shown here(my blog) prove that it works: myspquestions.wordpress.com/over-217-line-items/
@@andrewhess123
First I thank you for making this extra effort for me, your word would have been enough.
Second, Incredible!! What an outstanding simple idea.
Thank you!!
@@giorgiosurian2190 No problem, you had me questioning myself. I was wondering myself... "does it work right?" "Is it just a way around the warning?" but yeah it worked
That's amazing! Thanks for this trick!
Have you tried this with nested flexible galleries? I am trying to emulate a Chained "double lookup" but I get stuck with the flexible gallery inside the main one. Dynamic item visibility does not work.
If you still read comments, I am curious to get your feedback 😉
Hi Benjix, My channel is still fairly new, I am responding to all comments. :D I have not tried using nested flexible galleries yet. Feel free to share your experiences with it.
@@andrewhess123 Hi, thanks for your reply. Actually I have three lists chained by one-to-many relationships. Let's say list A, then list B, which as a lookup field pointing to list A, and then list C, which has a lookup field pointing to list B. I am trying to figure out a delegable way to achieve filtering list A on some conditions on list C. Normally you would use SQL-like joins.
As list A is roughly modeled to act like a "container" of items from list B, which in turn "contains" items from list C, I typically would like to get items on list A that contain items of list B that comply with a given condition on items of list C.
To give an example, suppose items of list A are folders, items of list B are documents and items of list C are document reviews (approval steps). I would like to display in a gallery folders that have documents (nested gallery) that in turn have at least a review step assigned to a specific user.
I tried to play on the visibility property of the nested gallery (list B), and added a condition on item visibility of the outer gallery (list A) (the item must have a visible nested gallery to be visible). But this does not work. Besides, unless I have made mistakes, it seems that using the same method you presented in a nested gallery is not working: the gallery is either completely visible or totally hidden, but no filtering happens.
Sorry for this long post 😃
I was a little bit hard to explain. I hope I was clear enough.
@@benjixlegaulois5000 Hi Benjix I have done something similar, but I didn't do all the nesting inside nested galleries. What I did was you would select the first gallery option and it would filter the next gallery on a new screen. Then when you selected on the 2nd gallery it would be filtered based on that selection on another gallery on a 3rd screen. Then I would have a search bar at the top of each screen that was specific to which gallery was shown.
@@andrewhess123 I thought about this idea, but I was too afraid that it would cause delegation issues and I did not even dare. I am curious, did you try on large datasets just to know whether the downstream gallery (the final one) would correctly return results without any delegation problems (missed results as it would be that case if we would use collections as intermediate steps)? Anyway I will give it a try and keep you informed 😉
@@benjixlegaulois5000 My datasets were large, over 10k, but using SQL tables... it depends on delegation issues.
Great idea, but I’m trying to figure why it works 😎
I use collections to get 4000 records, but you have to load all of those records to your app and it’s still limited. Although I haven’t hit the limit
True this was just one of those things i figured out a while back. Now I think Dataverse for Teams may be my new way to beat delegation without premium licenses.
@@andrewhess123 Does Sharepoint have some advantages over Dataverse❓ Are there any❓
@@jray1429 Well there is, Dataverse, and Dataverse for Teams, I would say SharePoint has only advantages of simplicity. Dataverse requires premium licenses, Dataverse for Teams does not, like SharePoint.
Wow this will help me out a lot. Thak you. Will this work for searching more than 2000 items and can I use the text box and search by keyword?
Yes, that is the purpose. Use the visibility in a Flexible Gallery instead of using a Search Query in the items function of a regular gallery. As long as there are no other issues that are causing delegation. This just solves the search issue.
Check out 6:25
@@andrewhess123 Thanks. It seems to fail if I attempt to search visible item more than 110 but I do like the idea
@@MizanNet Hi Mizan I just tested with 116 items and it worked. I could test a 2000 item SharePoint list, but I will have to set that up, since I don't have one set up on my Dev Environment.
Here on my blog article I have over 217 line items and it is still showing. myspquestions.wordpress.com/over-217-line-items/
This was really helpful. Does this also work with datasets over 10k rows?
I haven't tried over 10k
👍