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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,923)