Answered

Running total in a collection/sharepoint list or How to create a fibonacci sequence inside of a ForAll Loop

(0) Share

Report

Posted on by sbedows

I have a need to create a running total inside of a ForAll loop. Basically a Fibonacci sequence of adding the previous running total to the current record in a collection/sharepoint list. Before I start, let me state that I realize I can hard code this, but I am attempting to find a solution to allow for a dynamic number of steps.

I have a business process that is built on 18 steps. As part of creating the project, the end user edits the SLA for each stage. What the app needs to do next is calculate the due date of each stage, taking into account the number of days used before it. I have all the code worked out for calculating business days/holidays for up to 3 years, but where I am struggling is getting the actual date for each subsequent stage because it doesn't know how many running days to add or what the previous date was. (This is where I know I could create 18 text boxes and assign each a value and do a running total. However I'm trying not to hard code it to allow for when there are more/less stages in the process in the future).

As I said, getting the actual date is not the problem, it's knowing how many days to add. Any thoughts or suggestions would be much appreciated.

		Column I need to calculate
	Days	Total Days	Actual Date
Start	1	1
Stage 2	1	2
Stage 3	3	5
Stage 4	1	6
Stage 5	7	13
Stage 6	3	16
Stage 7	5	21
Stage 8	1	22
Stage 9	10	32
Stage 10	4	36
Stage 11	3	39
Stage 12	3	42
Stage 13	2	44
Stage 14	5	49
Stage 15	3	52
Stage 16	1	53
Stage 17	3	56
Finish	5	61

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@sbedows
Bring your SharePoint list into a collection. I have filtered the list based on a project number

ClearCollect(colData, Filter(your_SP_list, ProjectID=1));

Place your collection inside a gallery.

colData

Then insert a label and use this code in the Text property of the gallery.

Sum(Filter(colData, ID<=ThisItem.ID), Days)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
sbedows 8 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney - Thank you. If I want to collect it back to the same collection (or a new collection), can I use that formula inside a ForAll or some way to create that column in the collection?

Was this reply helpful? Yes No
Verified answer

mdevaney 29,991 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@sbedows

Here's how to do it. Assume we have collection that looks like this

ClearCollect(colData, {ProjectID: 1, StageID: 1, StageDays: 1}, {ProjectID: 1, StageID: 2, StageDays: 2}, {ProjectID: 1, StageID: 3, StageDays: 6}, {ProjectID: 1, StageID: 4, StageDays: 4}, {ProjectID: 2, StageID: 1, StageDays: 3}, {ProjectID: 2, StageID: 2, StageDays: 5} );

Then you can use this code to create a new collection for Project #1 with the RunningTotal included.

ClearCollect( colSolution, AddColumns( Filter(colData, ProjectID=1) As TableXY, "RunningTotal", Sum(Filter(colData, StageID<=TableXY.StageID), StageDays) ) );

The final result will look like this:

ProjectID StageID StageDays RunningTotal

1 1 1 1

1 2 2 3

1 3 6 9

1 4 4 13

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Thrive 34 on at

Like
a
(0)

Report
Copy link

Link copied!
What if you have more than one project ID? Can this be done without hardcoding the Project ID into the filter function?

Filter(colData, ProjectID=1) As TableXY,

Was this reply helpful? Yes No
sbedows 8 on at

Like
a
(0)

Report
Copy link

Link copied!
@Thrive - Yes, you can do this in a ForAll loop. Using the same collection as @mdevaney above, the code would look like...

Clear(colSolution); ForAll(Distinct(colData,ProjectID), Collect( colSolution, AddColumns( Filter(colData, ProjectID=Result) As TableXY, "RunningTotal", Sum(Filter(colData, StageID<=TableXY.StageID, ProjectID=Result), StageDays) ) ));

Was this reply helpful? Yes No