What is the most files that would need to be linked to container fields?
If you would never need more than 5-10, for instance, it might be simplest just to create 5 or 10 container fields on your layout. You can size the containers so that just the filename appears, which then won't take up much room on your screen.
Anyway, that's the approach I'm using. I never need more than 4 files linked, so I created 4 container fields. I right-click on the field to add a link.
it might be simplest just to create 5 or 10 container fields on your layout.
That's not a good approach. You should have a related table where each record contains one file.
Thanks. I'll try using multiple containers and see where that gets me. However, it could be more than 10 files for a couple records. How do the behaviors change when you've got multiple container fields? If I wanted to search for a linked file, would I have to search all the container fields separately?
Can you explain this a bit further? I'm pretty new to FileMaker, and have only dealt with a single database. Would a related table be a new database? And how would searching be affected by your method?
I'm afraid there are limits to what can be explained in a forum message. Relationships are the key to Filemaker's power. You really need to do the tutorial and read up on the subject.
In a nutshell, you need to define a table of Companies and a table of Documents. They can (though do not have to) be in the same file. In the Companies table, define a CompanyID field as Number with auto-entered serial number. In Documents, define a CompanyID field as Number. Link the two tables, matching on CompanyID. You probably want to turn on automatic creations of records on the Documents side of the relationship.
Now you have a one-to-many relationship between a company and its files, and you can have ANY number files related to a company.
Your question about searching is right on the money. Although container fields cannot be searched, you can define a calculation field that returns the file's name as text, and search that (along with any other metadata you care to enter about the file).
Alright that makes some sense to me conceptually. I'll go through the tutorial and read up to see if I can implement it.
Alright I've spent a good portion of the afternoon learning about relationships and multiple tables, and it seems to be working pretty well! Thank you!
I have managed to upload a few files to a few firms as a proof of concept, and have extended the idea of portals and relationships to my next item: managing meeting histories. I've got it set up now so that if you look up a company in the right layout, you can see all the meetings each client has had with that company over the years. However, I can't ask everyone to re-enter their meeting histories manually. I realize that once I assign the matching CompanyID to a meeting, it will show up appropriately in the layout, but there must be some way to apply that CompanyID number to every historical meeting record with XXXXX as that company. So right now, if a client has had 6 meetings with Firm XYZ, I need to manually enter the CompanyID 6 times in the meeting history database.
Also, is there a way to take this one step further and have those CompanyIDs get automatically generated as serial numbers across multiple tables to ensure that they match? In the database, I've got a table called "Firm Data" which has the list of all the companies and their CompanyIDs. When I upload a client meeting history from Excel, the firm names used there will generally match the ones used in the database, but the Excel sheet has no knowledge of the CompanyID field. Is there a way to essentially apply a "VLOOKUP" functionality whereby FileMaker recognizes a matching firm name and assigns a CompanyID to that historical meeting?
Thanks again for all the help.
You could define another relationship between companies and history, matching on company name, and use it in order to lookup the CompanyID (even during import).
Note that this is a part of data migration, not of normal operations. I'd probably do all this in another file, and import only 'clean' data into the final solution.
What about an equation? I've tried using FileMaker's equation tools but the learning curve is steep for me on that one. Can I set the CompanyID field in Meeting History to be a formula that matches the Firm Name in Meeting History to the Firm Name in Firm Data, and returns the CompanyID if it's a match?
If 'equation' means calculation, then no, that won't work.
First, Filemaker wouldn't know how to match the names in a calculation. This must be done by defining a relationship (with some exceptions for more advanced tricks - but in any case you need a relationship, if you want to refer to fields from another table).
Second, a calculation that references related fields is forced to be unstored - and therefore unsuitable to serve as a matchfield in relationship (on the 'child' side).