Filter D365 Finance and Operations Records by month using ODATA

[Christoph_G]

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!

[v-micsh-msft · Accepted solution]

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 Functions. Date, Math, Type, Geo 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.

Doing it in this way should be able to filter within current month.

See the running details:

Regards,

Michael

[Christoph_G]

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]

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

[v-micsh-msft · Accepted solution]

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

[Christoph_G]

Hi @v-micsh-msft,

looks good! Thank you very much! 🙂