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.
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.
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;
Further information due to a bug in this solution if server side paging kicks in with $skiptoken
and that token occurs on a closing date.
I had to go the extra step and convert the dates to normal dates and include the IsClosingDate in the PK . (Skip to End to see code)
Detailed information:
The @odata.nextLink and $skiptoken will fail if the Date is included as a field in the PK of the source table.
If the $skiptoken is using a date field containing closing dates and the server side paging occurs on a closing date, the token may duplicate data due to improper paging or error stating the skip token is no longer valid.
Only adding the IsClosingDate to the Primary Key didn’t solve the problem because $skiptoken=‘2014-12-31’,‘True’ in OData can’t query a closing date. Thus resulting in the “Skip token is no longer valid” error
This scenario can be replicated by using a temporary table with a PK on PostingDate,GLAccount but also includes an IsClosingDate boolean to alert the consumer that the date is actually a closing date.
The PK is still valid since Business Central is aware of the unique Date but Odata is not.
The fix which appears to have resolved filters/queries/paging: