Get files (properties only) filter query by specific date

(0) Share

Report

Posted on by slvedva

176

Hi,

I have request via power automate get file properties only from Sharepoint, but I should filter out specific file.

From get files properties only I can get file created date, BUT I need filter out only files, which have current date = target date, but target date = file create date + 5 working days.

In Sharepoint I have list with files, which created in 14/09, 15/09, 18/09, 19/09, 20/09 etc., Today = 22/09, that mean with function get files only properties I should receive ONLY files, which created in date 15/09.

File create date = 15/09 + 5 working days = 22/09 = current date

How to set-up this via Filter Query?

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (1)

Verified answer

ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (1)

Report
Hi @slvedva

To get all the files which are created 5 days ago, you could use range query on Created column:

Created ge '@{startOfDay(addDays(utcNow(),-5))}' and Created lt '@{startOfDay(addDays(utcNow(),-4))}'

Edited:

To get the files which are created 5 days ago considering only working days then we need to first traverse back to the 5th working day from today. We need to consider the case for Saturday & Sunday (weekends) where we need to subtract 5 & 6 days respectively to get the past 5th working date. For all other days, we need to subtract 7 days to get the required date. We will collect the date using expression in compose action and then use the range query in the "Get files" action.

if(equals(dayOfWeek(utcNow()),0),startOfDay(addDays(utcNow(),-6)),if(equals(dayOfWeek(utcNow()),6),startOfDay(addDays(utcNow(),-5)),startOfDay(addDays(utcNow(),-7))))

Next, we will add one day to the output of compose action to get the next date which we will use in the range filter query:

Created ge '@{outputs('Compose')}' and Created lt '@{startOfDay(addDays(outputs('Compose'),1))}'

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
slvedva 176 on at

Like (0)

Report

Hi @ManishSolanki
A little bit confusing. In test example I have files created 2023-09-12, I added compose which in output give 2023-09-18, but when I set up Filter query, the output give Created ge '2023-09-18T00:00:00.0000000Z' and Created lt '2023-09-19T00:00:00.0000000Z'. Today 2023-09-23 of course system filter out 0 files, which is correct. But if I run this flow on 2023-09-18, does flow will filter out any file, if Created ge 2023-09-18???

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report

Hi @slvedva

As Created is a system field that also stores timestamp, so that is why we are using range query (greater than and less than). In filter query syntax, ge means "greater than or equals to" and lt means "less than" (not inclusive).

Using "Created ge '2023-09-18T00:00:00.0000000Z' and Created lt '2023-09-19T00:00:00.0000000Z'" filter query, get items will fetch all those files which are created between 12:00 am (18-09-2023) and 12:00 am of next day (19-09-2023).

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
slvedva 176 on at

Like (0)

Report

But I don't need filter out files, which are created between 18-09 and 19-09. If I run flow 18-09, I need filter out files, which are created between 12-09 and 13-09.
And if I run flow in 19-09, than I need filter files, which are created between 13-09 and 14-09 etc.

Was this reply helpful? Yes No
ManishSolanki 15,091 Super User 2025 Season 2 on at

Like (0)

Report
Hi @slvedva

In that case, we can add an input parameter in the trigger to input the date and accordingly the flow will get the files for past 5 days (working days).

1. In the manual trigger flow, we will add a date parameter 'Input Date' to input the date from which past 5 days items will be returned:

2. Next, add compose action and using expression, we will calculate the past 5th working date:

Expression needs to be entered in the expression box as highlighted in the above screenshot:

if(equals(dayOfWeek(triggerBody()['date']),0),startOfDay(addDays(triggerBody()['date'],-5)),if(equals(dayOfWeek(triggerBody()['date']),6),startOfDay(addDays(triggerBody()['date'],-4)),startOfDay(addDays(triggerBody()['date'],-6))))

3. Finally, pass the filter query to Get files action to get the desired list items:

Created ge '@{outputs('Compose')}' and Created lt '@{startOfDay(addDays(outputs('Compose'),1))}'

Flow run result:

I tried passing 18 Set as input date and the flow is giving correct results:

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

Was this reply helpful? Yes No
slvedva 176 on at

Like (0)

Report

Hi @ManishSolanki

Maybe I need more detail describe situation.
This flow I plan schedule automatically every working day and when flow run, system should check SharePoint files create date and 5 working days after file creation system will send out reminder, that mean when get files, it should filter files 5 working days after creation in other days don't filter at all.
Try describe example:
I have files created in 12-09-2023
When flow run 12-09-2023 and get files, system don't filter anything;
when flow run 13-09-2023 and get files, system don't filer anything;
when flow run 15-09-2023 and get files, system don't filer anything;
when flow run 18-09-2023 and get files, system filter all created files;
when flow run 19-09-2023 and get files, system don't filer anything;
when flow run 20-09-2023 and get files, system don't filer anything etc;

Was this reply helpful? Yes No
slvedva 176 on at

Like (0)

Report

Hi @Matthy79
Maybe you can help with my issue? Thanks.

Was this reply helpful? Yes No
Matthy79 4,180 Super User 2024 Season 1 on at

Like (0)

Report

Honestly I don’t understand the explanation. Let me explain the problem I have. Tried to add comments in red.

In your example the files are created 12-09-2023 (Tuesday)

When flow run 12-09-2023 and get files it is the same day so no working day in between;

when flow run 13-09-2023 (Wednesday) and get files it is the next working day (day 1);

Why do you leave out 14-09-2023 (Thursday). This would be day 2

when flow run 15-09-2023 (Friday) and get files it is day 3;

16 and 17 are Saturday and Sunday (no working days so no count)

when flow run 18-09-2023 and get files, system filter all created files. I don’t under why this is day is day 5 for you. For me it is working day 4

when flow run 19-09-2023 and get files, system don't filer anything; (for me this would be the 5th working day)

when flow run 20-09-2023 and get files, system don't filer anything etc;

Also a big problem with working days is that there are different holidays in each country. You will have to match everything against your holidays.

Was this reply helpful? Yes No
slvedva 176 on at

Like (0)

Report

Hi @Matthy79
Sorry my mistake and you are correct
13-09-2023 - yes, it's working day 1
14-09-2023 - working day2
15-09-2023 - working day3
18-09-2023 - working day4
19-09-2023 - working day5 and flow I run automatically every working day. In my case don't matter holidays, I talk 5 working days in week.

Was this reply helpful? Yes No
slvedva 176 on at

Like (0)

Report

@Matthy79 maybe there are some possibility create additional flow, when file is created, system update file some field with some notification date, which calculate create date + 5 working days and which later can be used for filtering?

Was this reply helpful? Yes No