1 2 Previous Next 17 Replies Latest reply on Mar 22, 2011 2:51 PM by DeShawnJackson

    Question: Link a container to more than one table

    DeShawnJackson

      Title

      Question: Link a container to more than one table

      Post

      Set up:

      Tables:

      Contacts

      Inventory

      Documents

      Preferences: One record

      Previous Relationships:

      Contacts::k_ID_Record = Documents::kf_ID_Record AND

      Contacts::k_1_Contacts = Documents::kf_Type

      Inventory::k_ID_Record = Documents::kf_ID_Record AND

      Inventory::k_3_Inventory = Documents::kf_Type

      etc...

      Where k_ID_Record fields are auto-serial unique IDs and k_1_Contacts, etc fields are global calculations (i.e. k_1_Contacts = 1, k_3_Inventory = 3, etc. ) *The global calculation fields are used in the database to distinguish the "type" of document.

      Problem:

      Some documents need to be linked to both Contacts and Inventory, but some Contacts and Inventory items will obviously have identical k_ID_Record's (i.e. the 1st contact created and the 1st inventory item created). 

      Example:

      Documents Table:

      Document::Container = DocA

      Document::kf_ID_Record = 1<p>2

      Document::kf_Type = 1<p>3

      Instead of DocA being linked to Contact #1 and Inventory #2, DocA will be linked to Contact #1, Contact #2, Inventory #1, and Inventory #2.

      Current Solution:

      The current solution I have come up with is to separate the kf_ID_Record field into separate fields:

      Inventory::k_ID_Record = Documents::kf_Inventory

      Contacts::k_ID_Record = Documents::kf_Contacts

      etc....

      Solution works, but may not be ideal in the future if I need to relate Documents to several tables. Is there a better solution to this?

        • 1. Re: Question: Link a container to more than one table
          Sorbsbuster

          I have a Reference Library of linked dopcuments, where the docs are all 'in' this library table, but may have been linked from Customers, Suppliers, etc.  When I have this set-up I include the originating Table Name it came from and use the tAble Name and the ID as the match.

          • 2. Re: Question: Link a container to more than one table
            philmodjunk

            I would think giving the Documents table a serial ID field as a primary key and then using it in any relational links to other tables would allow you to use the same field for all such relationships for as many other tables as you might need.

            OtherTable::fk_DocumentIDnumber = Documents::pk_DocumentIDnumber

            • 3. Re: Question: Link a container to more than one table
              DeShawnJackson

              The contacts table and the inventory table can both have multiple documents and I have the relationship setup to where documents can be added via a portal relationship in either layout....so the kf_ID fields consist of multiple id values from the respective layout.

              I wish there was a way to relate a repeating field in the following way:

              Contacts::k_ID_Record = Documents::kf_ID_Record[1]

              Inventory::k_ID_Record = Documents::kf_ID_Record[2]

              etc...

              This way I could have one key field to store multiple contact and inventory ids, yet separate them so that item #1 and contact #1 are accidentally related to the same document due to identical id numbers.

              Any suggestion how to do this type of relationship since my "grand" solution is not possible?

              Otherwise, I'm stuck creating kf_ID_1, kf_ID_2, etc.

              • 4. Re: Question: Link a container to more than one table
                philmodjunk

                Sounds like you have a many to many relationship where you can use a join table to link contacts to documents and also inventory to documents.

                Contacts--<Contact_Documents>----Documents

                Inventory---<Inventory_Documents>----Documents

                (For simplicity, I'm leaving out the filtering secondary pair of fields here...)

                Contacts::ContactID = Contact_Documents::ContactID
                Documents::DocumentID = Contact_Documents::DocumentID

                Inventory::InventoryID = Inventory_Documents::InventoryID
                Documents::DocumentID = Inventory_Documents::DocumentID

                To assign documents to a contact, place a portal to Contact_Documents on a contacts based layout. You can add fields from Documents to the portal's row as needed to provide additional info.

                The same method can be used on the Inventory table.

                It may or may not be possible to use the same data source table for both "join" table occurrences mentioned above.

                One method to avoid all the extra relationships needed when the second pair of fields "filters" out records was newly added with FileMaker 11. You can use filtered portals where the filtering expression filters the matching records (by type in your case).

                If you haven't seen it already, here's a demo file for many to many relationships you may want to examine:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                • 5. Re: Question: Link a container to more than one table
                  DeShawnJackson

                  The one thing I hate about the portal filter is that if you choose to display a found count of the number portal records, the found count includes all records from the relationship, not the number of records being displayed in the portal due to the filter.

                  I am looking into the relationship explained above...but I think that's pretty much what I got going, right?:

                  Contacts::Contact ID (Contacts table ) = Contact Docs::kf_Contact ID (Doc table)

                  Inventory::Item ID (Inv table ) = Inventory Docs::kf_Item ID (Doc table)

                  *part in parenthesis is the source table

                  I'm downloading the example now to take a look at it.

                  • 6. Re: Question: Link a container to more than one table
                    philmodjunk

                    Your relationships are different. The join table links the primary key (auto serial number) from contacts to the primary key (auto serial number) from documents. This completely avoids the issue of having a Contact and an Inventory record with the same ID number. It also permits linking the same document record to any number of contacts and any number of inventory records as without conflict.

                    If you don't need to match the same document record to multiple records on the other side of the relationship, you can define serial number fields in contacts and inventory that are distinct from each table. Make them text fields and give them next serial values that start with a different letter. Contacts records can be number c1, c2, c3, c4... and Inventory records can be numbered I1, I2, I3, to give a possible example here. Just make the matching field in Documents a text field.

                    The fact that aggregate calculations such as count ignore the portal filter can be a pain, but there is a work around you can use. Define a summary "count of" field in the portal table. Place a separate, one row portal on your layout (you can use invisible borders so it looks like any other field) with this summary field inside of it and with the same filtering expression. This count will then be consistent with the records in your filtered portal.

                    • 7. Re: Question: Link a container to more than one table
                      DeShawnJackson

                      My brain is still trying to process the difference in the results between your relationship and mine because they seem to come out the same...May come to me later...but it seems I just skipped the step of creating the extra table and put those foreign key fields from your middle table into the Documents table, ultimately creating the same concept...I'll ponder on it a bit

                      It is definitely a many to many relationship. Documents can basically go into any (specified) layout and the same document could be linked to many contacts, inventory items etc. as well as each contact or item being link to several different docs. However, you definitely hit a spark with the c1, c2, part. Since I already have it set up that Contacts type = 1, inventory type = 3, etc. I could define a field so that contact #1 is 1.1 and inventory #1 is 3.1. This way clearly 1.1 does not equal 3.1 in the foreign key such that

                      Contacts::kp_ID (value=1.1) = Documents::kf_ID (value=1.1<p>3.1)

                      Inventory::kp_ID (value=3.1) = Documents::kf_ID (value=1.1<p>3.1)

                      In each layout only the matching value will be displayed.

                      (I chose a decimal instead of letter to keep the field a number.)

                      Thanks for the filter work around. I'll see how it works out. I think I tried something similar, but gave up on it and just fixed the relationship instead using filter, which in that particular case worked out better in the long run.

                      • 8. Re: Question: Link a container to more than one table
                        philmodjunk

                        If it's many to many, you need either a join table or a list of values field (less flexible) or you cannot join the same documents table to multiple contacts records in the same relationship.

                        If I have a record with ContactID = 1 and I link a documents record to it by putting 1 in the Documents::contactID field, how do I also link a second contactss record with ContactID = 345 to it? If I put 345 into the contactID field, I loose the connection to the ContactID = 1 record.

                        Now try it with a join table. Let's say this document record has a documentID of 678.

                        You put two records in the join table:

                        ContactID     DocumentID
                        1                 678
                        345             678

                        The first join record links Contact 1 to document 678. The second join record links contact 345 to the same 678 document.

                        The other option I suggested will not work with a true many to many relaitonship. It's simply a way to put numbers from two different sources of primary key values in the same field without getting duplicates. On further review, If I were doing that, I'd just define two fields in Documents and use one for the ContactID and one for the InventoryID.

                        • 9. Re: Question: Link a container to more than one table
                          DeShawnJackson

                          Yeah that's exactly what I was doing, putting a list values in the field, which led to creating a function to insert and remove values...so this is where I can see that having the separate join table and simply deleting the related record would have a great advantage.

                          And the defining two different fields in Docs was what I had going before, but was becoming a pain when I had more tables to relate...had to add extra field to hold the new IDs then switch up some things in scripts, so that's why I was looking for an easier route...Like for example, when a user wants to delete a document from a contact, I wanted to still keep the document in the Doc table if it had other relationships, but completely delete the doc from the doc table if it was not linked to anything else whatsoever. With that being said, my script needed to make sure all those ContactID, InventoryID, FormsID, etc foreign keys in the doc table were empty before deleting it out of the doc table...See where I'm going with this?

                          If I do the join table, I could probably do a find for the docID, if foundcount 0, (or 1 I guess since it would be the last relationship to the doc), then doc can be deleted from doc table, if foundcount>1, then just delete the related record.

                          • 10. Re: Question: Link a container to more than one table
                            philmodjunk

                            Sounds like you've got it. Wink

                            • 11. Re: Question: Link a container to more than one table
                              DeShawnJackson

                              Thanks a bunch! I think I can find some other ways to use this concept in my database as well. Cool

                              • 12. Re: Question: Link a container to more than one table
                                DeShawnJackson

                                I should be able to add new documents into the Documents table through a portal via this relationship right?

                                Example:

                                Portal = Related (the join table) 

                                Portal fields = Documents ( doc table fields)

                                Relationship

                                Related::Doc ID = Documents::ID

                                -add to/delete Related via relationship

                                -add to Documents via relationship

                                Related::Record ID = Contacts::ID

                                Related::Record Type = Contacts::Type

                                -add to Related via relationship

                                Sometimes documents are assigned to contact, but sometimes I need to add new documents into the database.

                                • 13. Re: Question: Link a container to more than one table
                                  DeShawnJackson

                                  Nevermind, I knew I would figure it out once I posted that...I changed the portal to be the Documents table and it still only shows the related records via the relationship above.

                                  • 14. Re: Question: Link a container to more than one table
                                    philmodjunk

                                    Sometimes you can get smoother results by including an "Add" button that switches to the 3rd table (documents), creates a new record and then uses a variable to bring back the new record's ID number to create the matching record in the join table. (With the portal based on documents, deleting a portal row deletes the document record. When it's based on the join table, deleting the portal row just removes the link between the two tables.)

                                    1 2 Previous Next