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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,287)