Filtering on Column Name

[Andrew_J]

Hi All,

I have a SP List it has a date column "Date today UK" the maths is:

Date today UK	Subtract	Result Uk
21/03/2024	93 Days	19/12/2023

It starts off:

The Int Var = formatDateTime(getPastTime(3,'Months'), 'yyyy-MM-dd')
Compose = formatDateTime(addDays(utcNow(), -93), 'yyyy-MM-dd')

I need to filter for multiple dates some of which have NUL values but also refrence the "Date today UK" column.  But every time I do the I get nothing back.

I know what I am doing it not correct but I should be able to use the "Date today UK" data with the variable and get a match to fire the next part of the process.  I am hoping to get a single result today and I think the filter to be in APPLY TO EACH. 

How do I resolve this please.

Andrew

[creativeopinion · Accepted solution]

@Andrew_J This is my demo list. I've removed the Date from a few of items—I have a total of 8 items in my list.

Filter Out Items without a Date

First, add a Filter Query to filter out those items without a date. You will need to use the internal column name of your Date column. Keep in mind that the internal column name may not always match the name displayed in your SP list. If you aren't sure how to get the internal column name, you can refer to this section of one of my YT Tutorials.

Return a Count of Items

Whenever I use a Filter Query in a Get Items action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

Run a test.

Compose a Dynamic Date

Whenever you are using dates and times in your flow—it's best practice to use a Convert Time Zone action. In the base time field, insert the utcNow() expression.

Source Time Zone: Coordinated Universal Time

Destination Time Zone: Local Time Zone

Format String: Round Trip

Use the output of the Convert Time Zone action in the addDays() function. 

Try to match the date format in your expression to that being returned from your SP List. 

Adjust your Filter Query. 

You might be interested in this YT Tutorial: Send Emails Based on a 📆 Date Column in SharePoint with Microsoft Power Automate

In this Microsoft Power Automate tutorial, I’ll show you how to build a flow that will send a Happy Birthday email to a user based on a date column in a SharePoint list. The SharePoint list also contains a column with a Manager’s name which we’ll use to send a three-day and day of reminder to the user’s manager.

This automation will use the Filter Array action to filter out all SharePoint list items where the user’s birthday is today or in three days. This flow can apply to a variety of scenarios such as:

📅 Student Birthdays

📅 Project Due Dates

📅 Contract/Membership Renewals

📅 License Expirations

📅 Client Anniversaries

---

IN THIS VIDEO:

✅ How to Send an Email based on a Date Column in SharePoint

✅ Using the Recurrence Trigger in Power Automate

✅How to Use the Filter Array Action with multiple conditions

✅ How to Get Dynamic Content from a Filter Array Action

✅How to Get a Date Three Days from Today

✅ How to Create a Dynamic Date Based on utcNow()

✅ How to Return a Count of Items

✅ How to initialize and set a variable

✅ How to use the Send an Email (V2) action

✅ How to send test emails

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[Andrew_J]

@creativeopinion 

That worked a treat although I used +93 days istead of the -93 days.  That meant  Icould be checking dates in the future as I wanted to be ahead of the game. 

As for the filter all worked well once I got the above sorted in my mind and and then added a select and HTML table generation to then send an email.

Thanks.

Regards,

Andrew