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

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

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

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.

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;