cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JC75
Level: Powered On

Running total for Leave records in Sharepoint list

Trying to create an app which allows a user to submit and review leave amounts by type of leave.

 

simplyfiyng what it will do - through power apps a user will submit details of a individuals leave(not their own) Which is stored in a SP list, so this will contain NAME(also a unique staffNo), Location, dateofleave(just one date) and type of leave (2 types) all stored in a SP list

 

multiple submissions for a paricular person may be made overtime, for different dates, so this would look like this in SP

   

 

    NAME. StaffNO   Location       Date.                 Type 

     Joe.        1.           london.     10/11/2017.        LeaveType 1

     Joe.        1.           london.     14/02/2017.        LeaveType 2

     Fred.       2.          london.      12/01/2017.       LeaveType 1

 

I can work out in powerapps how to filter/lookup etc to provide a textbox with a count of Number of leavetypes etc equally for a particular person.

 

But whats the best way of adding this to the sharepoint list, or even a seperate list that keeps a running total of each staff memebers number of Leavetype 1 and leavetype 2? So thats its easier to build other filters datatables etc based on these values, and it easy to see within sharepoint these values also?

 

Ideally I was thinking that coud either be that two addtional columns in sp exist that are TotalLeavetype 1 and totalleavetype2 and some how adding a new entry for someone counts the number of entrys for leavetype 1 associated with that person already and adds 1 and updates that column - but not sure thats best as would it either be then an increasing number for each entry or each row would show the same total value.

 

Or that it writes to two lists the main one just storing the details but the second one only contains a single entry for that memeber and just patches that  value to the LT1 and LT2 columns, however how best to check if an entry already exists or if a new one needs to be created.

 

There will be screens with the app - that will allow a user to see leavetype totals by user, location users at all locations

 

Some suggestions on peoples thoughts on how to best manage this - as feel there a number of approaches - including maybe importing the one list as a collection then using that to write out the values to another or for filters on the screens?

 

 

 

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Running total for Leave records in Sharepoint list

Hi @JC75,

 

Thanks for posting.

If the count is used to calculate the leavetype Running total per person, then it is recommended to create a seperate List to hold this value.

Basically this should be a User List, with unique value configured to User Email or something which could have the user Unique identified. So when creating new records, we could use the User Email to check if it is need to create new record or update the existing one.

For example, if here you would like to use the Form control to deal with the Running Total record, then we could initialize the form control with the code below, followed by the Naviagte function:

If (IsEmpty(filter(table, UserEmail in EmailField)), NewForm(),EditForm());Navigate()

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JC75
Level: Powered On

Re: Running total for Leave records in Sharepoint list

Thank you for the response and helpful siggestions, I shall have a go with your ideas.

 

One question just thinking this through is how when submitting a new leave record - I will have the one list which will contain the actual record and a second list that will just be using your method looking if that user exists already and increasing the total for leave type that was submitted.

 

How best is this handled when submitting to two seperate lists for in essence the same entry?

 

the one list is going to record every actual leave request so will contain mutliple entires for potentially one user, where as the second list just needs to add one extra to the leavetype value to the same user (or create a new line if the user hasnt previously had an entry created) 

 

 

JC75
Level: Powered On

Re: Running total for Leave records in Sharepoint list

I tend to not use forms, as they dont always do as i need.

 

How would the above work using patch function to a list?

 

I want to check list A for if a user exists(using the unique filed of .office365users.Displayname - then patch values to the row(Leave total etc) and then patch to a second list that would allow duplicates and send all the rest of the details to.

 

Regards

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,910)