AnsweredAssumed Answered

Properly getting a link amongst two relational tables

Question asked by miw on Jan 30, 2009
Latest reply on Feb 4, 2009 by miw

Title

Properly getting a link amongst two relational tables

Post

I have two tables that are linked to one another.  In order to establish the proper links, following good DB design (I think?), I am not allowing the user to actually edit the linked fields on either layout window.  Thinking that TableOne is the Master table, I have autocreated a unique serial number called TableOneID and a unique calculated field, called TableOnePlace, that concatenates two other input fields in TableOne into one name.  On the TableTwo layout, I want to establish the proper link to TableOne.  In TableTwo, I have a field named TableOneLink which I want to be used to establish the link to TableOne's TableOneID field record.  I want to be able to establish the link by having a pulldown in the TableTwo layout that shows the records in the unique TableOnePlace field.  Once that is selected, I want to record the TableOneLink in TableTwo as the TableOneID that corresponds to the TableOnePlace selection in the pulldown menu.

 

Issue 1:  I thought I could put the TableOnePlace field on the TableTwo layout in order to create the pulldown menu and select the record I want linked.  However, since the TableOnePlace field is calculated, I cannot make the pull down selection work in the TableTwo layout since it cannot be modified.  What is an alternative to achieve this objective of using a pulldown menu to select one of the TableOnePlace records?

 

Issue 2:  Once a TableOnePlace record is somehow selected (after resolving Issue 1), how do I properly get the TableOneLink in TableTwo to equal the TableOneID in TableOne so the link has been properly established?

 

I have struggled with layouts and scripts to help resolve this to no avail.  Any help would be greatly appreciated!

Outcomes