10 Replies Latest reply on Mar 30, 2009 1:11 PM by comment_1

    multiple container fields with multiple files

    trialuser1111

      Title

      multiple container fields with multiple files

      Post

      Apologies if this is posted someplace else, but I couldn't find an answer in my searching.  I am in the middle of a 30-day trial for FileMaker Pro 10, and the next feature I need to learn is container fields.  For my purposes, I need to be able to link or attach multiple files (basic formats, PDF, DOC, and XLS) to various records in my database.  Users will need to be able to look up a company (record), and see the files that have been linked to that company.  I have figured out how to add single files to records, but when I go to add a second the first appears to be replaced.

       

      Assuming this is possible, the next question is: What is the most efficient way to load/link and assign files to the right containers?  That would just be icing on the cake.

       

      Thanks 

        • 1. Re: multiple container fields with multiple files
          DonorTracker
            

          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.

          • 2. Re: multiple container fields with multiple files
            comment_1
              

             


            DonorTracker wrote:
            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.

            • 3. Re: multiple container fields with multiple files
              trialuser1111
                

              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?

              • 4. Re: multiple container fields with multiple files
                trialuser1111
                   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?
                • 5. Re: multiple container fields with multiple files
                  comment_1
                     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).


                  • 6. Re: multiple container fields with multiple files
                    trialuser1111
                      

                    Alright that makes some sense to me conceptually.  I'll go through the tutorial and read up to see if I can implement it.

                     

                    Thank you. 

                    • 7. Re: multiple container fields with multiple files
                      trialuser1111
                        

                      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. 

                      • 8. Re: multiple container fields with multiple files
                        comment_1
                          

                        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.

                        • 9. Re: multiple container fields with multiple files
                          trialuser1111
                            

                          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? 

                          • 10. Re: multiple container fields with multiple files
                            comment_1
                              

                            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).