Drop Down with id and name doesn't retrieve the right value from the database

[SSISJoost]

Hi I have a form and one of the fields/columns is a number (FK_Sector). However it is a foreign key to an other table with two columns (SectorID and SectorName). Therefore I changed the field to a Drop Down list that gets its values from the Sector table. It should show the SectorName, but store the SectorId in FK_Sector.

When I select the SectorName column as value for the Drop Down it shows me the name in the Drop Down. Because I use the SectorID column in the Update property of the data card it saves the number in the table (column FK_Sector).

But when I re-open the form it always shows the first item in the Drop Down list and not the actual value from the database. When I use SectorID as value instead, it does work and shows me the actual value from the database column... But I don't want a DropDown list with numbers  Any suggestions?

[Anonymous]

I am not sure if I understand well your problem but if your dropdown is pointing to a different table (Sector table) from the rest of the item it is not going to show you the value of that particular item (you are not saying what to show on that dropdown). It seems that it works with the Sector Id only because on the default value of your dropdown list you have 'ThisItem.FK_Sector'

I think a good solution could be to record both values (id and name, 2 columns) on your destination table and then on the Default value of your dropdown add something like 'ThisItem.FK_SectorName' assuming you will call the sector name column  FK_SectorName.

Otherwise, I would just add a new Label to the Datacard to show the Sector name (you would need to use a lookup using the FK_Sector on your Secor table to get the value)

Lookup(SectorTable, ThisItem.FK_Sector=SectorID,Sector Name)

and then activate the drop-down if I want to edit (eg. add a button that makes visible the drop-down and invisible the new viewing label) or something like that

[BitLord69]

You have to set the Default value of the DropDown using Lookup, the way @AnonymousKOM suggests, but without using an extra label. Replace what you have today, FK_Sector according to your screen dump, with the Lookup and it will work.

Good luck!

[SSISJoost]

Hi KOM_4410,

Just to clarify, this is my database model (SQL Server).

I don't want to store the SectorName in my Assignment table... I only want the SectorID.

Storing the correct value (SectorID) from the Drop Down in the Assignment table works. I see the correct value in FK_Sector. However, when I edit the form it always shows the first item from the Drop Down. Is there are property on the Drop Down that I can override with a expression to get the correct value?

Something like

LookUp('[dbo].[Assignment]'; AssignmentID = Assignments.Selected.AssignmentID; FK_Sector)

[SSISJoost]

Hi BitLord69,

I tried setting the Default property with an expression (and even a hardcoded value like 20), but it still always shows the first item from the Drown Down.

[SSISJoost · Accepted solution]

Solved it:

Default: LookUp('[dbo].[Sector]', SectorID = ThisItem.FK_Sector, SectorName)