Convert text to date

[Jonathan-101]

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.

[grantjenkins]

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.

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

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')

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

formatDateTime(utcNow(), 'yyyy')

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')

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')

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.

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.

[Jonathan-101 · Accepted solution]

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?

[grantjenkins · Accepted solution]

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.

[Jonathan-101]

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