Skip to main content

Notifications

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

API Query with flowfields

(0) ShareShare
ReportReport
Posted on by 231

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

  • Juliem Profile Picture
    Juliem 231 on at
    RE: API Query with flowfields

    Thank you very much

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: API Query with flowfields

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

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

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: API Query with flowfields

    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.

  • Juliem Profile Picture
    Juliem 231 on at
    RE: API Query with flowfields

    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 32,748 Super User 2024 Season 1 on at
    RE: API Query with flowfields

    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 231 on at
    RE: API Query with flowfields

    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 32,748 Super User 2024 Season 1 on at
    RE: API Query with flowfields

    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 231 on at
    RE: API Query with flowfields

    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 32,748 Super User 2024 Season 1 on at
    RE: API Query with flowfields

    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.

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

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans