cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gk1d09
Frequent Visitor

Getting just the Month number from a Date field in filter query

I have a Date field in my list and just want to extract the Month number.

 

Essentially i would like the query to be something like:

formatDateTime('Date','MM') eq '03'.
In order to get only the values from March from the list.
 
The above expression does not work.
How can i achieve this?
 
1 ACCEPTED SOLUTION

Accepted Solutions

Hi!

No need for an 'apply to each' yet.

Please note the following

equals(formatDateTime(item()?['Date'], 'MM'), '03')

formatDateTime() output is a string, so you need to wrap 03 with single quotes

 

Can you run manually your flow, and inspect Filter array outputs? You will probably get an array of items matching your requirements? Hope there are just a few, if not we can add a 'SElect' action block just after the 'Filer array' for troubleshooting purposes

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

6 REPLIES 6
efialttes
Super User III
Super User III

Hi!

Sharepoint list?

If so, are getting the info via 'Get item' or via trigger 'When a item is created'?

Or, did you get it by means of 'Get items', and you wanna get the trick inside an 'Apply to each' / 'Select' / 'Filter array'?

Thanks!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



gk1d09
Frequent Visitor

My trigger is from a Powerapp. But I am using Get Items to get items from the Sharepoint list based on the filter query to then Apply to Each from the result. The filter query is based based on the Date from the Sharepoint list and the month number eq value is fed from the Powerapp. For an example, I am getting the month value in Powerapps using Month(StartDate)

Hi!

I think the problem is, when using oDATA expression in your Filter Query input, you need the following sintax:

 

ColumnName operator value

 

...but you cannot manipulate your column name by using

 

expression(Columname) operator value

 

The approach you have designed can work if you replace Filter Query and use a 'Filter array' action block just after 'Get items' instead, just need to assign 'Get items' output as 'Filter array' input. 

Does this new approach make sense to you?

Please let me know and we will start working on it



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



gk1d09
Frequent Visitor

Okay. Its seems strange that you cant essentially substring a column before a compare.

 

I have added a Filter array with:

 

{
    "inputs": {
        "from""@body('Get_items')?['value']",
        "where""@equals(formatDateTime(item()?['Date'], 'MM'), 03)"
    }
}
 
Or adnaced. @equals(formatDateTime(item()?['Date'], 'MM'), 03)
 
So now i need to Apply to each on the body of this?
 
Thanks.

Hi!

No need for an 'apply to each' yet.

Please note the following

equals(formatDateTime(item()?['Date'], 'MM'), '03')

formatDateTime() output is a string, so you need to wrap 03 with single quotes

 

Can you run manually your flow, and inspect Filter array outputs? You will probably get an array of items matching your requirements? Hope there are just a few, if not we can add a 'SElect' action block just after the 'Filer array' for troubleshooting purposes

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

gk1d09
Frequent Visitor

Thanks, that has worked. Apply to Each on the body of Filter array is working correctly as well and producing the output i am expecting. I think this is solved.

 

Thank you for your help.

 

 

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

Top Solution Authors
Users online (20,657)