cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aec2018
Microsoft
Microsoft

How do I extract values from a table (or JSON) to populate a drop down?

To populate a drop down input, I'm making an API call using a custom connector. I then set the response using Set(variableName, CustomConnector.Action(query).data.GetCompeteRegionMapping). The response is an object made up of objects with the same key:

 

"GetCompeteRegionMapping": [
        {
          "AzureBillingRegion": "AE Central"
        },
        {
          "AzureBillingRegion": "AE North"
        },
        {
          "AzureBillingRegion": "AP East"
        }]

 

I just want the AE Central, AE North, AP East, etc. as the drop down options but I think it's giving me null errors and (the correct number of) blank options because it's trying to display key/value pairs instead of just values. When I check the Power Apps monitor, my network call is correct and has the data but when I select one of the blank options, I get this error:

 

{
  "status": null,
  "duration": null,
  "dataSource": null,
  "responseSize": null,
  "controlName": "Dropdown_Test",
  "propertyName": "OnSelect",
  "nodeId": null,
  "formulaData": {
    "script": "",
    "spanStart": null,
    "spanEnd": null
  },
  "data": {
    "context": {
      "entityName": "Dropdown_Test",
      "propertyName": "OnSelect"
    }
  }
}

 

How can I get just the values from this table that's created in the Set() function? Or how can I set the variable to be an array of just the values initially? Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks for the suggestion. It actually turned out to be a lot simpler than I thought. I set the response of the custom connector to a collection using ClearCollect(collectionName, customerConnectorCall()) in the OnVisible property of the screen. Then, for the Items property of the drop down component, I set Distinct(collectionName, columnName). That gave me all of the unique values of the column. I got the idea from this PowerApps forum post.

Thanks!

View solution in original post

2 REPLIES 2
C-Papa
Super User
Super User

Hi, as far as I'm aware, you would need to pass this through PowerAutomate to extract the values. Below i have extracted them using formula, if you put the below on a button OnSelect in will collect the values into a collection called col_test. then change your dropdown items to col_test and you should be able to select the values. 

 

Clear(col_test);ForAll(Split(CustomConnector.Action(query).data.GetCompeteRegionMapping,""": """).Result As MyRecord,Collect(col_test,Left(MyRecord.Result,Find("""",MyRecord.Result)-1)))

Thanks for the suggestion. It actually turned out to be a lot simpler than I thought. I set the response of the custom connector to a collection using ClearCollect(collectionName, customerConnectorCall()) in the OnVisible property of the screen. Then, for the Items property of the drop down component, I set Distinct(collectionName, columnName). That gave me all of the unique values of the column. I got the idea from this PowerApps forum post.

Thanks!

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.

Top Solution Authors
Top Kudoed Authors
Users online (3,887)