how to cummulative total

[Englishweb]

Hello

Can anyone suggest the easiest way to keep a permanent record of the Total Hrs ?  The Total Hrs is calculated from the Hrs in the PowerApp . I want to reset the Hrs control in a gallery record to 0 but keep accumulating an archive of the Total Hrs. Is this possible? Hope this makes sense.

Thanks!

[WarrenBelz]

Hi @Englishweb ,

Try a Global Variable Set(vHours = vHours + yourfield)

[Englishweb]

Hello @WarrenBelz
Thanks for your quick reply. Could you explain in a bit more detail (to a non-programmer) how to do this?
Thanks!
Thanks

[WarrenBelz]

Hi @Englishweb ,

I just noticed you said "Permanent", so I assume you want these when the App is closed?

If so, I will change my strategy a bit - let me know and also I assume you have a "master" list where this record would apply one per item.

[Englishweb]

Hello @WarrenBelz 

Yes I am using an excel spreadsheet as the datasource to patch the values for each of 3 gallery records for tutoring hours: Hrs1+ Hrs2+ Hrs3 = Total Hrs.  So far so good...

I would now like to reuse the same Gallery records by clearing certain information (including the Hrs) for a new set of students. The problem is that I need an archive of the Total Hrs already accumulated for each tutor. At the moment this is obviously lost when I clear the gallery fields.

Is there a solution?

Thanks

[WarrenBelz]

Hello @Englishweb 

I do not use Excel as active data storage, but I will summarise what I would do in SharePoint and maybe you can parallel it in your world.

I would have a "master" SharePoint list of Tutors and probably use a numeric ID that would also be stored as a reference in your Excel sheet. In PowerApps, Collect the list at app start and at the point just before you reset, add the hours from the current totals (this would be a ForAll statement based on the ID above), to the current ones in the Collection then Patch the SharePoint list with the new totals.

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.

[Englishweb]

Hello @WarrenBelz 

Your solution is interesting and it would work if all 3 records were cleared at once.

In my case any one of the 3 records may be cleared by a user at any time so I can't see how this would work.

[WarrenBelz]

Hi @Englishweb ‘

Would this not be easier, simply Patch the particular record back as the first action before the total is cleared?

[Englishweb]

Hello @WarrenBelz 

Thanks for your reply. I am patching the records back to Excel before the total is cleared, but writing new data to the same record obviously overwrites the existing data. 

Each record is linked to a person's tutoring task. I am trying to preserve a history of the total number of hours of tutoring each person has undertaken over the course of the school year.

Is there a way to patch data to a new cell or row each time that the form is cleared? I don't know in advance how many times the records may be cleared and reused.

[WarrenBelz]

Hello @Englishweb ,

As per my suggestion on collecting first, I will call the collection colCurrentHours, your data source Tutors, with fields Tutor and TotalHours.

So the first thing you would do on app start is collect this - something like

ClearCollect(
   ColCurrentHours,
   ShowColumns(
      Tutors,
      "Tutor",
      "TotalHours"
   )
)

You would then have a two-column collection with the Tutor name and the current registered hours.

When you clear the table, set a Variable for this and the amount to be cleared

UpdateContext(
   {vHours:
   Lookup(
      colCurrentHours,
      Tutor=YourGallery.Selected.<TutorNameField>,
      TotalHours}
   )
);
UpdateContext({vHours:vHours + <CurentHoursTotal>})

You could probably so this in one step, but the above should work

Then when you patch back to the field, Patch vHours (or whatever you decide to call it) instead of the current total field.

Please click and 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.