Delegation Challenge without using Power Automate

[AmínAA]

Greetings!

I am facing a probably not so hard difficulty (Hopefully) while building an App. A client has asked me to be able to sort a specific table, by the ammount of times each record on Table A is used on all the records of table B, bear in mind I'm using Dataverse Tables. Now I've tried making it work exclusively with PowerApps but I have found no way to make this work, since most of the functions I can think of to make this happen, fall into non-delegability.

I have thought about using either Calculated or Formulas columns in Dataverse to make that calculation in advance, however since there is no Count or Count variant function I have literally no clue on how to achieve this without using a complex Power Automate operation to make it happen. So, if anyone can come up with something that's not a PowerAutomate function I'll be glad to hear it, so thanks in advance!

DATAVERSE TABLE A

Column "L1_CODE": Contains a single leter

Desired column "L1_QUANTITY": Contains the ammount of records that the DATAVERSE TABLE B holds on each "L1_CODE" record. So that if the first L1_RECORD is "L" and it is used in 30 different record, the column for the record "L" will hold the number 30.

DATAVERSE TABLE B

Column "L1_CODE": Contains a Lookup to the DATAVERSE TABLE A.

Best of lucks, and once again, thank you all for the help and time put into helping out!

[Amik · Accepted solution]

@AmínAA - not the answer you will want to hear, but you're correct that you will need non-delegable functions for this.

However, if you can reduce the number of rows in your query by a specific criteria using delegable Filters, you will be able run those non-delegable functions on that filtered subset so long as the number of rows returned in that filtered subset is under your delegation limit.

Otherwise, Power Automate is a worthwhile option, it is robust and fast.

[ivan_apps · Accepted solution]

One thing I always suggest when delegation is an issue is considering Model-Driven Apps instead of canvas apps. You can have the custom UI and design of Canvas apps via Custom Pages, but when it comes to filter criteria and data intake via a form, MDAs do it best and quickly. Views can be created and customized to show what you need and used on lookups. They can be further extended via JavaScript if needed. Since you already have licenses, it’s a worthwhile consideration.

now if you want to stick to Canvas apps, Reza Dorani has a great video on delegation with “in” operators. Search for that and you will see an example where he gets around delegation by having 2 galleries.

Gallery1 is a DelegableGallery that includes as many filters that are fully delegable. This gallery is then hidden.

Gallery2 points to Gallery1 as its data source. Your non-delegable filters are here and they will filter the results of Gallery1. You also make sure Gallery 2 is a flex height gallery so that when there are special conditions that can’t be separated between delegable and non-delegable - you can simply hide the row. When hiding the entire row, the flex height gallery will collapse the white space and make it look like it’s filtered at the data source. It’s a great trick but requires a good bit of setup for it to work properly. 

best of luck!