Make A Power Apps Timesheet App – Part 2

This is the 2nd article in a series about how to build your own timesheets app in Power Apps. The first article focused on creating a timesheet lines feature with the ability to add, edit, delete and save lines. This article will continue the tutorial to include the ability to create new timesheets and submit them for approval.
If you haven’t completed Make A Power Apps Timesheet App Part 1 then I would highly encourage you to check it out first.
Table of Contents
• Setup The Timesheets SharePoint List
• Add A LookUp Columns To The Timesheet Lines SharePoint List
• Design The Sidebar Menu
• Create A New Timesheet
• Save The New Timesheet To The SharePoint List
• Submit The Completed Timesheet
• Update The Save Button Code
• Lock The Text Input Fields When Timesheet Is Submitted
• Delete An Existing Timesheet
• Display The Current User's Timesheets In The Sidebar
• Select A Timesheet From The Sidebar
• Timesheets App Completed
Setup The Timesheets SharePoint List
Create a new SharePoint list with the following columns:
- Employee (Person)
- StartDate (Date)
- EndDate (Date)
- TotalHours (Number)
- Status (Choice) [New, Submitted, Accepted, Rejected]
The SharePoint list will look like this once it becomes populated with data.

Add A LookUp Column To The Timesheet Lines SharePoint List
Each timesheet will have one or more associated timesheet lines. Therefore, we must create a relationship between the Timesheet List and the Timesheet Lines. Go to the Timesheet Lines SharePoint list and add a new number type column named TimesheetID.

Design The Sidebar Menu
Add a new label to the SidebarContainer with the text “Timesheets.” Give it the same styling as the “Timesheet” label in the HeaderContainer but make the Color property White.

Give the Timesheets label these property values to give it some padding.
AlignInContainer: AlignInContainer.Stretch
Height: 75
PaddingLeft: 40
PaddingTop: 35
Insert a horizontal container named NewTimesheetContainer into the SidebarContainer.

Place an Add Document icon and a label with the text “New Timesheet” inside the NewTimesheetContainer.

Write this code in the OnSelect property of the icon to set the current timesheet value to blank and clear the deleted time sheet lines collection.
Set(gblTimesheetCurrent, Blank());
Clear(colDeleteTimesheetLines);
Create A New Timesheet
When a user presses the New Timesheet icon a form will appear and ask the user to select the new timesheet’s week. Insert the following 4 controls into the MainSectionContainer.
- Label – with the text “Select Timesheet Week”
- DatePicker
- Label – empty
- Button – with the text “Next”

The highlighted areas will conditionally shows based upon whether the variable gblTimesheetCurrent is blank or is not blank.

Set the Visible property of all controls highlighted in green to this code…
IsBlank(gblTimesheetCurrent)
…and set the Visible property of all controls highlighted in orange to this code.
!IsBlank(gblTimesheetCurrent)
Update the timesheet title label to show the text “Create A New Timesheet” when the variable gblTimesheetCurrent is blank.

Use this code in the Text property of the title label. When the variable gblTimesheetCurrent is not blank display the selected timesheet’s
If(
IsBlank(gblTimesheetCurrent),
"Create A New Timesheet",
$"Timesheet: {Text(
gblTimesheetCurrent.StartDate,
"mmmm d"
)} - {Text(
gblTimesheetCurrent.EndDate,
"mmmm d, yyyy"
)}"
)
Save The New Timesheet To The SharePoint List
Once the user clicks the next button the timesheet is saved to SharePoint and they can begin to enter their time worked.

Write this code in the OnSelect property of the button. The Employee field is populated with a person type value. StartDate and EndDate are set to the Sunday and Saturday of the selected week. Status is New and TotalHours are 0.
// create a new timesheet and store the result
Set(
gblTimesheetCurrent,
Patch(
Timesheets,
Defaults(Timesheets),
{
// set timesheet employee to current user
Employee: {
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: $"i:0#.f|membership|{User().Email}",
Department: "",
DisplayName: User().FullName,
Email: User().Email,
JobTitle: "",
Picture: ""
},
// set start date to Sunday of the selected week
StartDate: DateAdd(dte_TimesheetWeek.SelectedDate,1-Weekday(dte_TimesheetWeek.SelectedDate,StartOfWeek.Sunday),TimeUnit.Days),
// set end date to Saturday of the selected week
EndDate: DateAdd(dte_TimesheetWeek.SelectedDate,1-Weekday(dte_TimesheetWeek.SelectedDate,StartOfWeek.Sunday)+6,TimeUnit.Days),
Status: {Value: "New"},
TotalHours: 0
}
)
);
// create the first timesheet line
ClearCollect(
colTimesheetLines,
Patch(
'Timesheet Lines',
Defaults('Timesheet Lines'),
{TimesheetID: gblTimesheetCurrent.ID}
)
);
// reset date picker to blank
Reset(dte_TimesheetWeek)
After the Next button is pressed the RightContainer updates to look like this.

Update The New Timesheet Line Code
We must update the new line icon code to apply the Timesheet ID.

Replace any code in the OnSelect property of the new line icon with this code.
Patch(
colTimesheetLines,
Defaults('Timesheet Lines'),
{TimesheetID: gblTimesheetCurrent.ID}
)
Submit The Completed Timesheet
The save button writes timesheet data back to the SharePoint list but it does not update the timesheet status to “Submitted”. To do this, insert a new Send icon into the ActionsContainer and label with the text “Submit.”

Write this code in the OnSelect property of the Submit icon. The final line of code uses the the Select function to run the code found in the OnSelect property of the Save icon. We will update that code next.
// update the timesheet status to submitted
Set(
gblTimesheetCurrent,
Patch(
Timesheets,
gblTimesheetCurrent,
{Status: {Value: "Submitted"}}
)
);
// save the timesheet lines
Select(ico_Save);
Update The Save Button Code
The Save button must now perform one additional action to update the timesheet’s total hours.

Add the new code below to the OnSelect property of the Save icon.
// collection to update timesheet lines
ClearCollect(
colUpdateTimesheetLines,
ForAll(
gal_TimesheetLines.AllItems,
'Timesheet Lines'@{
ID: ID,
TimesheetID: gblTimesheetCurrent.ID, // <----- NEW CODE
PayCode: drp_PayCode.Selected.Value,
Sunday: Value(txt_Sunday.Text),
Monday: Value(txt_Monday.Text),
Tuesday: Value(txt_Tuesday.Text),
Wednesday: Value(txt_Wednesday.Text),
Thursday: Value(txt_Thursday.Text),
Friday: Value(txt_Friday.Text),
Saturday: Value(txt_Saturday.Text)
}
)
);
// update timesheet lines and store the results
ClearCollect(
colTimesheetLines,
Patch(
'Timesheet Lines',
gal_TimesheetLines.AllItems,
colUpdateTimesheetLines
)
);
// delete timesheet lines
ForAll(
colDeleteTimesheetLines,
Remove(
'Timesheet Lines',
ThisRecord
)
);
Clear(colDeleteTimesheetLines);
// ***NEW CODE***
// update total hours on timesheet record
Set(
gblTimesheetCurrent,
Patch(
Timesheets,
gblTimesheetCurrent,
{
TotalHours: Sum(
colUpdateTimesheetLines,
Sunday
) + Sum(
colUpdateTimesheetLines,
Monday
) + Sum(
colUpdateTimesheetLines,
Tuesday
) + Sum(
colUpdateTimesheetLines,
Wednesday
) + Sum(
colUpdateTimesheetLines,
Thursday
) + Sum(
colUpdateTimesheetLines,
Friday
) + Sum(
colUpdateTimesheetLines,
Saturday
)
}
)
);
Lock The Text Input Fields When Timesheet Is Submitted
The user must not be able to change the timesheet lines once the timesheet is submitted.

Use this code in the DisplayMode property of the timesheet lines pay code dropdown menu and all of the text input fields.
If(
gblTimesheetCurrent.Status.Value="New",
DisplayMode.Edit,
DisplayMode.Disabled
)
Hide the ability to delete timesheet lines by setting the Visible property of the Trash icon to this code.
gblTimesheetCurrent.Status.Value="New"
A submitted timesheet should look like this.

Delete An Existing Timesheet
We must add one more ability to the ActionsContainer to delete a timesheet. Insert a Trash icon and a label with the word “Delete” into the container.

Write this code in the OnSelect property of the Trash icon.
ForAll(
gal_TimesheetLines.AllItems,
Remove(
'Timesheet Lines',
{ID: ThisRecord.ID}
)
);
Clear(colDeleteTimesheetLines);
Remove(
Timesheets,
gblTimesheetCurrent
);
Set(
gblTimesheetCurrent,
Blank()
)
Display The Current User’s Timesheets In The Sidebar
New timesheets will appear in the sidebar menu as they are created. The user can change to a different timesheet by clicking on it. Add a blank vertical gallery to the SidebarContainer.

Set the Flexible Height property of the gallery to true.

Use this code in the Items property of the gallery to show only the current user’s timesheets.
Sort(
Filter(
Timesheets,
Employee.Email = User().Email
),
StartDate,
SortOrder.Descending
)
Insert two labels into the gallery to display the timesheet start & end date and status,

Use this code in the Text property of the date range label.
$"{Text(
ThisItem.StartDate,
"mmm d"
)} - {Text(
ThisItem.EndDate,
"mmm d"
)}"
Write this code in the Text property of the status label.
ThisItem.Value.Status
Select A Timesheet From The Sidebar
When a user selects a timesheet from the sidebar it should appear in the RightContainer. Add a button to the gallery and make it fill the entire gallery row. Remove any text found in the button.

Use this code in the OnSelect property of the button to select the current timesheet and load its timesheet lines.
Set(
gblTimesheetCurrent,
ThisItem
);
ClearCollect(
colTimesheetLines,
Filter(
'Timesheet Lines',
TimesheetID = gblTimesheetCurrent.ID
)
);
Clear(colDeleteTimesheetLines)
Make the button Fill property transparent when the timesheet is not selected and show a slightly transparent white color when the timesheet is selected.
If(
ThisItem.ID=gblTimesheetCurrent.ID,
RGBA(255, 255, 255, 0.15),
Color.Transparent
)
Also update the HoverFill and PressedFill properties to this code.
RGBA(255, 255, 255, 0.15)
Timesheets App Completed
The timesheets app is now completed. Add any additional features that are required then deploy it to the organization.

Did You Enjoy This Article? 😺
Subscribe to get new Power Apps articles sent to your inbox each week for FREE
Questions?
If you have any questions or feedback about Make A Power Apps Timesheet App – Part 2 please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.
Very inspiring. We will be working on an application similar to this internally. This will be a very good start. Thank you very much Matthew!
Anh,
Good luck 🙂
Hi Matthew, thank you for the tutorial; l keep getting ‘TimesheetID’ doesn’t exist. on this code
// create the first timesheet line
ClearCollect(
colTimesheetLines,
Patch(
‘Timesheet Lines’,
Defaults(‘Timesheet Lines’),
{TimesheetID: gblTimesheetCurrent.ID}
)
);
Thank you
Eldon,
Good point. I’ve added a section to my tutorial called “Add A LookUp Column To The Timesheet Lines SharePoint List.” Do this step and it will fix the problem.
very nice,
starting from
If(
IsBlank(gblTimesheetCurrent),
“Create A New Timesheet”,
$”Timesheet: {Text(
gblTimesheetCurrent.StartDate,
“mmmm d”
)} – {Text(
gblTimesheetCurrent.EndDate,
“mmmm d, yyyy”
)}”
)
i start getting errors did i miss somthing not mentiond in the blog ?
Also:
// create the first timesheet line
ClearCollect(
colTimesheetLines,
Patch(
‘Timesheet Lines’,
Defaults(‘Timesheet Lines’),
{TimesheetID: gblTimesheetCurrent.ID}
)
);
hi Mathew
its works ok now i hade to remove
{TimesheetID: gblTimesheetCurrent.ID}
but if u have explination that will be nice
hi Matthew Devaney
u can delete my oyther comments its woreks nice and now i understand the logic thank you that was so inspiring
Hello Matthew,
thank you for this i cant wait for part3,
i have question how to display the selected week if it have multi lines ? beaucse right now its only display 1 line (with the id) but if we add more then line in same week its sum them but not display them in Timesheets only 1 line ..
2nd question
i did add timer to it but how i can take the value of the timer and add it for one of the days in that case we have 7-5 days
thank you again
Prof,
I do not plan to write a part 3… can you please rephrase you question? I do not understand it.
when we insert new line they all get same timesheetID how can we show them ( 2 lines or more ) after submitting the timesheet.
and my 2nd question is :
is it possible to add Timer control value to one of them text inputs
Hi Matthew! Another master piece. Congratulations.
Please, take a note that “TimesheetID” doesn’t exist in SharePoint list ‘Timesheet Lines’.
Review Part 1 ?
Hello, I am having issue with the “Save” and “Next” buttons, codes are not working and giving the red errors, I am new to powerapps, thank you
Mr. Young,
Please tell me what error message are the Save and Next buttons giving? Attaching a screenshot will also help.
Hi Matthew, I appreciate your reply, everything worked out well, I had an error in the code at my end, your code worked perfectly. Thank you so much for the knowledge sharing and hope to see more coming!! 😀
M. Young,
I’m glad to hear it. You’re welcome 🙂
Dear
Thank you for making these posts!
I followed the instructions as per the post & reached almost at the app completion. However, I am getting “This formula uses scope, which is not presently supported for evaluation” Error on lbl_StatusTimesheets control. Please see the attached screenshot
Is this something you can assist?
Thaks
Tere,
You code shows: ThisItem.Value.Status
I think it should be this instead: ThisItem.Status.Value
Hi Matthew
I changed the code as per your suggestion & its ok now
Thanks you so much!!