cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bobed
Level: Powered On

SQL Lookup on Drop Down

I have a simple SQL Azure Database dropdown lookup scenario which doesn't seem to work and is driving me a bit crazy.

 

I have a table called DataStore which looks a bit like this:

DataStore

DataStoreID (PK)

DataStoreName

DataStoreTypeID (FK)

 

I have another table that defines the values for DataStoreTypeID called DataStoreType

DataStoreType

DataStoreTypeID (PK)

DataStoreTypeName

 

I want a form for Data Store that shows a dropdown for DataStoreType, showing the name rather than the ID, showing the currently selected ID with the corresponding name on edit and saving the ID correctly on save - normal stuff for dropdowns, in other words.

 

I've got this working when showing the DataStoreTypeID - the correct value is shown and the value will save. When I configure the DataStoreTypeName to be shown, the dropdown always shows the first value in the DataStoreType table and won't save the value at all.

 

Has anyone got this scenario working with SQL Azure connections?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SQL Lookup on Drop Down

Hi @bobed

 

The answer that I gave in this post might help you.

 

https://powerusers.microsoft.com/t5/Creating-Apps/Drop-Down-Box-Lookup/m-p/82049#M827

 

The pertinent parts are to set the Update property of your card control, and to set the Default property of your dropdown control.

 

Let's assume that the name of your drop down control is DropdownDataStoreType. To configure the card to save the DataStoreTypeID value when a user saves a data store record, select the card control that relates to DataStoreTypeID, and set the Update property to the following formula:

DropdownDataStoreType.Selected.DataStoreTypeID

 
To configure the drop down box to show the correct item when a user opens an existing record, set the Default property of your drop down the following formula:

LookUp(DataStoreType, DataStoreTypeID = Parent.Default).DataStoreTypeName

View solution in original post

2 REPLIES 2
Super User
Super User

Re: SQL Lookup on Drop Down

Yup. It all works and one day I will finish the video that shows it off. 😞 Until then here is what I think you need to know I will try to translate it to your names.

 

For DropDown1 I use:

 

ShowColumns('[dbo].[DataStoreType]',"DataStoreTypeName","DataStoreTypeID")

 

This lets your dropdown show the Name column you want.


Then when you create a new record in DataStore you need to do syntax such as

 

Patch('[dbo].[DataStore]',Defaults('[dbo].[DataStoreType]'), {DataStoreTypeID: DropDown1.Selected.DataStoreTypeID})

 

That is how you do it in a nutshell. One day I will finish the video guide. 🙂 

 

 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
Super User
Super User

Re: SQL Lookup on Drop Down

Hi @bobed

 

The answer that I gave in this post might help you.

 

https://powerusers.microsoft.com/t5/Creating-Apps/Drop-Down-Box-Lookup/m-p/82049#M827

 

The pertinent parts are to set the Update property of your card control, and to set the Default property of your dropdown control.

 

Let's assume that the name of your drop down control is DropdownDataStoreType. To configure the card to save the DataStoreTypeID value when a user saves a data store record, select the card control that relates to DataStoreTypeID, and set the Update property to the following formula:

DropdownDataStoreType.Selected.DataStoreTypeID

 
To configure the drop down box to show the correct item when a user opens an existing record, set the Default property of your drop down the following formula:

LookUp(DataStoreType, DataStoreTypeID = Parent.Default).DataStoreTypeName

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,204)