AnsweredAssumed Answered

Join Table?

Question asked by miw on Nov 19, 2014
Latest reply on Nov 30, 2014 by miw


Join Table?


I have two tables, "Item" and "Set".  The Set table has a unique "SetID" field and the Item table has a unique field "ItemID".  I have also created a join table, "SetItem" which has "LinkSetID" and "LinkItemID" fields.

The join table is connected as follows:  

Set::SetID >----- = -----< SetItem::LinkSetID as well as 

Item::ItemID >----- = -----< SetItem::LinkItemID.

In the past, I have created join tables whereby the Set table can have multiple records with any combination of Items; I have controlled the Items that can be joined in the Set table via a "cItemID" calculated field whereby the calculation is:  List (SetItem::LinkItemID) & "¶-.1 ".  Then I also have a relationship to a copy of the Item table called "UnaffiliatedItem" ; with this relationship:  UnaffiliatedItem::ItemID >------ ≠ --------| Set::cItemID.  This allows me to put in a Set record any number of Items without repetition of the same Item to a Set and works fine.

Now I want to do something a bit different and cannot figure out how to establish the proper relationship (or is it in the "cItemID" field calculation?).  I want to be able to link Items to Sets in a similar manner but want to limit the ability to link an Item only once to a Set and then no other Sets.  Once an Item is linked, it can no longer be considered to be linked to any other Set.  I am thinking a join table is still necessary, but how can I limit the Item selection so that an Item is linked to a Set and never given the opportunity to link to another Set?