Closing Dates and OData - how to query and use in external clients?

Dynamics 365 Business Central/NAV Developers Forum
#1

The issue: Closing Dates are a problem for consumers of OData endpoints.

Does anyone know the correct way to operate on closing dates in OData?

Simple scenario:

  • Given table: Date, Amount PK (Date, Amount)
  • Page with the table as the source
  • OData Endpoint of Page (not using API Type)

Table populated with:
Date, Sum(Amount) from GL Entries Group by Date

The “ClosingDates” Property was not included on the table field, thus any Page rendered did not communicate closing dates populated from the G/L Entries. ClosingDates = false (default) does not render the Date on a page with the C Prefix. The same page is provided as an OData Endpoint.

By setting the ClosingDates property to true, the page now correctly communicates C12/31/2019 to the user in the GUI. However, there is nothing to communicate the value during calls to web services. So we end up with what appears to be a schema issue. Recall the PK is on Date/Amount.

OData Result:
“PostingDate”:“2019-12-31”, “Amount”:-100.00
“PostingDate”:“2019-12-31”, “Amount”:1000.00

The only solution was to add an additional property: “IsClosingDate” to the API response to clarify “ClosingDate” in the dataset.
IsClosingDate := (PostingDate = ClosingDate(PostingDate))

This feels like a valid workaround, but then a consumer doesn’t appear to be able to fetch closing entries for a particular date.

OData Filters using the dates can’t operate on Closing Dates. $filter=(PostingDate eq 2019-12-31) Just can’t seem to find an example to query ClosingDates explicitly in OData.

1 Like
#2

A good question.
And if you using less then? So it will be:
$filter=(PostingDate lt 2020-01-01)
That will be I hope including the closing date.
And
$filter=(PostingDate lt 2020-01-01) AND PostingDate gt 2019-31-12)
maybe the closing date

#3

Ya, that would work for getting the data, but that doesn’t resolve the core of the issue is that a date property on Business Central serialized as “12/31/2019”, “12/31/2019” and they are not the same.

Upon further investigation:

The root of the issue is that the Time Constant on the Date is not pushed out on the API Endpoint. Thus, causing auto-generated client code to corrupt results.

The Odata Key on the OData client was:
[global::Microsoft.OData.Client.Key(“PostingDate”)]

But - the PostingDate of 12/31/2019 and C12/31/2019 (really: 12/31/2019 23:59:59) is not pushed out on the API as DateTime. It’s a Date.

And that is where the autogenerated OData client code fails and is causing the issues. (Items with the same key would overwrite each other in the results)

I feel like adding the “IsClosingDate” is a step in the right direction. But… The Odata endpoint query can’t resolve a question like “IsClosingDate = 1 AND PostingDate = X”. Unless… I go the extra step of converting closing dates to dates.

#4

Providing the IsClosingDate boolean was valid in this scenario.
However, no solution was found for the OData Filter other than to expand the criteria to < EndDate + 1 Day;