Powerapps DropDown Control issues : While Populating drop down input control items with text/records/list/DataRows from Azure function

Question

Agenda: We are trying to display our data fetched from SQL in drop down list ,But instead of giving directly we are using "Azure Functions" which can help retrieve data from SQL via Key Vault

Issue: Powerapps DropDown/ListBox control are not able to understand the output given by Azure function. We tried to render outputs from Azure Functions i.e. string/text/records/list/DataRows

Error:

the property expects table values but this rule produces incompatible text values

What we did:

1. Created Azure Function(Gave all needed access such as managed service identity, app addition to AAD etc.)(Tested the function, we get expected output)
2. Created API with swagger 2.0
3. Created a Custom powerapp connector with the above OpenAPI file(Connector works fine)
4. We invoke the function from the Powerapp canvas like this MCFAzureFunction.RunThis(Button1.Pressed)

Help: Let us know how best we can push the response data fromazure function to powerapp dropdown or listbox control. or we are doing/missing something

Code:

using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Azure.KeyVault;
using Microsoft.Azure.Services.AppAuthentication;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;

namespace MCFuncApp
{
    public static class Function1
    {
        private static HttpClient client = new HttpClient();

        private static string BuildConnString(string secret, string dbCatalog)
        {
            return ("Server=********** Timeout=30;");
        }

        private static string DataTableToJSON(DataTable table)
        {
            string JSONString = string.Empty;
            JSONString = JsonConvert.SerializeObject(table);
            return JSONString;
        }

        [FunctionName("Function1")]
        public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)
        {

            log.Info("C# HTTP trigger function processed a request.");

            // parse query parameter
            string name = req.GetQueryNameValuePairs()
                .FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
                .Value;

            if (name == null)
            {
                // Get request body
                dynamic data = await req.Content.ReadAsAsync<object>();
                name = data?.name;
            }

            string exampleSecret = "Secret";
            string qResponse = "";

            if(name != null)
            {   // "https://********"
                string vaultBaseUrl = "*********";
                var azureServiceTokenProvider = new AzureServiceTokenProvider();
                var kvClient = new KeyVaultClient(new KeyVaultClient.AuthenticationCallback(azureServiceTokenProvider.KeyVaultTokenCallback), client);
                var secret = (await kvClient.GetSecretAsync(vaultBaseUrl)).Value;
                var connString = BuildConnString(secret, "*****");

                string query = "*********";

                var dt = new DataTable();
                using (SqlDataAdapter da = new SqlDataAdapter(query, connString))
                {
                    da.Fill(dt);
                }

                qResponse = DataTableToJSON(dt);                
            }


            return name == null
                ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
                : req.CreateResponse(HttpStatusCode.OK, qResponse, "application/json");
        }
    }
}

Thanks in Advance !

Answer 1

I think the issue might be related to the structure of the JSON content the Azure Function returns.

A supported structure should look something like:

Table({ Text: "Item 1", Val: 1 }, { Text: "Item 2", Val: 2 }, { Text: "Item 3", Val: 3 })

You might need to serialize the returned rows manually, as the DataRow class contains much more information than what you need, and concatenate the end result using a StringBuilder before passing it back as the response's content.

Hope it helps.