cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agneum
Level 8

Get value from Lookup table?

Using SQL Server hosted on the cloud. I simply want to select an Id behind the scenes in a dropdown from a lookup table. For the user I want the name to be shown. If I set Items to '[dbo].[DepartmentLookup]' and Value to DepartmentId, I only get a list of numbers. If I select names, it is shown correctly for the user, but the values cannot be saved because they are not integers.

 

I'm looking for the follwoing, but for Powerapps + SQL Server:

https://powerapps.microsoft.com/en-us/blog/support-for-lookups/

 

And no, I'm not moving to Sharepoint lists in a million years.

5 REPLIES 5
MelindaK
Level 8

Re: Get value from Lookup table?

Hi,


There are 2 ways of achieving this, that I know of.

The first one is to set the items on the drop down to be your table, and then in advance settings to chose what you want your display value to be. Here is an example of what I have:

dropdown.png

 

The second one which is more complicated, and not ideal as you query the database twice:

The way I am doing this is:

- on the drop down I set the items to be the display value you want, in your case the names.

- then I am using Patch to submit data I'm doing another lookup to get the id of the selected item. For example:

Patch([dbo].[Table], Defaults([dbo].[Table]), { 
	Id:LookUpp([dbo].[DepartmentLookup],dropdownn.Selected.Value = Name).Id,
	Name:dropdownn.Selected.Value
})

 

Hope this helps Smiley Happy 

MelindaK
Super User
Super User

Re: Get value from Lookup table?

Just to clarify, you just want to retrieve the selected value from a drop down control?

If this is the case, you can simply refer to the selected property of your drop down control.

 

Here's an example screenshot. In your case, you would set Items to '[dbo].[DepartmentLookup]', and value to field that you want to display in the drop down. 

 

ss1.PNG

 

To retrieve the selected value, you would use the syntax Dropdown1.Selected.DepartmentId.

 ss2.PNG

agneum
Level 8

Re: Get value from Lookup table?

Thanks!

agneum
Level 8

Re: Get value from Lookup table?

Problem is that I'm in EditForm. How does the datacard know what value to read? And still, it will complian that the string is not a correct value. 

Super User
Super User

Re: Get value from Lookup table?

To configure the drop down to show the correct value when it loads, set the Default property of your drop down the following formula:
 

LookUp('[dbo].[DepartmentLookup]', DepartmentID = Parent.Default).Name

 

To configure the card so that it saves the DepartmentID value to your data source during an update,  select the parent card control, and set the Update property to the following formula:
 

YourDropdownBoxName.Selected.DepartmentID 

 
 These instructions assume that your card is unlocked, and is bound to the DepartmentID field.

 

 

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 503 members 5,158 guests
Please welcome our newest community members: