cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Christoph_G
Advocate II
Advocate II

Filter D365 Finance and Operations Records by month using ODATA

Hello everyone,

I'm trying to get all Sales Orders with the ConfirmedShippingDate in a specific month.

Since ODATA has a month() function, my first approach of the filter query was:

"month(ConfirmedShippingDate) eq 9"
to filter dynamically for the current month I also thought about
"month(ConfirmedShippingDate) eq month(utcnow())

both of those statements return an error:
"An error has occurred. Property 'Int32 Month' is not defined for type 'Microsoft.Dynamics.Ax.Xpp.AxShared.Date' clientRequestId: 220afd28-0f67-4b1a-9930-f84db1df38d2"

I then came up with this solution, which does work, but is not optimal since it doesn't actually relate to months but only goes back a fixed amount of days

"ConfirmedShippingDate ge @{adddays(utcnow('yyyy-MM-dd'), -31, 'yyyy-MM-dd')} and  ConfirmedShippingDate lt @{adddays(utcnow('yyyy-MM-dd'), +0, 'yyyy-MM-dd')}"

To make this more dynamic and usable I tried this to always go back until the actual beginning of the current month:

"ConfirmedShippingDate ge @{adddays(utcnow('yyyy-MM-dd'), -dayofmonth(utcnow()), 'yyyy-MM-dd')} and  ConfirmedShippingDate lt @{adddays(utcnow('yyyy-MM-dd'), +0, 'yyyy-MM-dd')}"

I thereupon get the message that the "exppression is not valid"; adding the int()-function doesn't change a thing [-int(dayofmonth(utcnow()))].

Any ideas how to solve this dynamically? It doesn't matter to me if i have to tell the flow which month it is or if it works automatically for the current month. I just want somehting better than going 31 days back when executing the flow at the end of the month (which would cause problems if the month just has 30, 28, or 29 days)

Thank you in advance!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-micsh-msft
Community Support
Community Support

Hi @Christoph_G,

 

The Month() function will not work under Dynamics 365 OData query, which is documened in the article below:

Query Data using the Web API

Quoted:

"

This is a sub-set of the 11.2.5.1.2 Built-in Query FunctionsDateMathTypeGeo and other string functions aren’t supported in the web API.

"

To filter within the month, we could take a try with the following way:

"ConfirmedShippingDate ge @{Startofmonth(utcnow())} and  ConfirmedShippingDate lt @{StartofMonth(adddays(utcnow('yyyy-MM-dd'), 31))}"

Actually we could just take use of the expression under the Filter query:

For the Startofmonth of current month, we use:

startOfMonth(utcnow())
As the first expression,
For second value, calculate the startofmonth after Add 31 days of UTCNow(), which means the start of next month.
81.PNG
82.PNG
 
Doing it in this way should be able to filter within current month.
See the running details:
83.PNG
 
Regards,
Michael
Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @Christoph_G,

 

Take a try to modify the formula as below

"ConfirmedShippingDate ge @{Startofmonth(utcnow())} and  ConfirmedShippingDate lt @{StartofMonth(adddays(utcnow(), 31))}"

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-micsh-msft
Community Support
Community Support

Hi @Christoph_G,

 

The Month() function will not work under Dynamics 365 OData query, which is documened in the article below:

Query Data using the Web API

Quoted:

"

This is a sub-set of the 11.2.5.1.2 Built-in Query FunctionsDateMathTypeGeo and other string functions aren’t supported in the web API.

"

To filter within the month, we could take a try with the following way:

"ConfirmedShippingDate ge @{Startofmonth(utcnow())} and  ConfirmedShippingDate lt @{StartofMonth(adddays(utcnow('yyyy-MM-dd'), 31))}"

Actually we could just take use of the expression under the Filter query:

For the Startofmonth of current month, we use:

startOfMonth(utcnow())
As the first expression,
For second value, calculate the startofmonth after Add 31 days of UTCNow(), which means the start of next month.
81.PNG
82.PNG
 
Doing it in this way should be able to filter within current month.
See the running details:
83.PNG
 
Regards,
Michael
Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hello @v-micsh-msft

looks like what I was looking for - thank you for your suggestion. I will try it soon!

Kind Regards,
Christoph

Hi @v-micsh-msft,

I just copied what you suggested into my Flow and get following error:
An error has occurred. The time zone information is missing on the DateTimeOffset value '2017-11-01T00:00:00.0000000'. A DateTimeOffset value must contain the time zone information. clientRequestId: 5f59e51b-f8b0-4574-be81-8c2ed3025a1c

I don't understand why the exact same statement you used causes trouble in my Flow.. any ideas?

Thank you
Christoph

Hi @Christoph_G,

 

Take a try to modify the formula as below

"ConfirmedShippingDate ge @{Startofmonth(utcnow())} and  ConfirmedShippingDate lt @{StartofMonth(adddays(utcnow(), 31))}"

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-micsh-msft,

looks good! Thank you very much! 🙂

2017-10-27 13_37_27-Eigenen Flow bearbeiten _ Microsoft Flow.png

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,524)