Electronically Signed Weekly Timesheet Template Made Easy

Esign weekly timesheet template

hey everyone april dunham here in this template tuesday video i'm going to share my timesheet template we'll learn about the forall function and how to do repeating tables in powerapps but first here's the intro [Music] [Music] as usual let's walk through the app first and see how it functions when we first open the app we're taken to our landing screen where we have a welcome message it shows the current date and our current weekly time we can show our high the left nav by clicking the hamburger icon and let's walk through the new timesheet submission process first we'll select the start weekday that we want to do this timesheet for then we're taken to the time card screen where most of the work happens so we see a summary of the week period that we're submitting this for the employee name which is the current logged in user and then we have the weekdays broken out so that we can enter in hours for each day of the week there's a bill to drop down combo box where you can tag a time card entry to a specific project client cost center and there's a comments field as well so as i enter in the time for what i've worked this week if i want to tag time for multiple clients or projects i can just click the plus button a new line is added into here i can change this client and enter in time keep clicking that plus button to add new rows to this week's timesheet the very quickbooks-esque type timesheet if i decide i don't need one of these i can just click the trash can and that goes away i have a save and a submit for approval so that if i don't want to submit this for approval yet i just want to save what i've done i can click the save icon so that's written out into our data source in a pending status so i know that it's not ready to be saved quite yet and i can go back in via the my timesheets screen select the week period that i want to edit and it's going to get all of the time entries for that current week period that i have pending then once i'm done i could submit that for approval and once it's submitted for approval if i'm an administrator i can go into the approval section and there i'll see the individual time entries that i need to approve you can see a summary of who it's for the time period and the bill to information i can click on that and approve or reject the individual time entries in that screen the approval screen is only showing the time entries that have not been approved or rejected so you see as i approve that one it fell off of this list in the template itself i'm not doing anything to restrict the approval section you can build in your own security trimming for that though i have videos on how to do that the main things that i wanted to show how to do within this template is how we handled that repeating table functionality in the timesheet itself and how we're submitting that with the forall function so let's go to the app in edit mode the weak screen is using the calendar component that you might have noticed i've used in several of these templates it allows us to select a starting weekday and when we click the next button we're outputting that weak start to a variable so that we can use that later on and this is the first piece of the puzzle on how we're doing that repeating section so when we're submitting a new timesheet entry the first step of the puzzle is to create a new collection we're doing that with the clear collect function that you see we're creating a collection called time entries collection and we're filling in some values so we need to track the employee bill to monday through sunday times and comments so we're creating a new record in that that's essentially a blank record so that we can fill that in then we're navigating to our new time card screen so now if we go to the new screen you'll notice this is just a gallery and within your galleries we know that we can use them to display static information but you can also use galleries to display information that you need to input so you can put text boxes and drop downs within these galleries and have editable items which is what we're doing here for the bill 2 we're using a combo box and we're pointing it to a sharepoint list that's storing the bill to client information the rest of these are text boxes that we've mapped to our collection item so the default value for monday is going to map to the monday field and so on and so forth how we're handling making this a repeating table is we've added within the gallery a icon and in that icon if we look at its on select property you see that we are patching back to that local collection so the first thing we're doing when we click that plus icon is we want to save the record that we're currently in because we will have went in and updated the information because you notice when we click the next button on that calendar screen we created an empty record so now that we've filled in that information we're going to patch that empty record and overwrite it with the information we just entered when you do a patch you point it to the name of your collection and for the item that you want to patch type in this item and that will get the current item that you're in so it knows to patch that one then all you need to do is map your individual fields to the values in the gallery so you'll notice what i'm doing for bill 2 i'm mapping it to our combobox bill2.selected.value and so on and so forth now monday through sunday those are all number values in my data source so that's why i'm using the value function and wrapping in my text values from the text boxes in that so that it's properly formatted as a number after we update that record we want to add a new line so that we can enter in a new record so that's what we're doing down here with the collect we're collecting on that same time entries collection and we're adding in a new blank item so we can keep repeating that process we added a new blank item we would fill in that record we click plus again it would update what we just did and then add a new row the delete's really simple it's just using the remove function with remove you type it in the name of the collection that you want to remove from and then pass it in the record so if we're using this within the context of a gallery just point it to the collection that your gallery is pointed to and use the this item function to get the current item you could add some additional logic here to kind of error proof it to pop up a dialog box to confirm if you want to delete this i do have a video on how to do that on my youtube that i'll put a reference to in the video another thing that i'll point out what we're doing is on these header labels for the days of the week you'll notice that we had the actual weekday so if it was the 20th of july for example through the 27th and then also the name of the day i'm using the text function and i'm passing it in the week start column from our data source which is going to get the monday starting day of the week and i'm formatting that so that i can get just the day from that date value you can do that with this formula here just use the enus dd and that will extract the date from a date time value another thing you'll notice that you might not have seen before is i'm using this char in parentheses 10. that's a way that we can add in in our powerapps text values a line break so if we put that in there you notice that we had now the date value and then the date itself on two separate lines and then i'm just hard coding whether it's monday tuesday etc now let's talk about the for all so if we look for example at the submit now the submit and the save are actually the exact same the only difference is the status that they're outputting so we'll just take a look at the submit now we want to go through when we submit this and get every record that we have in this gallery and write that out to our data source so to do that we can use a function called for all with for all you pass it in a collection or data source and it will go and essentially loop through that data source and you can have it do something like what i'm doing here in performing a patch i'm pointing the for all to the gallery itself so i can point that to gallery timecard.all items to get all the items within that and then i can say do a patch i want to patch directly to my data source which is a sharepoint list called time entries and i want to do defaults time entries so that i can let it know that this is a new record that i want to add and then we'll do the same mapping that we saw earlier with our collection we'll take our field names from our list pass it in the values and you'll notice we're doing the same thing we're taking the values from the text boxes themselves within our gallery and getting that value i'm doing some math here i have a total column in my list so i want to take all the monday through sunday values and add those up so you can do that in line directly in powerapps and that's for the initial submission now let's take a look at what if we had went in and we saved without submitting it because we need to come back in later so for example if i go to my time sheets and look at this week i never submitted this for approval yet so i've went into an existing record this is a different screen in the app if we look at its submit for approval on select you'll see it's a little bit different because we need to account for if the item in the list is new or not because you could come back in here to an existing time period and add more into here so maybe i only entered my time for client x i decide i need to add more time for client y so i need a way to distinguish what i've already had in there that's already in my data source versus what i just added here that's not there yet so how i'm doing that is i'm using that same for all pointing it again to the all items on my gallery but within that i'm using an if statement and i'm checking if in this current item that it's in the loop in if it's id is blank then i know if there's a no id that there's no record in my data source therefore it needs to be a fresh new patch but if there is an id that's not blank then i know it was already there so i need to overwrite what was in there with what i have on the screen so that's what this if does so this patch is the exact same just using the defaults for a new one mapping all the fields to the text inputs in my gallery and then a second patch if it is an existing record i'm going to instead of using the defaults i'm going to take the id and compare them so compare the id in the list to the current id of the record that i'm in in the loop that's pretty much it the save is the exact same like i said except the difference being the status so when it's just saving the status is pending but when you're submitting it for approval the status is submitted for approval and that determines if it falls into the card approval screen queue this is a great starter template there's lots of ways that you can expand on it like i said you could add some security trimming into it you could easily change it to where you could enter in time for example for multiple employees at once instead of just for yourself it's meant to be a great starter template for you to get started with doing time sheets and lots of little nuggets and things that you can learn from it but i just wanted to highlight the main things in this video being the repeating tables and working with the for all function as always i'll post this template on github for you to download and use there'll be a flow that you can run that will set up the underlying sharepoint list structure and if you have any ideas for other templates that you'd like to see me build please drop me a note in the comments and let me know and i'll be happy to work that in to one of my future template tuesday videos i hope you found this helpful if you did please like and subscribe and i'll catch you in the next video