SQL Lookup on Drop Down

(0) Share

Report

Posted on by Community Power Pla...

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Shanescows 2,218 Most Valuable Professional on at

Like (0)

Report

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. 🙂

Was this reply helpful? Yes No
Verified answer

timl 36,393 Super User 2025 Season 2 on at

Like (0)

Report
Hi @Anonymous

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

Was this reply helpful? Yes No