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

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: https://sharepoint.stackexchange.com/a/197828

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

=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
    )
)

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

The Person field should only contain users, not groups:

Security

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… 🙂