1 Reply Latest reply on Jul 27, 2010 8:32 AM by philmodjunk

    How to select what related records to show.,...

    100

      Title

      How to select what related records to show.,...

      Post

      Hi,

      I have a table "Suppliers" and each supplier may have many "SupplierContacts". However, each SupplierContact may be related to many suppliers. So, with my SQL background I created a relatinoship like this:

      [Suppliers :: SupplierID], [SupplierContactsXRef ::SupplierXRefID, SupplierID, SupplierContactID], [SupplierContacts :: SupplierContactID]

      In this way I would normally in access/sql server etc be able to do a join to link in both directions. Do, how the heck to do it in FM? ... I want to have a Suppliers form that has a contacts portal control and all related contacts showing in that control.

      Thanks,

      Allen.

        • 1. Re: How to select what related records to show.,...
          philmodjunk

          The table structure is essentially the same. You define a join table to bridge the gap between Suppliers and SupplierContacts.

          Suppliers---<SupplierContactsXref>-----SupplierContacts

          Suppliers::SupplierID = SupplierContactsXref::SupplierID
          SupplierContacts::SupplierContactID = SupplierContactsXref::SupplierContactID

          You just have to set this up visually in Manage | Database | Relationships instead of in a SQL Join expression.

          Using the above relationship, you can place a portal to SupplierContactsXref on a suppliers based layout and use it to show each supplier contact and you can add fields from supplier contacts to this portal. You can do the opposite if you place a portal to SupplierContactsXref on a SupplierContacts based layout.

          Here's a demo that links "Companies" to "Contracts" in this manner: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html