Expose Closing Dates in Business Central API

(0) Share

Report

Posted on by Darryn

Hi,

I am needing expert advice on how to write an extension such that I can expose closing dates of Business Central. From what I understand dates in API (APIv2 and ODATA) do not contain any information about closing dates as the type is Edm.Date. After lots of hours looking for a solution, using the source looked promising (CLSINCOME), but not all entries are of this source, with some being general journals. Hence I need to get information in the API about a date being a closing date. This is for historical data as well as current so a solution to calculate and store a new field in a table extension may not work unless I can update all historical data.

So my questions:

Can a query extension do a calculation such as set a entry flag, date := ClosingDate(date)? From what I can see this is a no as queries all flow through to T_SQL.

If a calculated field is displayed on a page, but not stored, can this be sent via an API call to the page?

If I need to store a closing date flag in my data, can run a procedure that would update historical data without breaking any data?

Thank you brains trust.

Regards

Darryn

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

JAngle 133 on at

Like (0)

Report

A very different topic but it should show that having a procedure associated to a field in an api page can return simple or complex types: joshanglesea.wordpress.com/.../

Prior-Year entry is another field that could help with filtering

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (1)

Report

In the SQL database the posting date is a date/time field.

So the difference for a normal date and the closing date is the time you see in that field.

Normal dates have one time, closing date has another timestamp for the same date.

So you can query them from the SQL server.

And yes you can build a API that returns the value of a flowfield. You just need to make sure you do a clacfields on them in the API code.

Was this reply helpful? Yes No
Darryn 5 on at

Like (0)

Report

Josh, Thank you for sharing your use and solution. It helped me to be able to understand how to provide a solution. In the end I have done a simple Format of the date including <Closing> and will further process after consuming through the API.

Was this reply helpful? Yes No
Darryn 5 on at

Like (0)

Report

Hi Inge. This is Business Central cloud. Can I still do SQL queries? Also, what type would I give the Flow Field. I am using an approach which Josh suggested for Page, but would also explore a Query approach if possible. Can Flowfields be used in queires?

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

Hi Darryn

You can not do a sql query against BC cloud.

But a query can be a good option.

And yes, flowfields are supported in the query objects.

Here is some more info around it:

docs.microsoft.com/.../optimize-sql-query-objects-and-performance

Hope this helps you.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Expose Closing Dates in Business Central API

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

Expose Closing Dates in Business Central API

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates