cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @Anonymous,

 

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
Community Support
Community Support

Hi @Anonymous,

 

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

Anonymous
Not applicable

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"
    }  }

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (6,370)