Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / API Query with flowfields
Business Central forum
Suggested answer

API Query with flowfields

(0) ShareShare
ReportReport
Posted on by Juliem 224

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

Categories:
Other
  • Juliem Profile Picture
    Juliem 224 on at
    RE: API Query with flowfields

    Thank you very much

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,744 Moderator 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,744 Moderator 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 224 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,744 Moderator 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 224 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,744 Moderator 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 224 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,744 Moderator 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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,552 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,552 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans