Small and medium business | Business Central, N...

Query all fields from table via ODATA API

(5) Share

Report

Posted on by LiamSh

I am trying to understand why some queries fail when called via ODATA API and others do not. I get a 404 error, with no further information added for a small number of tables.

I am trying to return ALL fields from Change Log Entry, as an example, using ODATA API and a custom extension I have published containing a query. I have noticed in debugging that if I remove certain fields from Change Log Entry from my query, the query (& associated API) will run successfully.

Below is the AL code for the Change Log Entry table itself. This is the base table design, rather than my query. I noticed this field is a bit more than just a simple field where data is loaded. It has a lookup to AllObjWithCaption. Are there any known restrictions on which tables / fields can be loaded via ODATA APIs / Web Services? I believe it's restricted, or something like this.

Depending on the reason for the issue, let's say I absolutely need to extract this field, what are workarounds if it is indeed a limitation causing my error?

Many thanks in advance

        field(6; "Table Caption"; Text[250])
        {
            CalcFormula = lookup(AllObjWithCaption."Object Caption" where("Object Type" = const(Table),
                                                                           "Object ID" = field("Table No.")));
            Caption = 'Table Caption';
            FieldClass = FlowField;
        }

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (8)

Answers (0)

Steven Renders 5,672 Moderator on at

Like (1)

Report

This is a flowfield. Adding flowfields to queries should not be an issue, although there has been a bug that causes it to fail.

A workaround is not adding flowfields to query APIs and instead create an extra query API with the data of the table the flowfield is calculating.

Was this reply helpful? Yes No
LiamSh 36 on at

Like (0)

Report

@Steven Renders I'm not sure the Flow Field is the issue. I have referenced hundreds of flow fields from other tables successfully however a common theme in testing is that ANY reference to the AllObjWithCaption table seems to fail when a lookup is actioned.

Are there restrictions on returning data from this table using APIs? As I know I'm querying the Change Log Entry table, however I'm actually pulling data from AllObjWithCaption via the API.

Noted below, is the first few lines of the AL code for AllObjWithCaption, the InherentPermissions = rX; is something I believe sets permissions to not be accessible via anything but System Processes, in addition to this I believe the system processes can only have read access also. So could this be the reason for the API restriction?

table 2000000058 AllObjWithCaption

{

DataPerCompany = false;

Scope = Cloud;

//WriteProtected=True;

InherentPermissions = rX;

Was this reply helpful? Yes No
Suggested answer

Jainam M. Kothari 15,639 Super User 2025 Season 2 on at

Like (1)

Report

May this can help : https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/odata-known-limitations

https://blog.steveendow.com/2021/09/using-metadata-and-expand-with-business.html

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-extract-data

Was this reply helpful? Yes No
LiamSh 36 on at

Like (0)

Report

@Jainam M. Kothari thankyou for your reply. Which part of the links assists with my particular query? I've reviewed the links and I don't believe they address or explain the issue?

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,838 Super User 2025 Season 2 on at

Like (0)

Report

setting might be enforcing system-level restrictions that prevent AllObjWithCaption from being accessed via ODATA APIs.

Please go through it: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-inherent-permissions

Was this reply helpful? Yes No
Suggested answer

Khushbu Rajvi. 20,838 Super User 2025 Season 2 on at

Like (1)

Report

Do one thing: test this by comment the CalcFormula and FieldClass properties.

field(6; "Table Caption"; Text[250])
{
//CalcFormula = lookup(AllObjWithCaption."Object Caption" where("Object Type" = const(Table), "Object ID" = field("Table No.")));
Caption = 'Table Caption';
//FieldClass = FlowField;
}

If the API runs successfully after this change, then the issue is indeed caused by the FlowField lookup. We will think accordingly.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,750 Super User 2025 Season 2 on at

Like (0)

Report

Hi, I did a simple test and did not encounter the problem you mentioned.
Please check again whether the endpoint is correct.

Thanks.

ZHU

Was this reply helpful? Yes No
Vahid Ghafarpour 12,200 Super User 2025 Season 2 on at

Like (1)

Report

If any of the responses helped resolve your issue, please take a moment to mark the best answer. This helps others in the community quickly find solutions to similar problems.

To do this, simply click the "Does this answer your question?" button on the most helpful response and like the helpful posts. If your issue is still unresolved, feel free to provide more details so the community can assist further!

Thanks for being an active part of the Dynamics 365 Community!

Was this reply helpful? Yes No