1 2 3 4 Previous Next 50 Replies Latest reply on Dec 16, 2015 7:09 AM by burghfan

    Portal & Relationship Question

    burghfan

      I am having trouble in our solution adding a a relationship between a "Products" table and a "Documents" table and i'm hoping someone can point me in the right direction.

       

      Currently the "Products" table is related to a "Manufacturers" table with a Primary Key from the "Manufactures" table to a foreign key in the "Products" table: ID_Manufacturer = id_manufacturer

       

      the "Estimates" table picks up the "Products" table in a Table Occurrence via foreign key fields (id_manufacturer) in "Products" tables: id_manufacturer = id_manufacturer

       

      to generate a new Estimate we utilize a portal from a "Estimates Line Items" table using PopOvervButton with a select a product via a portal and a script that populates the portal rows. The portal with in the popover is setup to show only products via a specific "Manufacturer"

       

      I tried to more or less duplicate the above steps in using a "Document Line Items". but I have run into the following issues, which I believe may require I add another Table that would bring in information from both "Products" and "Documents"

       

      *** The purpose behind my wanting to add a "Documents" table is that we currently store four (4) PDF's through the use of container fields for each product. Many products have the same PDF, and I was hoping that by using a "Document" table I could slim down the size of the "Products" table allowing multiple products to share the same PDF.

       

      I can get the portal in the PopOver portal to only show "Documents" however my script fails; depending on the how I set the "
      Set Variable" and "Value" the following occurs; nothing, every product for a specific "Manufacturer" gets the same "Document", adding a second document deletes the previous portal rows.

       

      In the portal "Estimate Line Items" a portal row with information from the "Products" table can be deleted without deleting the product record.

       

      I think I've tried so many variations on this that I can't even think straight.

       

      The attachments show screens shots of the scripts that works for "Add a Product to Estimate" and the non-working "Add a Document to Product"

        • 1. Re: Portal & Relationship Question
          Mike_Mitchell

          burghfan wrote:

           

          *** The purpose behind my wanting to add a "Documents" table is that we currently store four (4) PDF's through the use of container fields for each product. Many products have the same PDF, and I was hoping that by using a "Document" table I could slim down the size of the "Products" table allowing multiple products to share the same PDF.

           

           

          An excellent goal. You just need to modify the data model to compensate.

           

          What you have is called a "many-to-many" join. One document can apply to many products; one product can have many documents. Hence, you can't implement this directly using a single relationship with a unique parent and child key. As you've discovered, you wind up with a mess.

           

          There are two methods for implementing a many-to-many join in FileMaker:

           

          1) Join table. You insert a table in between the two parent tables, where each record represents the unique combination of keys from the two parent tables (in this case, Products and Documents). You attach and detach records in the two parent tables by adding or deleting fields in the join table. So your relationship looks something like this:

           

               Products ---< ProductDocumentJoin >--- Documents

           

          (Essentially, a join table converts the many-to-many relationship into a pair of one-to-many relationships.)

           

          2) Multi-line keys (or simply multi-key). In this configuration, you use a return delimited list of keys on the parent and child side. To attach / detach records, you add /.remove key field values in the key fields. So for example, your Products would have another field (not the primary key) for "DocumentsKey", and the Documents might have a field "ProductKey". Each of them is then populated in a list:

           

          Products                              Documents

          1                                        2

          2                                        3

          4                                        5

           

          (Using serial numbers, formatted as text - because multi-keys only work on text fields.) In this case, the records match because the "2" appears on both sides. But the Products record will match any Documents record having a 1, 2, or 4 in its ProductKey field, and the Documents record will match any record in Products having a 2, 3, or 5.

           

          This method allows you to avoid having the in-between table, but is harder to manage and doesn't play well with certain functions (like ExecuteSQL ( )).

           

          You can get further information about many-to-many joins from the FileMaker Training Series (pay special attention to the Data Modeling chapter).

           

          HTH

           

          Mike

          • 2. Re: Portal & Relationship Question
            burghfan

            Hi and thank you

             

            I am trying too implement option one; but I am still having a few issues;

             

            I have place a join table between the "Products" table and the "Documents" tables

             

            Should the "Join" table looking like this:

             

            "Products Table" -> PK_ID_Products = fk_id_products 

             

            "Join Table"


            fk_id_documents = PK_ID_Documents <- Documents Table"

             

            Or should the foreign keys be in the "Products Table" (fk_id_join) and the "Documents Table" (fk_id_join) with the Primary Key (PK_ID_Join) in the Join Table"?

            • 3. Re: Portal & Relationship Question
              burghfan

              Hi Again

               

              Option one works, is there a way to use a drop down list that would only have documents related to a particular manufacturer show up in a "ID_Document" field?

              • 4. Re: Portal & Relationship Question
                Mike_Mitchell

                Short answer is yes.

                 

                Longer answer is: How are Manufacturer and Product related? Are they in the same table, different tables?

                • 5. Re: Portal & Relationship Question
                  Mike_Mitchell

                  Sorry, just re-read your original post. Yes, they're related in a (presumably) one-to-many relationship.

                   

                  In that case, you will likely need to create a new TO for your join table and link it directly to the Manufacturers table via the product primary key. (You may also need to connect a new TO for the Documents table back to this new join table TO if the data you want in the list live in Documents.) Then you can create the value list from the context of Manufacturer, related records to Documents only.

                  • 6. Re: Portal & Relationship Question
                    burghfan

                    Thank you very much, I believe I have it working correctly.

                     

                    The biggest issue I had was placing the "Join" table to the left of the document table which was to the left of the product table.

                    • 7. Re: Portal & Relationship Question
                      burghfan

                      Actually getting my drop down list to only show document records from a specific "Manufacturer" is not working, it still shows all documents.

                       

                      When creating my drop down list as soon as I add the the filter for the "Manufacturers" table the drop down list is empty.

                       

                      I have a TO from the "Document" table related to the "Manufacturer" table via the PK and FK manufacturer fields.

                       

                      What am I missing? Is this where the second TO of the "Documents" table comes in? if so what PF and FK fields would I use?

                      • 8. Re: Portal & Relationship Question
                        burghfan

                        I got it working I mis-read you suggestion of the TO for the "join" table and was using the "documents" table.

                         

                        Long 2 days!

                         

                        Thank you for your response and patience

                        • 9. Re: Portal & Relationship Question
                          burghfan

                          Well I spoke to soon.

                           

                          When I switched to a "Product" record that is related to another "Manufacturer" the only "Documents" available were for the Manufacturer that I was working with previously.

                           

                          Any suggestions on what I did wrong?

                          • 10. Re: Portal & Relationship Question
                            Mike_Mitchell

                            Post your relationships graph.

                            • 11. Re: Portal & Relationship Question
                              burghfan

                              Substitute Principal for Manufacturer (we use the term Principal as opposed to Manufacturer

                               

                              Screen Shot 2015-12-14 at 5.08.52 PM.png

                              • 13. Re: Portal & Relationship Question
                                burghfan

                                I did post those. I believe what is happening is that the Value List for by  "ID Documents” Drop Down List is not refreshing.

                                 

                                 

                                Regards

                                 

                                Dan Nelson

                                • 14. Re: Portal & Relationship Question
                                  Mike_Mitchell

                                  I can't exactly follow what you did there, but this should help:

                                   

                                  Screen Shot 2015-12-14 at 5.52.10 PM.png

                                   

                                  You were right to include the id_principal in the join table; it's needed to make the connection (I was wrong earlier when I said product; you have to join back to the parent table).

                                  1 2 3 4 Previous Next