How to calculate from more records than the delegation limit?

(0) Share

Report

Posted on by ramzez

312

I'm looking for suggestions how to calculate from more records than the delegation limit without repeating code.

I have thousands of records in Dataverse from where I need to calculate the count of the records, sum of certain numbers etc. I was thinking of making a context variable of each of them to avoid delegation but this results in repeating the same code.

Here is a sample:

UpdateContext(
 {
 locUnit_theory_duration_in_minutes: 
 Sum(
 ShowColumns(
 Filter(
 'Theory Trackers',
 TheoryUnit = locSelectedUnit,
 cbTrainingTypeFilterStats.Selected.Result = "All Training Types" Or TheoryTrainingType = cbTrainingTypeFilterStats.Selected.Result,
 TheoryDate >= locStartDate And TheoryDate <= locEndDate
 ),
 "cr0c1_instructoruserid",
 "crbc8_instructor",
 "crbc8_name",
 "crbc8_theorydate",
 "crbc8_theorydurationinminutes"
 ),
 crbc8_theorydurationinminutes
 ),

 locUnit_theory_count: 
 Text(
 CountRows(
 ShowColumns(
 Filter(
 'Theory Trackers',
 TheoryUnit = locSelectedUnit,
 cbTrainingTypeFilterStats.Selected.Result = "All Training Types" Or TheoryTrainingType = cbTrainingTypeFilterStats.Selected.Result,
 TheoryDate >= locStartDate And TheoryDate <= locEndDate
 ),
 "cr0c1_instructoruserid",
 "crbc8_instructor",
 "crbc8_name",
 "crbc8_theorydate",
 "crbc8_theorydurationinminutes"
 )
 )
 )

 }
);

Any suggestions how to handle this better?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

developerAJ 4,763 on at

Like
a
(0)

Report
Copy link

Link copied!
you need to use collections here. i will explain the code below use it as reference and complete your requirement. but code to work you need to have some autoincremented column or anything where we can use filter
Set( firstRecord, First(dataversetable) ); Set( lastRecord, First( Sort( dataversetable, ID, Descending ) ) ); //now you have both first and last record Set( iterationsNo, RoundUp( (lastRecord.ID - firstRecord.ID) / 2000, 0 ) ); //here id is autoincremented column Collect(iterations, Sequence(iterationsNo,0)); ForAll( iterations, With( { prevThreshold: Value(Value) * 2000, nextThreshold: (Value(Value) + 1) * 2000 }, If( lastRecord.ID > Value, Collect( LargeListSP, Filter( dataversetable, ID_val > prevThreshold && ID_val <= nextThreshold ) ) ) ) );
at the end you will have a collection with all records of dataverse

Please click Accept as solution and Thumbs Up. if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
You can connect with me on LinkedIn

Was this reply helpful? Yes No
timl 37,285 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @ramzez

I'm a bit confused here because calls to Count and Sum are both delegable.

Are you finding that your formula isn't delegable?

Was this reply helpful? Yes No
ramzez 312 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry I wasn't clear with my question. The code works fine but what I'm looking for is if there is a better way to write it without repeating the same ShowColumns(... every time.

The sample code is just a sample of calculating two values but as I need to calculate multiple values, I would like to know if I need to repeat the same ShowColumns(... code every time or is there a better way.

Was this reply helpful? Yes No
Verified answer

timl 37,285 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

@ramzez

The UDF feature should be out this month in preview. If you wait for this, you can wrap your call to ShowColumns in a function and define parameters for your filter conditions.

https://learn.microsoft.com/en-gb/power-platform/release-plan/2023wave1/power-apps/reuse-power-fx-formulas-user-defined-functions

The other point I'd mention is that the call to ShowColumns isn't particularly necessary when calling aggregate operators such as Count or Sum. For example, returning crbc8_name isn't necessary when summing by crbc8_theorydurationinminutes.

If you just call "Sum(Filter" (eg, omit ShowColumns), the 'explicit column selection' of Dataverse will automatically optimise your formula anyway to return only the necessary columns.

Was this reply helpful? Yes No
ramzez 312 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you on the info about the UDF feature. and ShowColumns UDF is highly anticipated and will make a lot of things easier. I just have to wait until I can use as I am updating an app already in production.

@developerAJ
I'm hesitant in loading more than the delegation limit amount of records into memory (10k-15k) but I'll keep your advice in mind.

Was this reply helpful? Yes No