cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Girish_Kalamati
Level: Powered On

Powerapps DropDown Control issues : While Populating drop down input control items with text/records

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 !

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Powerapps DropDown Control issues : While Populating drop down input control items with text/rec

Hi @Girish_Kalamati,

Could you please share a bit more about the OpenAPI definition file of your custom connector?

Have you specified a proper Response part within your OpenAPI definition?

The error message seems to tell that the Items property of the Dropdown control is required to provide a Table value, but the MCFAzureFunction.RunThis(Button1.Pressed) formula that you provided returns a Text value.

I think this issue may be related to the value that you typed within the MCFAzureFunction.RunThis() function. The Button1.Pressed formula would always return false, regardless of whether or not click the Button1. Please take a try to type a actual value (e.g. true) within the MCFAzureFunction.RunThis() function, then check if the issue is solved.

In addition, please also take a try to modify your C# code as below:

 

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); /* <-- Modify here, don't convert the DataTable into a Json String using DataTableToJson function */
qResponse = dt; /* <-- instead, assign dt variable value to qResponse variable directly */ } 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"); } } }

Then resgister a new custom connector within your PowerApps based on your Azure function, check if the issue is solved.

You must check if you have defined a proper Response part within the OpenAPI definition file for your custom connector. The standard OpenAPI definition file format as below:

https://procsi.blob.core.windows.net/docs/SentimentDemo.openapi_definition.json

More details about creating a custom connector within PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/register-custom-api

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Girish_Kalamati
Level: Powered On

Re: Powerapps DropDown Control issues : While Populating drop down input control items with text/rec

hi Kris,

Tried out pointers recommended by you earlier and gave try few hours back "NO LUCK". 

I even simplified my OpenAPI in swagger to as simple as possible as below:

swagger: '2.0'
info:
  version: 1.0.0
  title: FetchData
host: ********.azurewebsites.net
paths:
  /api/Function1:
    get:
      description: Calls my azure function over https
      operationId: RunThis
      parameters:
        - name: code
          in: query
          description: code
          default: **********************************************************************
          required: false
          type: string
        - name: name
          in: query
          required: true
          default: Test
          type: string
      responses:
        '200':
          description: Successful response - Returned from OpenAPI
          schema:
            type: array
            items:
              {
                type: string
              }
        '400':
          description: Bad request - Returned from OpenAPI

Let me know if you have any article which talks about flushing in data in dropdown from Azure function and Describes schema for string array object as an azure function response.

 

Thanks in Advance!

 

 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 206 members 5,059 guests
Please welcome our newest community members: