Filter items from between 2 dates, export these to a excel file? and then email the file weekly.

[LewisFoulger]

Hello All,

Like the subject states, i am looking to filter items from the past week/month and then paste these items into an excel file of some sort? and then email the contents weekly? 

I am relatively new to automate and dont know the true power so was wondering if this is even possible? and if so how would this be done?

Appreciate the feedback!

[KvB1 · Accepted solution]

Yeah sure, you can create an odata filter query in the 'get items' action, then created a filter based on the created date.

For example:

Created gt '@{addDays(utcNow(),-21)}'

Will only return items that were created in the past 21 days

Edit: these things are great for learning some expressions, and odata filters are great to use whenever you can in a bunch of different actions, so its definately worth to understand how they work and what you can do with them

[LewisFoulger]

Thanks! I just watched a simple tutorial on how these things work with text etc.  I looked at pulling all of the items between these dates:

I know they are all the same at the moment but it is just for the future.

But i suppose this works the same!

[LewisFoulger]

Hello again, I know I accepted the solution but I just realised the issue with this is that month lengths change, is there a way to get around this? The code you sent was brilliant but does not combat the issue unfortunately.

[KvB1]

That depends on what exactly you want,

Do you want, for example, to press a button, and see the records that were created that specific month?

[LewisFoulger]

Depending on the month itself, so 12 different months, they have different lengths? So i would like the compensate that on my monthly report? I was wondering if there was Odata query to compensate for query data from last month. (I do have a date field if this helps)

[KvB1]

You can use: 

startOfMonth(utcNow())

To get the start of the current month. So if you use this in the filter, it will only return items that were created that month.

[LewisFoulger]

This could work, but the issue would be when to run the flow, as if i ran it on the 31st, it would miss some days off in february on a leap year for example?

[KvB1]

Well yeah, so it depends on what you want 😛

You could also tell the automate to grab all the dates between the start of the previous month and the start of the current month, and then run the flow on the 1st of every month.

[LewisFoulger]

That sounds like the best idea, run the flow at like 6am or something of the first month.

What would be the Odata to pull the first?

[KvB1]

You can use 

subtractFromTime(utcNow(),1,'Month') as the bottom of the filter, and

utcNow() as the top

If you want to use the exact start of the month you could change it to 

 startOfMonth(subtractFromTime(utcNow(),1,'Month'))

and

startOfMonth(utcNow())

So created would be gt (greater than) the bottom, and lt (less than) the top