cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iso
Advocate III
Advocate III

SQL action 'Get rows (V2)' Aggregation transformation field major bug

This is a bug with the following Power Automate Cloud action:  SQL Get rows (V2) and how table columns are handled when specifying an Aggregation transformation and a column name in the Select Query field referencing the column alias created in the Aggregation transformation field. Here is a screenshot of how the action is setup prior to the test run:

sql_get_rows_v2_ODATA_aggregation_bug.JPG

When running this in a simple test flow, it works as expected.  But when running this exact same action, and I mean, with the exact same settings and params in the production flow that has a lot more steps to it, then it fails miserably with an error message that does not point to any inkling of a solution. To wit:

 

 

 

The API 'sql' returned an invalid response for workflow operation 'SELECT01::check_if_REVISION_CODE_in_ENERGY' of type 'OpenApiConnection'. Error details: 'The API operation 'GetItems_V2' is missing required property 'body/value/0/rev_code'.'

 

 

 

And yes, the rev_code column exists in the table and contains no NULL values.  The column is of SQL Server DATE type.  When testing on another table with a column of type BIGINT, it works in the production flow.  But AGAIN, on the original table with the column of type DATE, it works in the test flow, so why not in the production flow?

One last thing to note: when selecting Peek code in the action menu, one can see this for the production flow:

 

 

{
    "inputs": {
        "host": {
            "connectionName": "shared_sql",
            "operationId": "GetItems_V2",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
        },
        "parameters": {
            "server": "default",
            "database": "default",
            "table": "[dbo].[ENERGY]",
            "$apply": "aggregate(rev_code with max as max_rev_code)",
            "$select": "max_rev_code"
        },

 

 

 which is exactly the same for the test flow, except for the "metadata" keys, which have different "operationMetadataId" values.

 

I am open to suggestion as this is the end of the road for me, and God forbid, perhaps someone at Microsoft in tech support familiar with this product issue could chime in and actually help users become more productive because as it stands, if adding up all the hours wasted on the this, I could have written a dozen flows in Python instead!

0 REPLIES 0

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (5,051)