cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Christoph_G
Level: Powered On

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
Community Support Team
Community Support Team

Re: Filter D365 Finance and Operations Records by month using ODATA

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

Community Support Team
Community Support Team

Re: Filter D365 Finance and Operations Records by month using ODATA

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
Community Support Team
Community Support Team

Re: Filter D365 Finance and Operations Records by month using ODATA

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

Christoph_G
Level: Powered On

Re: Filter D365 Finance and Operations Records by month using ODATA

Hello @v-micsh-msft

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

Kind Regards,
Christoph

Christoph_G
Level: Powered On

Re: Filter D365 Finance and Operations Records by month using ODATA

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

Community Support Team
Community Support Team

Re: Filter D365 Finance and Operations Records by month using ODATA

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

Christoph_G
Level: Powered On

Re: Filter D365 Finance and Operations Records by month using ODATA

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,864)