cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Skilled Sharer
Skilled Sharer

Get Multiple Rows from Azure Functions SQL Command

Hi all,

 

I am currently experimenting with Azure Functions and so far it seems I can only return an item at a time, a string or an int, for example. I have built a Function which returns the results of a select query on a table based on the current user department (passed in as a query parameter) - and if I only return the first row value as a string, I can display it in Power App, but if I return a List<String> then it is not showing anything, with no warnings. 

 

Does anyone have any idea? Is it that using Azure Function we can only return one thing back?

Or am I doing it wrong?

 

P.S. Probably I should mention I am creating a custom connector to connect to my Azure Function and if I test the connection there directly, I can see the values returned correctly even when I use List<String> in the Function code.

 

Screenshot_5.pngScreenshot_6.png

 

Thanks,

 

MelindaK
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Get Multiple Rows from Azure Functions SQL Command

Hi @MelindaK,

 

Do you mean when testing the Azure function in Azure Portal, it would work as expected, but when it comes to PowerApps, only single string could work?

For custom connectors, the error message (Response) and the corresponding returned data type (parameter) should be defined within the Swagger (Open API file), have you checked that part?

Adding a blog for reference:

Using Azure functions in PowerApps

Register and use custom connectors in PowerApps

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Get Multiple Rows from Azure Functions SQL Command

Hi @MelindaK,

 

Do you mean when testing the Azure function in Azure Portal, it would work as expected, but when it comes to PowerApps, only single string could work?

For custom connectors, the error message (Response) and the corresponding returned data type (parameter) should be defined within the Swagger (Open API file), have you checked that part?

Adding a blog for reference:

Using Azure functions in PowerApps

Register and use custom connectors in PowerApps

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Skilled Sharer
Skilled Sharer

Re: Get Multiple Rows from Azure Functions SQL Command

Hi @v-micsh-msft

 

Yes you are right, the Swagger file was set to return a 'string' response type, and I have now modified it to return 'array' of string items. I have realised that yesterday afternoon after a lot of reading.

I wish that there would be a more clear indication as to how to configure the Swagger file. It took me all day yesterday and this morning to get it to return a simple array.

 

Anyway thanks for your reply, it was clearly redirecting me in the right direction. 

 

PS. I'll leave here the part of the Swagger file which configures to return array in case anyone needs it. I've just tested it in a Power App and it returns the values corectly.

"paths": {
    "/api/endpoint_name": {
      "get": {
        "operationId": "operation_name",
        "produces": [
          "application/json"
        ],
        "consumes": [
          "application/json"
        ],
        "parameters": [
          {
            "name": "department",
            "in": "query",
            "description": "Your department",
            "required": true,
            "type": "string"
          }
        ],
        "description": "",
        "responses": {
          "200": {
            "description": "Success operation",
            "schema": {
              "type": "array",
              "items": {
                "$ref": "#/definitions/Employees"
              }
            }
          }
        }
      }
    }
  },
  "definitions": {
    "Employees": {
      "type": "string"
    }  }
MelindaK

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,214)