3 Replies Latest reply on Nov 10, 2014 6:58 AM by philmodjunk

    Portals - Join tables beyond Join Tables? Filter related portals?

    mknightnps

      Title

      Portals - Join tables beyond Join Tables? Filter related portals?

      Post

      I already tried to tackle this one way and got a mess. Perhaps that was the way to do it but I missed a critical piece. I'm hoping that there's a cleaner/simpler way to achieve this.

      The solution is for a company that makes special order parts (a classic job shop). The company has many customers. Some products belong to specific customer(s) while others only belong to one customer. Each customer has a set of contacts.

      For products to customers relationship:

      Products  < join table > Customers (Portal01 on Products layout to select customers)

      For products to customers to contacts relationship:

      Products <join table> Customers <Join Table > Contacts (Portal02 on Products layout to select contact)

      This does limit you to only seeing the contact(s) available to customer(s) chosen. However, altering the contact portal on one product affects any other products also using that contact. FM doesn't seem to understand that this is a unique record to a single product. I'm guessing that is caused by the "piggyback" nature of Contacts relationship. I figured I have to set this up this way instead:

      For products to customers relationship:

      Products  < join table > Customers (Portal01 on Products layout to select customers)

      For products to customers to contacts relationship:

      Products <join table>  Contacts (Portal02 on Products layout to select contact)

      How do I filter my contacts portal / ValueList to only show values associated with the customer(s) chosen in the unrelated portal/table?

        • 1. Re: Portals - Join tables beyond Join Tables? Filter related portals?
          philmodjunk

          I have questions about this part of what you describe:

          For products to customers to contacts relationship:

          Products <join table> Customers <Join Table > Contacts (Portal02 on Products layout to select contact)

          Would a given contact ever be linked to more than one customer? If this is never the case, you don't need a join table.

          This does limit you to only seeing the contact(s) available to customer(s) chosen. However, altering the contact portal on one product affects any other products also using that contact. FM doesn't seem to understand that this is a unique record to a single product. I'm guessing that is caused by the "piggyback" nature of Contacts relationship. I figured I have to set this up this way instead:

          But you don't have a unique contact record to a single product. The same contact record is linked to many products in your relationship so altering a field in a contact record will affect what you see for that contact no matter where in your database you acces data from that record. Thus, the database is doing exactly what you designed it to do and I don't quite see why you would want something different.

          But you also haven't identified which tables are the basis for your portals. In many to many relationships, you can base the portal on the join table or on the table that makes up the third table of your relationship, The results can look identical in many cases, but when you start editing data in the fields of that portal or when you add/remove records via that portal, you can get very different results.

          Normally it is best to base the portal on the join table if you will be using it to edit data.

           

          • 2. Re: Portals - Join tables beyond Join Tables? Filter related portals?
            mknightnps

            Would a given contact ever be linked to more than one customer? If this is never the case, you don't need a join table.

            No. However, multiple contacts from multiple customers can be assigned to a product.

            But you don't have a unique contact record to a single product. The same contact record is linked to many products in your relationship so altering a field in a contact record will affect what you see for that contact no matter where in your database you acces data from that record. Thus, the database is doing exactly what you designed it to do and I don't quite see why you would want something different.

            I understand why this is happening as configured. I'm asking where the missing piece is to make it work as desired. One customer has many contacts, many customers have many products, many contacts have many products. The trickery is getting the portal for many contacts to many products to filter based on the customers assigned to the product via the many customers to many products join table.

            Normally it is best to base the portal on the join table if you will be using it to edit data.

            Yes. Both portals were setup on the join tables.

            • 3. Re: Portals - Join tables beyond Join Tables? Filter related portals?
              philmodjunk

              No. However, multiple contacts from multiple customers can be assigned to a product.

              Which would require a join table between contacts and products, not customers and contacts.

              Products---<Product_Contact>------Contacts>----------Customer