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.
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
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
Inventory::k_ID_Record = Documents::kf_ID_Record
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.
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.
(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
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.
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.
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.
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:
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.
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.
I should be able to add new documents into the Documents table through a portal via this relationship right?
Portal = Related (the join table)
Portal fields = Documents ( doc table fields)
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.
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.
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.)