Power Platform Community Forum Thread Details

Hi all,

I've a Principle and Interest generator. I give it the inputs and it generates a Principal and Interest Schedule. What I am trying to do is to have the subsequent records containing Principal and Interest update when I update the Principal amount of a certain record.

If anyone have any ideas of how to go about this it would be greatly appreciated.

These are the inputs:

The Output when a user presses "Generate":

This is the result when I update a certain record in gallery. But I would like all the following records to update too once I click the red "save" button. I haven't been able to come up with an idea that will update the subsequent records but preserve the preceding records. - Any advice would be welcome.

This the code I use to Patch my collection located in the red "save" button:

Patch(myCollection, Gallery5_1.Selected, {Int: Value(TextInput7_1.Text), Pik: Value(TextInput7_4.Text), Pri: Value(TextInput7.Text)});

This is the bulk of the code used to Generate the charts and gallery:

Set(
 varSpinnerPrincipleandInterest,
 true
);

//Logic for Principle and Cash interest calculator starts
If(
 varRuns = 0,
 Set(
 varPri,
 Value(
 Text(
 'Principle.txt'.Text,
 "[$-en-US]###,###,###.00"
 )
 )
 );
 //Sets a variable for interest amount
 Set(
 varInt,
 Value('Interest.txt'.Text) / 100 * varPri
 );
 //Sets a variable for PIK amount
 Set(
 varPIK,
 Value('PIK.txt'.Text) / 100 * varPri
 );
 //Sets a variable for the start of the schedule
 Set(
 varDate,
 StartDate.SelectedDate
 );
 //Sets a variable for the ID for each record
 Set(
 varID,
 1
 );

 //Creates the table of the Principle and Interest Schedule 
 ClearCollect(
 myCollection,
 {
 Period: varDate,
 Pri: varPri,
 Int: varInt,
 Pik: varPIK,
 ID: varID
 }
 );
 ,
 //Adds a new record to the Principle and Interest Schedule based on the number of quarters (each quarter gets a record)
 Patch(
 myCollection,
 Defaults(myCollection),
 {
 Period: varDate,
 Int: varInt,
 Pri: varPri,
 Pik: varPIK,
 ID: varID
 }
 );
 Set(
 varInt,
 varInt
 );
 Set(
 varPri,
 varPri
 );
 Set(
 varPIK,
 varPIK
 );
 Set(
 varDate,
 varDate
 );
 Set(
 varID,
 varID
 );
 
);
Set(
 varPri,
 varPri - varInt
);
Set(
 varInt,
 varPri * Value('Interest.txt'.Text) / 100
);
Set(
 varPIK,
 (varPri + varPIK) * Value('PIK.txt'.Text) / 100
);
Set(
 varDate,
 DateAdd(
 varDate,
 90
 )
);
Set(
 varID,
 varID + 1
);
//Logic for Principle and Interest Schedule ends
UpdateContext({varRuns: varRuns + 1});
If(
 varRuns >= varLoopEnd - 1,
 UpdateContext({varTimerStart: false})
);

Many thanks,

Conor

Categories:

Building Power Apps

Hi @cnr ,

First of all, that's a pretty impressive chart - well done! 😀

So if I read this right, your table is a single-shot creation, start to finish, looping through a timer to create subsequent records. The challenge then, is to use another method to update the existing records, and here it get's tricky. The timer works, but I'd recommend a different approach to first create the table.

Then, updates take that same logic, but only apply it from the point at which you update.

Now first a disclaimer - interest logic makes my brain hurt - so you may need to check my calculations here as I'm not entirely sure what the rest of the graph should look like, but focus on the mechanics of updating the rest of the graph first, then you can fiddle with the math to get the results you want.

First: You can continue to use the timer, but I'd suggest trying this alternative first, and if you absolutely have to go back to the timer, you can copy/paste and panelbeat it to work in the timer. You might want to duplicate your page, so we don't break everything you already have, but then you need to check the names of the controls and make sure the code matches up to your duplicated page controls.

Create a button on your new page, set it's OnSelect: property to the following;

Clear(myCollection);
ForAll(Sequence(Value(varLoopEnd.Text), 0) As Loop,
With({
 StartingPrincipal: Value(Text('Principle.txt'.Text,"[$-en-US]###,###,###.00")),
 InterestRate: Value('Interest.txt'.Text) / 100,
 PikRate: Value('PIK.txt'.Text) / 100},
Collect(
 myCollection,
 {
 Period: DateAdd(StartDate.SelectedDate, (90 * Loop.Value), Days),
 Pri: If(Loop.Value = 0, StartingPrincipal, 
 Last(myCollection).Pri - Last(myCollection).Int),
 Int: If(Loop.Value = 0, StartingPrincipal * InterestRate, 
 (Last(myCollection).Pri - Last(myCollection).Int) * InterestRate),
 Pik: If(Loop.Value = 0, PikRate * StartingPrincipal,
 ((Last(myCollection).Pri - Last(myCollection).Int + Last(myCollection).Pik) * PikRate)), //(varPri + varPIK) * Value('PIK.txt'.Text) / 100
 ID: Loop.Value
 }
 )));

It does the same thing as the timer, but a little cleaner and faster (in my opinion anyway).

We create a sequence table which creates your index for you, starting at 0. Then for each row of that sequence table, we collect the data for each period, including the current sequence as the ID.

We use a "first iteration" If() loop = 0 to catch the first iteration, thereafter incrementing dates and recalculating as we go, based on the previous iterations data to get the current iteration data.

Next, a little UI logic to save us coding effort. Presumably, if someone changes the first record, it's the same as recalculating the entire thing - so rather than have to build the logic for all that, make the inputs on the first record view only, and hide the save button for the first record;

Inputs DisplayMode:

If(ThisItem.ID=0, DisplayMode.View, DisplayMode.Edit)

Save button Visible:

ThisItem.ID>0

If this isn't what you want, leave it out, but you'll have to build in your entire recalc logic into the save button as well otherwise.

Next, change your save button OnSelect: property to the following (again, note the names of the input boxes as I've named mine - you'll have to match it with yours, or rename yours 😊)

//update the current record 
Patch(myCollection, Parent.Selected, {
 Int: Value(InputInt.Text), 
 Pik: Value(InputPik.Text), 
 Pri: Value(Input_Pri.Text)}); 

//create a staging table containing all the remaining records we can use to reference our updates to the main table
ClearCollect(collectStaging, Filter(myCollection, ID>ThisItem.ID)); 

//using the staging table, iterate through the matching records in the main table, updating them as we go and thanks to the UI logic mentioned above, we don't have to cater for someone editing the first record - otherwise this logic would break as there is no previous record to the first record

ForAll(collectStaging As UpdateRecord, 
 With({
 previousRecord: LookUp(myCollection, ID=UpdateRecord.ID -1)
 }, //this gives us a handy record variable of the previous record, relative to the current record, to reference in our code - again, just keeps things neat

 Patch(myCollection, LookUp(myCollection, ID=UpdateRecord.ID), {
 Pri: previousRecord.Pri - previousRecord.Int,
 Int: (previousRecord.Pri - previousRecord.Int) * (Value('Interest.txt'.Text) / 100),
 Pik: ((previousRecord.Pri - previousRecord.Int + previousRecord.Pik) * Value('PIK.txt'.Text) / 100) 
 })
 )
 )

Now again - the math here might be wrong, but the mechanics should work - try it out and let me know how it goes 😁

WBP