Can't get the Dates for Files I have selected

[MattPackwood]

I am trying to figure a way to do this...

I have a Flow that checks a folder in SharePoint, it identifies the files that have not been updated in 21 days and send me an email of them (see the screen shot).

But I can't figure how to include the Last Modified date.

I know that the top of the flow specifies what objects (files) are in the folder and sub folder:

Then I create a string variable to hold the list I will create

Then I enter a "for each" that will iterate though "the list of all the files"

A conditional to see if their modification date is within the criteria (is "lastModified < (todays date -21 days)).

Ok. so now I can take the filenames of the files that match this, and concatenate to the string variable I created.

...

So that works...

But if I try to output the last Modified Date, I get "0001-01-01T00:00:00" for every one...

Clearly, I am missing something...

[creativeopinion]

@MattPackwood You might find this YT Tutorial helpful: How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks

Are you easily stumped when working with Dates in Power Automate? In this Power Automate tutorial, I’ll show you how to compose an expression that will return a future date, a past date, how to count the number of days between two dates, how to check for a birthdate and anniversary date as well as tips and tricks when working with dates in Power Automate.

I’ll cover some common use cases and concepts that can help you to build better Power Automate flows. Feel free to skip ahead using the timestamps listed below. I’ve also linked a few other tutorials that you might be interested in as well.

IN THIS VIDEO:

✅ 4 Date Functions You Need to Know

✅ How to use the Convert Time Zone Action

✅ How to Get a Future Date

✅ How to Get a Past Date

✅ How to Return SharePoint Items Due in a Number of Days

✅ How to Return SharePoint Items Due within a Date Range

✅ How to write a Filter Query for SharePoint Items Due in 30, 60 and 90 Days

✅ How to Calculate the Number of Days between Two Dates

✅ How to Check for a Birthdate and Anniversary Date

✅ How to work with Dates and Times in Excel

✅ How to use a Manual Trigger with Date Input

✅ How to Output Dynamic Text with an Expression

✅ How to Check if a Date Falls on a Weekend

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

[MattPackwood]

Anybody got any ideas?

[creativeopinion]

@MattPackwood 

Get Date 21 Days Ago

In this section of my YT Tutorial: How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks

I cover how to get a past date. Add a Compose action to your flow to store an expression.

formatDateTime(addDays(utcNow(), -21), 'yyyy-MM-ddT00:00:00Z')

This expression will return the date 21 days ago, specifying 12AM.

Get Files

If you want your flow to check files in a specific folder, you need to use the Get files (properties only) action. Select your site address and library name. You can search the entire doc library if you'd like, if you want to specify it to a specific folder, select a folder.

The name of this action is misleading as it will return files and folders. If you want to only return files, you need to specify a filter query that will only return files:

FSObjType eq 0 

Return Count

Whenever I use a Filter Query in a Get files (properties only) action, I always like to return the count of files/folders 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 Files (properties only) action into the length() function.

Run a test. Review the outputs of the Compose action and confirm the number of files being returned.

Add a Condition Check

Add a Condition action to your flow. If items have returned (aka there is number stored in the Compose action—that is not equal to 0), add the rest of your actions to the Yes branch. If not, do nothing.

Output HTML Table

he easiest way to output results from an action that outputs an array of items such as the Get Items action is to use the Create HTML Table action.

Add this to the Yes branch of your condition.

If you are using a date column in your output, you can learn how to format dates in this YT Tutorial: How to Work with 📆 Dates in Power Automate | Example Scenarios and Tips & Tricks

Run a test and review the outputs in your HTML table.

Insert the output of this action into your Send an Email (V2) action. 

-----

In the meantime, you should check out the YT Tutorial below to get a better idea of the logic behind sending a single email with multiple items as you'll need to understand that logic for your flow. Although the tutorial covers how to send an email of SP list items, the concepts covered in the video are the same.

If you are looking for a way to send multiple SharePoint List items that have been assigned to a user in an email—check out this YT Tutorial: How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

IN THIS VIDEO:

✅ How to send multiple list items in a single email with a Power Automate Flow

✅ How to create a dynamic date range

✅ How to use the Convert Time Zone action

✅ How to use a Filter Query in the Get Items action

✅ How to count number of items in an array

✅ How to use the Select action to extract a users display name and email address

✅ How to create a unique list of email addresses

✅ How to use the Create HTML Table action

✅ How to customize the HTML Table with CSS styles

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

✅ How to use the Append to String Variable action

✅ How to create a custom list of items for an email

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

✅ How to display singular or plural text based on the number of items returned

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

[MattPackwood]

OK, that makes sense so far, but I have a problem, I can't make the filter for files older than 21 days "work", if I put anything other that "FSObjType eq 0" in the filter box I get an error.

I see how to use the "FSObjType eq 0"

I get the "and"

But what I can't figure out is how to enter the "Modified It OutPut"

I can use the Magic Variable for the OutPut, but I don't know / see / can't figure out what the "Modified It" element is.  I believe it is a test that looks at the Current Date and the Date from "formatDateTime(addDays(utcNow(), -21), 'yyyy-MM-ddT00:00:00Z')", running this today the first Compose gives me the date 21 days ago (2024-04-21T00:00:00Z).

So I am thinking this is a function that will accept the input from the Compose (21 days ago), the last mod date and if A>B return True (the date is less than the date 21 days ago".

If I try typing in the function the Flow will save, it passes the Flow Checker but then errors out when running.

I can type in the "Modified It the" the single ', and pick the "Compose 21 days / OutPut" and the final '.

This is the error I get....

[creativeopinion]

@MattPackwood The operator you are using is incorrect. It should be lt (lowercase L = less than). If you'd like it to be less than or equals to, use le. 

[MattPackwood]

SORRY - Profoundly sorry!

I missed that it was an arithmetic comparision not a function.

Anyway, I am now at:

How do I compose the "List" of Files into something in an email that shows filenames and dates of last change?

[creativeopinion]

@MattPackwood Can you confirm that the Compose action is outputting the correct number of files you are expecting?

[MattPackwood]

Yes, that works!

[creativeopinion]

@MattPackwood I've updated the original post with additional instructions. The easiest way to get your data out would be using the Create HTML Table action. Hope that suits your requirements.