Solution: Overcome Delegation for Aggregate Functions

[mr-dang]

I've previously written about delegation in these threads:

• Early attempt
• Revision
• Progress check

Recently @PaulD1 posted a video in which he presented another solution for overcoming the 500 record limitation using nested galleries. It reminds me of @hpkeong's use of pagination. This has inspired me to create a new solution for not only reading >500 records, but performing aggregate functions >500 records and filters.

New challenges: 

• When showing records in a gallery, only 100 records are presented at a time even if your formulas are delegable. You can get all 500 if you scroll more, but that's not a reliable solution.

Big idea:

• Create a parent gallery with a table containing the whole numbers 1-1000 or however high you want.
  This will represent n, the number of groups of x amount of records to display in each row of the parent gallery.
• Insert a nested gallery (child gallery) which will show records from your datasource in intervals of x records.
  For our purposes we will use x=100 since galleries can only show 100 records at a time.
• Option 1: Collect the live table periodically, and perform calculations upon that collection as in my earlier solutions.
• Option 2: Insert controls that will calculate upon each child gallery.

Limitations:

• This solution is impractical and intense on resources since it's showing data live in gallery. So far, refreshes take ~5s for 1500 records
• Changes may cause the nested galleries to flicker and visually reload all items.
• This solution is limited to however many n is scalable for your purposes
• I've only tried this for CDS

Steps:

1. Create a table in Excel with however many groups of 100 records you expect in your datasource. For me, I listed numbers 1-1000, so I can't have >100,000 records in a datasource. My use cases won't come close, so this is solution fits my needs.
   
   Note: my table, named "Primes," has other columns for determining if the number is prime. It's just an existing table I have around--you just need one column with a series of whole numbers.
   
2. Insert a Slider. This slider is for testing purposes and longevity of this solution. At the moment, a gallery can only show 100 records at a time even if its formula is delegable. That means even calculations will only process the 100 shown until you scroll down for more. Should this limitation ever change, you can tweak this Slider accordingly.
   
   This slider will range from 100-500 and will represent the number of records that will be displayed in each interval, and thus, nested gallery.

   Change Slider1.Min to:
   100
   
   Change Slider1.Max to:
   500
   
   Change Slider1.Default to:
   100

3. Insert a Button that will determine the first and last record of your datasource. This information is used to determine how many n groups of 100 amount of records are actually in your datasource. If your datasource has 500 records, it has n=5 groups of 100 records.
   
   

   Set(firstRecord,First(datasource));
   Set(lastRecord,First(Sort(datasource,RecordId,Descending)))

   
   This means firstRecord is the first record in the datasource. The last record in the datasource is determined by sorting the datasource descending starting with the one with the highest ID number (RecordId), then taking the first result. Unfortunately Last(datasource) doesn't seem delegable.
   
   Note: RecordId is a field that is unique and automatically created for numbering each record in CDS. Replace this with whatever is the unique ID field for your records.
   
   
4. Insert a gallery whose Items property is set to your table of whole numbers. 
   Add columns to the table to determine which set of 100 records a record belongs to. I do this in PowerApps instead of the Excel table because it is a more flexible solution.
   
   

   AddColumns(
       Filter(Primes,
           Number<=RoundUp((lastRecord.RecordId-firstRecord.RecordId)/Slider1.Value,0)
       ),
       "min",(Number-1)*Slider1.Value,
       "max",Number*Slider1.Value
   )

   
   Orange
   My table of whole numbers is called "Primes."
   
   Green and Blue
   The table of whole numbers will be filtered to show only the whole numbers less than or equal to n as calculated in blue. This means if you subtract the ID of the first record from the ID of the last record and divide it by 100, the default value of Slider1, you'll find out how many groups of 100 are in your datasource.
   
   Note: if your unique identifier is not configured as a value, consider wrapping it in Value() or multiplying by 1.
   
   Magenta
   The filtered table has columns "min" and "max" added that will later be used to calculate the range of records for each interval: 1-100, 101-200, 201-300, etc. for x=100. If you adjust the Slider, this gallery will attempt to show that number of records in the nested gallery. It doesn't work yet.
   
   Number is the name of the column in which the whole numbers are listed.
   
   
5. Insert a gallery into your parent gallery. I'll refer to it as the "nested gallery" or "child gallery." 
   
   Change the Items property of the nested gallery to:
   

   Filter(datasource,
       RecordId>=firstRecord.RecordId+ThisItem.min,
       RecordId<firstRecord.RecordId+ThisItem.max
   )

   
   This means that each record in the parent gallery will show a child gallery that houses the records from your datasource that fall in different intervals of 100. The first child gallery will show records 1-100, the second child gallery will show records 101-200 and so on.
   
   The unique identifier RecordId must fall between a number greater than the minimum and maximum values calculated in the previous step. Everything is relative to the first record which doesn't change for our purposes.
   
   Note: again, if your unique identifier (RecordId) is not configured as a value, consider wrapping it in Value() or multiplying by 1.
   
   
6. Insert a Label inside the child gallery to show some data that will help you see that it is working.

___________________________________

Checkpoint:

At this point, you should be seeing 100 records in each child gallery until it reaches the n number of groups of 100.

In the next reply, I'll be describing how to perform aggregate functions upon this nested gallery scheme.

___________________________________

[mr-dang]

Reserved.

[hpkeong]

Hi @mr-dang

I have seen the blog, too and even another Video on Youtube (about an hour video) on the same topics.

Glad to have another solution with even shorter and easier to understand scenario.

I will try this whenve time allowed.

I remmeber i have used both Pagination and Timer to resolve this issue and another wishing that Delegation can always be improved even for CDS for:

- ! (Not)

Have a nice day.