Not sure what to do if your Get Items request returns a NULL value?
SharePoint lists are excellent tools for lightweight data entry storage. However, a SharePoint list is not a database. Key constraints and referential integrity cannot be enforced. When using Power Automate to join values in two different lists using the Get Items Action, it is common to find no corresponding value. If not handled correctly, the flow terminates. This post shows you a simple method to handle a NULL value returned from a SharePoint Get Items action in Power Automate.
The Problem
On a recent project, our customer ran into this exact issue and requested assistance. In this instance, the Power Automate Flow attempted to lookup a leave request using employee name for a specific date range. Not every employee will have a leave request during the specified date range. So, there will be instances where a NULL value is returned from the SharePoint Get Items Action in Power Automate. As described in the introduction, this was causing the flow to terminate prematurely.
The Solution
One method to gracefully handle this scenario is to test the length of the SharePoint Get Items Action in Power Automate using a Power Automate Condition. The SharePoint Get Items request and Condition test in Power Automate is shown below (identifiable fields are obfuscated for security purposes):
If we dissect this solution, the key element occurs in the “Condition 3” step. The “length” function is used to test the value returned from the “Get Request Leave” SharePoint Get Items action. The full expression is shown below:
length(body(‘Get_Request_Leave’)?[‘value’])
If this value is greater than 0, there was a match. The “yes” and “no” branches underneath the Condition can now for further actions with no fear of Power Automate terminating prematurely. Applying this simple approach will save you time and increase your success rate in Power Automate when joining values from multiple SharePoint Lists in Power Automate.
Give your business the strategic edge it needs! Create a data-driven culture throughout your organization and get reliable insights from your data without building infrastructure, buying the tools or hiring a team. Learn more.
IronEdge Group Recognized on CRN’s 2024 MSP 500 List
Houston, Texas, February 12, 2024 — IronEdge Group is honored…
