Dropdown selection changes wrong record

[Tobias]

Hello,

 

I've got a problem which is at least related to relationships (!), but I'm not really sure this is the appropriate forum. Fairly sure it's a noob problem though, but I have no idea what to start googling for.

 

I have set up relationships between 3 tables (see attachment), and display data from all of them in a layout (2nd attachment), using the BOM table as 'basis'. The dropdowns are populated with valuelists from the names of projects and companies, respectively.

 

What I would like to happen when I chose a new name for either company or project, is that the relationship updates - that the BOM is now related to the newly chosen company/project in stead of the old one. To be specific, the project_id field of the BOM table should change to match the id field of the new project choice.

 

But what does happen, is that the 'name' field of project/company record in question (the old one), simple changes to whatever new name I've chosen. The result is 2 different records with the same name. Obviously not a feel-good situation.

 

I suspect the problem stems from some missing relationship or perhaps the dropdowns are misconfigured. But I have no idea how to solve it.

Any tips or hints would be much appreciated. 

 

Thanks for your time,

Tobias

[comment]

You should define a value list using values from field Company::id + also displaying values from Company::name. Use this value list to populate the BOM::debitor_id field. If you are showing the related Company::name field on the BOM layout, make it non-enterable to prevent accidental modification.

 

Note: the value list set up has different options of showing the two fields - and the display is slightly different if using a drop-down vs. a pop-up.

[Tobias]

Ah. Now I understand, and it works too! 

 

Thank you