cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonathan-101
Helper V
Helper V

Convert text to date

Hi all,

I have a list of invoices in a SharePoint list that I would like to select an option in manually trigger a flow to return all invoices for that month.

 

Currently I have the options in manually trigger a flow as; Jan, Feb, Mar, Apr, May etc months.

 

How do I use this to filter query in Get items for the specific month? For example, when Jan is selected, I only receive all items from January?

 

Any help would be appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions

WOW - that is fantastic, thank you very much @grantjenkins 

 

I don't suppose it's possible to add after Nov, Dec an 'All' button that would capture all the invoices?

View solution in original post

I would add a variable for the Filter Query that would stay empty if you selected All and have the actual Filter Query as before if you selected a Month.

 

See flow below. Note that I added All to the end of the Month choices on the Manual trigger input.

 

It will initialize a variable to hold the filter query. Then if the month you selected was NOT All it would build up the Filter Query and set it in the variable. Then for the Get items Filter Query you just pass in the variable.

grantjenkins_2-1667385698334.png

 

 

grantjenkins_3-1667385752864.png



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

View solution in original post

4 REPLIES 4
grantjenkins
Community Champion
Community Champion

I think I've got what you are after. Took much longer than I expected but fun 🙂

 

Effectively we need to get the actual Month number from your Jan, Feb, etc. then build up the dates we need to filter on. Since we can't format our actual SharePoint dates as part of the Filter Query we need to check if the date is greater than or equal to the start of the month, and less than the beginning of the following month.

 

See full flow below. I'll go into each of the actions.

grantjenkins_0-1667004890332.png

 

The Manual trigger has a selection of months to choose from. I'm assuming this is similar to what you have.

grantjenkins_1-1667004937502.png

 

Compose Month gets the Month number from your choice. Note that it doesn't matter what you put for the day or year as it's just going to look at the month. triggerBody()['text'] will return your Month choice.

If your choice was March then this would return 03, if Sep then 09, etc.

 

formatDateTime(concat('01-',triggerBody()['text'],'-2000'),'MM')

 

grantjenkins_2-1667005050504.png

 

Compose Year just gets the current Year that we can use when we build up our dates.

 

formatDateTime(utcNow(), 'yyyy')

 

grantjenkins_3-1667005260184.png

 

Compose Start of Month builds up the date using 01 for the day, Compose Month for the month, and Compose Year for the year, and formatted as yyyy-MM-dd

 

formatDateTime(concat(outputs('Compose_Year'),'/',outputs('Compose_Month'),'/','01'), 'yyyy-MM-dd')

 

grantjenkins_4-1667005359956.png

 

Compose Start of Next Month adds 1 month to the Compose Start of Month.

 

startOfMonth(addToTime(outputs('Compose_Start_of_Month'),1,'Month'), 'yyyy-MM-dd')

 

grantjenkins_5-1667005432131.png

 

Finally, we use Get items using the following Filter Query:

InvoiceDate ge 'Compose Start of Month' and InvoiceDate lt 'Compose Start of Next Month'.

It's important to note that we use ge (greater than or equal to) so we include the 1st of the Month, and lt (less than) so we don't include 1st of next Month.

grantjenkins_7-1667005678092.png

 

I've just added a Compose on the end of my example so I can see what's actually returned by the Get items action. You would likely have a Apply to each so you can iterate over them.

grantjenkins_8-1667005897632.png

 



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

WOW - that is fantastic, thank you very much @grantjenkins 

 

I don't suppose it's possible to add after Nov, Dec an 'All' button that would capture all the invoices?

I would add a variable for the Filter Query that would stay empty if you selected All and have the actual Filter Query as before if you selected a Month.

 

See flow below. Note that I added All to the end of the Month choices on the Manual trigger input.

 

It will initialize a variable to hold the filter query. Then if the month you selected was NOT All it would build up the Filter Query and set it in the variable. Then for the Get items Filter Query you just pass in the variable.

grantjenkins_2-1667385698334.png

 

 

grantjenkins_3-1667385752864.png



----------------------------------------------------------------------

If I've answered your question, please mark the post as Solved.

If you like my response, please consider giving it a Thumbs Up.

Thats fantastic @grantjenkins thanks again for the expertise and quick assistance - very much appreciated

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (6,031)