2 Replies Latest reply on Nov 30, 2014 7:36 PM by miw

    Join Table?



      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?



        • 1. Re: Join Table?

          I don't see a need for the join table if an item can only be a member of one set.


          Set::__pkSetID = Items::_fkSetID

          • 2. Re: Join Table?

            PhilMod - your suggestion works well - no join table needed.  An ancillary question though - I noticed in the latest version of FM Pro there is a summary field calculation for "List of".  I am thus in the Set table trying to get a summary calculation for a list of the ItemIDs in a Set.  However, only the current active ItemID shows up.  For example, if my active Set has links to two Items where Item IDs are #1 and #2, I want to see my summary field showing both #1 and #2.  I have tried to show the summary calculation both summarizing repetitions as "all together" or "individually" but get the same single result.  Am I going about this the wrong way?  How can I get a summary of the ItemIDs that are all linked to an individual Set in one field on the SET table?