Power Automate: Working with Nulls and Empty Lists in SharePoint

In this tutorial, Jonathon Silva from Pragmatic Works walks through how to manage null values and empty lists in SharePoint using Power Automate. These scenarios often cause issues in flows, especially when dealing with incomplete data. This guide explains how to build robust flows that handle these conditions effectively.

Scenario Overview

The example uses an Employee Time Card SharePoint list with three columns:

• Employee Name
• Clock In Time
• Clock Out Time

The goal is to ensure that employees cannot clock in for a new day without clocking out from the previous day. If a clock-out is missing, the flow should send a reminder email instead of creating a new entry.

Step 1: Initial Flow Setup

Jonathon demonstrates a basic flow that:

1. Uses a manual trigger to create a new item in SharePoint.
2. Captures the username and adds the current time using an expression (addHours(utcNow(), 3) for EST).

This works for simple clock-ins but fails when previous entries lack a clock-out time.

Step 2: Checking for Null Values

To handle missing clock-outs, Jonathon adds a Get Items action:

• Retrieve all items from the SharePoint list.
• Apply an OData filter query to return records for the current user: Title eq 'username'
• Order by ID in descending order and limit to 1 item (the most recent entry).

Next, a Condition checks if Clock Out Time equals null. If true, the flow sends an email reminder; if false, it creates a new clock-in entry.

Step 3: Handling Empty Lists

What if the SharePoint list is empty? The previous condition won’t run because there’s nothing to check. To fix this, Jonathon adds another condition using the length() expression:

• length(body('Get_items')?['value']) checks if any items exist.
• If length > 0, proceed with the null check.
• If length = 0, create the first clock-in entry immediately.

Final Flow Logic

1. Condition 1: Does the list have any items?
   • If yes → Apply to Each → Check if Clock Out is null.
   • If no → Create first clock-in entry.
2. Condition 2: Is Clock Out null?
   • If yes → Send reminder email.
   • If no → Create new clock-in entry.

Key Expressions and Actions

• Get Items: Retrieve filtered SharePoint data.
• Filter Query: Title eq 'username'.
• Condition: Check for null using expressions.
• Length: Validate if the list is empty.

Benefits of This Approach

• Prevents duplicate or incomplete entries.
• Ensures data integrity in SharePoint lists.
• Automates reminders for missing actions.

By implementing these checks, you can create resilient Power Automate flows that handle real-world scenarios like missing data and empty lists. This approach is essential for maintaining accurate records and improving workflow efficiency.

Thanks for following along! Stay tuned for more Power Automate and SharePoint tips from Jonathon Silva and Pragmatic Works.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.