web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / API Query with flowfields
Small and medium business | Business Central, N...
Suggested Answer

API Query with flowfields

(0) ShareShare
ReportReport
Posted on by Juliem 379

Hi,

I am trying to create a Query API on the Change Log Entry table that will be use in Power Automate.

I'm having an issue adding the flowfields (Table Caption, Field Caption, etc...)

If I add them directly as columns, the Query doesn't work. Instead of returning the values, I'm getting this message

"error": {
    "code": "BadRequest_NotFound",
    "message": "No HTTP resource was found that matches the request URI...

As soon as I removed the flow fields, it's ok.

I tried adding a dataitem and getting the value myself but it doesn't let me because those flowfields are related to virtual tables

How can I get the field and table captions from the Change Log Entry in a Query API?

Thanks

-Julie

I have the same question (0)
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    Maybe you can share your code here. Then it is easier to see what you actually try to do and easier to figure out a way to help you.

  • Juliem Profile Picture
    Juliem 379 on at

    query 60030 "Change Log API"

    {

       QueryType = API;

       APIPublisher = 'Test';

       APIGroup = 'Custom';

       APIVersion = 'v2.0';

       EntityName = 'changeLogAPI';

       EntitySetName = 'changeLogsAPI';

       OrderBy = Ascending(tableNo), ascending(primaryKeyField1Value);

       elements

       {

           dataitem(changeLogEntry; "Change Log Entry")

           {

               column(entryNo; "Entry No.")

               {

               }

               column(userID; "User ID")

               {

               }

               column(tableNo; "Table No.")

               {

               }

               column(primaryKeyField1Value; "Primary Key Field 1 Value")

               {

               }

               //This part doesn't work (error message listed in my original post)

               column(fieldCaption; "Field Caption")

               {

               }

               // This part fails because it's a virtual table

               dataitem(fieldCaptionDataitem; Field)

               {

                    DataItemLink = tableno = changelogentry."Table No.", "no." = changelogentry."Field No.";

                    column(FieldCaption; "Field Caption")

                    {

                    }

               }

           }

       }

    }

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at 

    query 60030 "Change Log API"
{
    QueryType = API;
    APIPublisher = 'Test';
    APIGroup = 'Custom';
    APIVersion = 'v2.0';
    EntityName = 'changeLogAPI';
    EntitySetName = 'changeLogsAPI';
    OrderBy = Ascending(tableNo), ascending(primaryKeyField1Value);
    elements
    {
        dataitem(changeLogEntry; "Change Log Entry")
        {
            column(entryNo; "Entry No.")
            {
            }
            column(userID; "User ID")
            {
            }
            column(tableNo; "Table No.")
            {
            }
            column(primaryKeyField1Value; "Primary Key Field 1 Value")
            {
            }
            //This part doesn't work (error message listed in my original post)
            column(fieldCaptions; "Field Caption")
            {

            }
            // This part fails because it's a virtual table
            dataitem(fieldCaptionDataitem; Field)
            {
                DataItemLink = tableno = changelogentry."Table No.", "no." = changelogentry."Field No.";
                column(FieldCaption; "Field Caption")
                {
                }
            }
        }
    }
}

    In the code you published you had two columns named fieldcaption so it would not compile.

    This code at least compiles, but i have not tried to run it.

    But maybe it is not working even with this small corection?

  • Juliem Profile Picture
    Juliem 379 on at

    I know it would not compile, I tried both, separately, not together

               column(fieldCaptions; "Field Caption")

               {

               }

    This doesn't work

    "error": {

       "code": "BadRequest_NotFound",

       "message": "No HTTP resource was found that matches the request URI...

               dataitem(fieldCaptionDataitem; Field)

               {

                   DataItemLink = tableno = changelogentry."Table No.", "no." = changelogentry."Field No.";

                   column(FieldCaption; "Field Caption")

                   {

                   }

               }

    This doesn't work either

    {"error":{"code":"Internal_ServerError","message":"A query cannot be based on a virtual table. Please contact your system administrator.  CorrelationId:  30417284-9627-47b0-ad96-8040e30621d7."}}

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    Then you should probably use an API page instead. That should not have the same limitation

    page 50101 ChangeLogApi
{
    APIGroup = 'apiGroup';
    APIPublisher = 'Community';
    APIVersion = 'v1.0';
    Caption = 'changeLogApi';
    DelayedInsert = true;
    EntityName = 'ChangeLog';
    EntitySetName = 'ChangeLog';
    PageType = API;
    SourceTable = "Change Log Entry";


    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(tableNo; Rec."Table No.")
                {
                    Caption = 'Table No.';
                }
                field(tableCaption; Rec."Table Caption")
                {
                    Caption = 'Table Caption';
                }
                field(userID; Rec."User ID")
                {
                    Caption = 'User ID';
                }
                field(fieldNo; Rec."Field No.")
                {
                    Caption = 'Field No.';
                }
                field(fieldCaption; Rec."Field Caption")
                {
                    Caption = 'Field Caption';
                }
                field(dateAndTime; Rec."Date and Time")
                {
                    Caption = 'Date and Time';
                }
            }
        }
    }
    trigger OnAfterGetRecord()
    begin
        rec.CalcFields("Table Caption", "Field Caption");
    end;
}

  • Juliem Profile Picture
    Juliem 379 on at

    How can I sort in an API Page?

    This was the first thing I tried, which was showing up the fields, but they have a lot of records in the table and with the Query, I was using the OrderBy, which helped a lot for filtering

    OrderBy = Ascending(tableNo), ascending(primaryKeyField1Value);

    I can't seem to use that property on an API Page

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    Under the sourcetable you add source table view

    SourceTable = "Change Log Entry";

    SourceTableView = sorting()

    In between the parentheses you add the fields in the order you want them sorted.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,111 Moderator on at

    Here you can read about all the fun stuff you can do with the table view

    docs.microsoft.com/.../devenv-sourcetableview-pages-property

  • Juliem Profile Picture
    Juliem 379 on at

    Thank you very much

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

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

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 2,664

#2
YUN ZHU Profile Picture

YUN ZHU 960 Super User 2025 Season 2

#3
Jainam M. Kothari Profile Picture

Jainam M. Kothari 773 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans