Make A Power Apps Timesheet App - Part 2

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/make-a-power-apps-timesheet-app-part-2/. Author: Matthew Devaney.

Make A Power Apps Timesheet App – Part 2 Posted by - Matthew Devaney on - April 16, 2023 66 Comments

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 Code language: HTTP ( http )

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); Code language: JavaScript ( javascript ) 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 " )}" ) Code language: PHP ( php )

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) Code language: JavaScript ( javascript )

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} ) Code language: JavaScript ( javascript ) 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); Code language: JavaScript ( javascript ) 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 ) } ) ); Code language: JavaScript ( javascript )

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 ) Code language: JavaScript ( javascript )

Hide the ability to delete timesheet lines by setting the Visible property of the Trash icon to this code. gblTimesheetCurrent.Status.Value= "New" Code language: JavaScript ( javascript )

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() ) Code language: JavaScript ( javascript )

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 " )}" Code language: PHP ( php )

Write this code in the Text property of the status label. ThisItem .Value .Status Code language: CSS ( css ) 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) Code language: JavaScript ( javascript )

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 ) Code language: CSS ( css ) 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 Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up 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. Matthew Devaney M365 Copilot Power Apps

How To Create Copilot Custom UI Widgets In Power Apps