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
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,712)