SQL Lookup on Drop Down

[bobed]

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!

[Shanescows]

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

[timl · Accepted solution]

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