cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrtnM
Helper V
Helper V

Filtering based on date range

I am building a Flow that gets items from a Sharepoint list, filters them based on date (created in the latest 31 days), puts those in an html table and sends an email.

 

In the list, one item is created in 2017, one in 2018, one in Februari 2019 and three are created yesterday for testing.

 

This is the Filter array expressions I use:

@greaterOrEquals(formatDateTime(item()?['Created'], 'MM/dd/yyyy'), formatDateTime(addDays(utcNow(), -31), 'MM/dd/yyyy'))

 

Everything works, except for the filtering: the filter array collects all the items from yesterday, plus the item from 2017. Why on Earth does this happen? I have tried changing the "-31" to for example -1 (this gets the same result), and -700 (this only collects the 2017 item).

 

I have also tried to replace the expressions in Filter array with 

@greaterOrEquals(formatDateTime(item()?['Created'], 'MM/dd/yyyy'), formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd'))

but this returns nothing, and

@lessOrEquals(formatDateTime(item()?['Created'], 'MM/dd/yyyy'), formatDateTime(getPastTime(1, 'Month'), 'yyyy-MM-dd'))

but this of course returns everything.

and also tried to delete the Filter array and instead in the Get items make a Filter query:

sd.png

 

 

This is the complete Flow as it looks right now.

Namnlös.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
yashag2255
Dual Super User II
Dual Super User II

Hey @MrtnM 

 

I did it like this and it works fine: 

ddeew.PNG

Expression: 

 

addDays(utcNow(),-31)

Note that you need to get items where created date is greater than or equal to that date (today -31)

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

5 REPLIES 5
yashag2255
Dual Super User II
Dual Super User II

Hey @MrtnM 

 

I did it like this and it works fine: 

ddeew.PNG

Expression: 

 

addDays(utcNow(),-31)

Note that you need to get items where created date is greater than or equal to that date (today -31)

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Well, that worked smoothly 🙂

Perfect, thanks!

 

Follow up question: 

If insted I would like to collect the items created earlier than -31 days ago (up until -31 days), what would that expression be?

yashag2255
Dual Super User II
Dual Super User II

Hey @MrtnM 

 

You can just replace the 'ge' with 'le' and it should get you items created prior to the -31 days created date. Ensure that you check the top count in the action as the default returns 100 items only. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Another follow up:

 

If I also would liek to filter items from a yes/no column, that is: filter items from the last 31 days, that also have No in column X, is it possible to do this in the Filter query as well?

yashag2255
Dual Super User II
Dual Super User II

Hey @MrtnM 

 

Can you try this: after the date filter condition, put an and columname eq true (if it is a yes no type column then the value will be boolean and true for yes, false for no. you need to add the true or false from the expression editor)

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (24,270)