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.

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.