How to implement an Absence tracking and approval system with SharePoint and PowerApps – Part 2

Here’s the link if you missed the previous post

We’re building an absence tracking system with SharePoint and PowerApps. Last time we laid the groundwork by preparing the team in Teams and we set up the Shared Mailbox.

Let’s look at the SharePoint part today. We’ll place the two SharePoint lists in the Team we prepared. We need to place the two lists within a SharePoint site.

Weird issue with SharePoint permissions settings

When you create a new Team, SharePoint creates a new site for you automatically – it’s the one where the files in the “Files” tab are stored.

For this solution, we’ll need to access two special settings within SharePoint lists – the Item Level permissions checkboxes. With custom-created sites, you can access these settings under “Advanced settings”:

In some cases, the lists in Teams-Created sites don’t allow you to change these two settings. So before continuing, check if you can create a list and see the advanced settings.

If that’s not available for you, create a new site and add the lists there.

SharePoint permission levels

To properly secure the lists, we’ll need to introduce additional Permission levels at the site level. Security levels in SharePoint are like security roles in PowerApps. They define the privileges each user has.

As mentioned, this is defined on Site-level. Here’s how to create a new permission level:

Open the Site Permissions and click the “Permission levels” button. This should allow you to update existing levels and add new ones. We’ll discuss this more separately down with the lists, for now it’s important to remember where to reach them.

The two lists

We need two lists – one for keeping the actual requests and the other for keeping the total available time for each employee.

Let’s create the requests list first.

The requests list

We need the following columns

Field nameField TypeDescription
FeedbackSingle line of textWe’ll use this field to store the manager’s feedback.
First Absence DayDate (Required)This field will be used by employees to submit their absence duration
Last Absence DayDate (Required)Same as before. The naming is crucial here. Initially, I used “First day” and “Last Day” and this caused a lot of confusion as employees didn’t know if this means the last day that they are away or the first day when they were back already in their office.
Duration in DaysCalculatedThe total days of absence the person is taking. This will be a calculated field – I’ll explain it below
Days remainingNumberThe number of days the person has left to spend after this absence. This will be calculated by PowerAutomate.
ManagerPerson or GroupThis will be populated by PowerAutomate as well. Managers are specified in Office 365, this field will just store a copy of this information to cover for cases when a person’s manager changes.
ApprovedYes/NoThe manager’s decision. If approved, this field will be set to Yes, if not yet decided or rejected, the field will be set to No. One drawback of this solution is that we can’t easily distinguish between “Not Decided” and “Rejected”, which might be an issue in your case, but we didn’t need this information, so I didn’t resolve this issue. You could add a simple date field named “Decision Date” if needed, but it’s up to you.
OutlookEventIdSingle line of textThis will store the ID of the event in Outlook. We might need this information later if the user decides to cancel the request.
ApprovalIdSingle line of textThis will store the GUID of the Approval in Dynamics, which we’ll need in case of cancellation.
IsCanceledYes/NoA dirty hack for removing the requests, which I’ll explain shortly.

This is how the list settings page should look like:

Let’s also create a separate view for employees, which will hide the unnecessary fields:

The employees don’t need to be bothered with anything except with these few fields.

Duration in days

This field calculates the number of weekdays between the First and Last absence days. It’s a madness – let me show you:

=IF(ISERROR(DATEDIF([First absence day],[Last absence day],"d")),"",(DATEDIF([First absence day],[Last absence day],"d"))+1-INT(DATEDIF([First absence day],[Last absence day],"d")/7)*2-IF((WEEKDAY([Last absence day])-WEEKDAY([First absence day]))<0,2,0)-IF(OR(AND(WEEKDAY([Last absence day])=7,WEEKDAY([First absence day])=7),AND(WEEKDAY([Last absence day])=1,WEEKDAY([First absence day])=1)),1,0)-IF(AND(WEEKDAY([First absence day])=1,(WEEKDAY([Last absence day])-WEEKDAY([First absence day]))>0),1,0)-IF(AND(NOT(WEEKDAY([First absence day])=7),WEEKDAY([Last absence day])=7),1,0))

OMG!! This is the source of this:

If you want you can check the explained and broken-down version here:

            [First absence day],
            [Last absence day],
           [First absence day],
           [Last absence day],
            [First absence day],
            [Last absence day],
        (WEEKDAY([Last absence day]) - WEEKDAY([First absence day])) < 0,
        2, 0)
               WEEKDAY([Last absence day]) = 7,
               WEEKDAY([First absence day]) = 7
               WEEKDAY([Last absence day]) = 1,
               WEEKDAY([First absence day]) = 1)
            WEEKDAY([First absence day]) = 1,
           (WEEKDAY([Last absence day]) - WEEKDAY([First absence day])) > 0),
                WEEKDAY([First absence day]) = 7
            WEEKDAY([Last absence day]) = 7
  1. IF(ISERROR(DATEDIF(From,To,"d")),"",...)
    • This part checks if there is an error in calculating the date difference using the DATEDIF function. If there is an error, it returns an empty string (“”). Otherwise, it proceeds with the calculation.
  2. (DATEDIF(From,To,"d"))
    • This calculates the number of days between the From and To dates using the DATEDIF function.
  3. +1-INT(DATEDIF(From,To,"d")/7)*2
    • This adjusts the calculated days by adding 1 and subtracting the number of weekends (Saturdays and Sundays) within the date range.
    • INT(DATEDIF(From,To,"d")/7) calculates the number of complete weeks within the date range, and multiplying it by 2 gives the number of weekend days.
    • Adding 1 compensates for the starting day, which may not be a weekend day.
  4. -IF((WEEKDAY(To)-WEEKDAY(From))<0,2,0)
    • This part subtracts additional weekend days if the starting day (From) and ending day (To) are not on the same weekday.
    • If the difference between the weekday numbers of To and From is negative, it means the From day is later in the week than the To day. In that case, it subtracts 2 from the calculated result.
    • For example, if From is a Friday and To is a Monday, it subtracts 2 to exclude the weekend days.
  5. -IF(OR(AND(WEEKDAY(To)=7,WEEKDAY(From)=7),AND(WEEKDAY(To)=1,WEEKDAY(From)=1)),1,0)
    • This subtracts an additional day if both the starting day (From) and ending day (To) are either Saturdays or Sundays.
    • It uses the OR and AND functions to check for these conditions and subtracts 1 from the calculated result if the condition is true.
  6. -IF(AND(WEEKDAY(From)=1,(WEEKDAY(To)-WEEKDAY(From))>0),1,0)
    • This subtracts 1 if the starting day (From) is a Sunday and the difference between the weekday numbers of To and From is positive.
    • It ensures that if the starting day is a Sunday and there are additional weekdays in the date range, it subtracts 1 from the calculated result.
  7. -IF(AND(NOT(WEEKDAY(From)=7),WEEKDAY(To)=7),1,0)
    • This subtracts 1 if the starting day (From) is not a Saturday and the ending day (To) is a Saturday.
    • It ensures that if the starting day is not a Saturday and the ending day is a Saturday, it subtracts 1 from the calculated result.

List security

This list can be accessed by all employees, but they cannot do anything else than Create new requests and read their own requests.

We’ll tackle these requests in two parts – let’s look at how to restrict the employees to only be able to create and view requests. HR Team members is the team the users are assigned to by default. This team has the “LiveSolutions – Employee” custom permission level. The HR Team Owners are the HR Department members. They should also be able to edit and delete all requests.

Let’s review these custom permission levels.

LiveSolutions – Employee is a custom Permission level. Here’s what it can do:

  • Add items – this allows them to add new requests
  • View items – this allows them to read all requests. We’ll restrict this further in just a second.
  • Open item handlers – this allows them to view the list forms, which is what we need.
  • View application pages – same as the previous one, SharePoint is weird.
  • View Pages – In SharePoint, you can create SharePoint pages. We’ll check this out shortly, for now we just need to allow them to view the pages.
  • Open – Allow the users to open the actual list. The previous privilege allowed them to view the item itself, but to be able to access the list itself, they need this one too.

Then we also have the “LiveSolutions – HR Manager” permission level. It has the following privileges:

  • Override list behaviours – this is an important one. This will allow the HR department to see all requests, not just their own. We’ll discuss this shortly.
  • Add items
  • Edit items
  • Delete items
  • View items
  • Browse Directories
  • View Pages
  • Use remote interfaces – this allows the admin user to perform operations (PowerAutomate Flow)
  • Use client integration features – same as before
  • Open
  • Manage personal views

Perfect – we’ve configured the permission levels now!

Let’s check how to configure the list so Employees can only see their own records

Allow employees to only see the records they create

As shown in the beginning, we’ll configure the “Item-level permissions”. Go to List settings and check “Advanced settings”

Here’s how we’ll configure these settings:

  • The users can only read the items, which they created.
  • The users can only create and edit the items which they created themselves. We could set this to “None” as well, but it makes no difference since the users are prohibited from editing the list items.

This is where the “Override list behaviours” permission comes into play. Users with this permission are not bound by these settings and can therefore

Let’s check out the other list now.

Available time ledger

A complicated name for a simple principle.

Each employee needs a row in this list every year. HR Department can use the Grid view to quickly enter all the rows.

The list contains the following fields

Column nameTypeDescription
NotesSingle line of textthe optional notes for each entry. Just for HR’s internal use.
PersonPerson or GroupThis field holds information about who this record is for.
Allocated DaysNumberThe total allocated days for a given person for a given year.
YearNumberThe year this record is for
Remaining daysNumberThe number of remaining days – the automation will subtract from this number. Initially, it should be equal to Allocated Days.

The Person field should only contain users, not groups:


PowerApps forms & SharePoint page

The Requests list has a customised form with PowerApps, but this post is becoming too long so I’ll cover it in a separate post.

Wrapping up

This post is becoming too long. I’ve got a couple more things to show you here, but I’ll write about them in the next post.

We still need to cover:

  • Adding the lists to Teams
  • Customising the form of the requests list
  • The automation itself
  • Cancellations
  • Testing

Whoa… We’ve got enough content for at least three more posts… 🙂

Share this article
Shareable URL
Prev Post

How to implement an Absence tracking and approval system with SharePoint and PowerApps – Part 1

Next Post

SharePoint vs PowerApps forms

Read next