cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kryptos
Regular Visitor

dropdown list with both ID and Value, store ID or key only

Im relatively new to Power Apps but experienced Access developer (like we all were) and adjusting to the differences and nuances. 

 

I have various drop down list in which I would like to store key values in an app im creating and not the descriptive value. I want the drop down to select the ID and Value from the lookup table, make the ID not visible so the user just sees the descriptive value and store the ID or key value. This was a common design pattern in Access and you could perform this by making the size of the first column 0". 

 

Is this also possible in power apps?

7 REPLIES 7

@kryptos 

 


@kryptos wrote:

... I want the drop down to select the ID and Value from the lookup table, make the ID not visible so the user just sees the descriptive value and store the ID or key value

 

  1. Create a Gallery Control
  2. Create a Dropdown Control
  3. Have a Gallery connected to a Data Source
  4. In Gallery Fields, make the Gallery not show the ID of the data source entries, and show just the description of them.
  5. Get your Gallery so it shows all the entries first
  6. Go to Items Property of Gallery and make it a Filter
  7. Go to Items Property of Dropdown and put the name of your data source in there.
    1. This makes it so you could select your record from the dropdown among all the records
  8. In Items property of Gallery use Filter function and put Something = Dropdown.Selected (depends on your scenario) if you want to show only a single entry in the Gallery based on the value of the dropdown.

In PowerApps the specific ID doesn't even necessarily have to be a key part of this.

rebeccas
Community Champion
Community Champion

I also came from Access to PowerApps. If you are using a SharePoint list then yes there is an autoID that is created with each line item that you can refer to. I use it often. 

 

Then...you select the item from a gallery (will work better than the dropdown but you could use the dropdown to work the same way if you want) and the OnSelect=Set(varSelected, ThisItem.ID)

 

The item for your Form would then be LookUp(SharePointList, ID=varSelected)

@poweractivate Thank you for the followup. I got as far as step 4 - not clear how to "not show the ID of the data source entries", is this via the properties or advanced tab of the Drop Down properties. Also, will this approach work for cascading drop downs? This is my use case.

Hi @rebeccas , thank you for your reply. I am not using SharePoint as a data source but SQL Server, would this method OnSelect work all the same?

rebeccas
Community Champion
Community Champion

Yes, I have a few apps that are tied to SQL and the OnSelect and LookUp works exactly the same. I rarely use the Gallery.Selected...it is unreliable and for sure doesn't work well across screens. A lot of time it will click back on the first item in the gallery instead of holding where you selected.

Im inclined to try this outside of a gallery and apply in my cascading drop down use case, then try using gallery after Ive dialed in the method.

 

So I tried setting the OnSelect and Item options in the manner you pointed out above - I may be missing something on the formula in Items. The "=" has a red error underline. Im very new to PowerApps with very little formula use, the errors, Invalid argument type is ambiguous.

 

2020-01-14 21_12_25-Ranch - Saved (Unpublished) - Power Apps.png

 

Im at a stand still and not coming across this exact case, any guidance from here is appreciated.

rebeccas
Community Champion
Community Champion

Let me try again in case I didn't explain it well enough. 

 

In the gallery you have on the icon for the OnSelect put:

 

Set(varSelected, ThisItem.ID) --- then click it after you put it so your variable is set

 

On your Form set the DataSource to your SQL Table and then put the Item as:

 

LookUp(SQLTableName, ID=varSelected)

 

This is setting a variable when you click the icon in your gallery that I named "varSelected" (but you can name whatever you want) in this example and your variable value will be the ID of the item that was selected in the gallery. Yours might not be ID, whatever your ID field you have is what would go here.

 

For the form the data source is set and then for the "Item" of the form your telling it which item you want to see by using the LookUp. For the Look up it I saying LookUp(ThisDataSource, FieldInDataSourceName = (that is the same) varSelected (as my variable I set).

 

When you use this you don't even have to have the same data source for your gallery and form...just a field that is the same. I use this a lot of when I want to select an item from a gallery and then display details in a form that has a different data source but a common tie point. But it also works well for when the gallery and form have the same data source. It will also work across screens because it is global variable. 

 

I hope this helps, let me know if not.

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.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,046)