1 2 Previous Next 17 Replies Latest reply on Jun 14, 2011 4:49 PM by MatthewPiscitelli

    Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?

    MatthewPiscitelli

      Title

      Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?

      Post

      I wish to create a relational database.  I have a series of records that list information relevant to archaeological proveniences.  For example, the site, year excavated, unit, and level.  For each provenience, there are a series of analyses that were performed (e.g. botanical analysis, lithic analysis, pollen analysis).  For each analysis there are one or more documents of different types/formats. 

      Therefore, I would like to create a record that has the provenience information and then has individual fields for each type of analysis (botanical, lithics) that links to the other documents.  In order words, if I clicked on "Botanics" an Excel spreadsheet, PDF, Word document would pop up.

      Any help would be greatly appreciated!  Thanks.

        • 1. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
          philmodjunk

          Set up a related table for attached documents with a single container field defined to store one document per record. Add other text fields to identify the name and type of documentation as needed.

          With a portal, you can than display this list of documents. If you use Insert File with the store by reference option to insert each document, you can open them by double clicking the container field. In a Script, go to field [select] can also open the file. If you choose to store a physcial copy of the file instead of the file path "reference" to it, you can still open the files by using export field contents--an option you can select from the edit menu or perform in a script.

          The advantage to your related table/portal is you can now attach any number of documents that you want to any given record in your database.

          • 2. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
            MatthewPiscitelli

            Ok, so I have a record in one table listing the provenience (Site, Year, Unit).  Then in a related table (a new one entirely) I have the same provenience information (Site, Year, Unit), but also what type of analysis it is (e.g. Botanical) and then a container for the document.

            Thus, one table would like like this:

            Site: CAB

            YEAR: 2007

            Unit: 1

            Botanics: Yes

            Then the related one:

            Site: CAB

            Year: 2007

            Unit: 1

            Type of Analysis: Botanics

            Container: PDF

            I would then set up the portal between what?  Sorry, I am a new user.

            • 3. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
              philmodjunk

              Instead of using the Provenance fields, add an auto-entered serial number to your first table. Add a matching number field to the documents table so that you can set up this relationship:

              YourTable::PrimaryKey = Documents::ForeignKey

              Double click the link between these two tables in manage | Database | Relatinships and enable "Allow creation of records via this relationship" for the Documents table.

              Eliminate Site, Year and unit fields from the documents table as you don't need them here.

              Now you can place a portal to Documents on the layout for "YourTable". (I've used the name "YourTable" here as I'm not 100% sure I know what one record represents here. Subsitute your more meaningful names for YourTable, PrimaryKey and ForeignKey.)

              If you click into the container field in the empty bottom row of this portal, you can insert file to insert a new document into a new record and FileMaker will update the ForeignKey field with a matching value from the current record's primary key. (That's the advantage to enabling "Allow creation of..." for this relationship.

              You might want to read up on Portals in FileMaker help or any other training materials you have to further your understanding of this set up--which is an extremely common way to work with multiple related records in Filemaker databases.

              • 4. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                MatthewPiscitelli

                This has been of a great help!  I have a more clearly defined plan and I will continue working on this.  If I encounter more questions I will surely respond to this forum.  Thanks you for your helpful and rapid response!

                • 5. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                  MatthewPiscitelli

                  I have created two tables.  One, called "Source," lists all of the information that I would like for the record.  The second table, called "Analysis Documents," will contain all of the files that I wish to attach to the "Source" records.  I have created a relationship between both tables whereby the "ID" field in the "Source" table (auto-entry serial numbers) is connected to the "ID" field in the "Analysis Documents" table (I have Allowed for the creation of... in the "Analysis Documents" side of the relationship).  I have also set up a portal in the layout view for the "Source" table that shows the attached documents in the "Analysis Document" container field.  There are 10 rows in that portal reflecting the maximum number of attached documents for any one record at this point.

                  Here is my final question, and it deals with data entry: The "ID" field in the "Source" table is automatically generated.  However, when I add documents into the "Analysis Documents" records, do I have to manually enter the "ID" there to reflect the same "ID" number autmatically generated in the "Source" table?  Thanks.

                  • 6. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                    mgores

                    No, that number is automatically entered in the the child table, Analysis Documents, when you create a new record by adding the file in the portal.

                    If you add a scroll bar to the portal, you will not be limited to 10 rows, every time you add a record there will be a space below it to add another.  There is no limit to how many documents you can associate to each record in your Source table other than memory and disk space.

                    • 7. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                      MatthewPiscitelli

                      Ok, thanks Mark.  So I add the "File reference" directly in the Portal that I play in the "Source" table layout.  Then the record in the "Analysis Documents" will be automatically generated?  But, then I would have to go to that record in the "Analysis Documents" and enter the analysis type, right?

                      Also, if I add a portal, leave the number of rows on default, but then just add the scroll bar, I won't have to worry about specifying the maximum number of documents I will add ahead of time?

                      • 8. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                        philmodjunk

                        No need to worry about the maximum number of documents. You should be able to work with your documents directly in the portal if you add all the necessary fields to it. (And the ID field need not be in the portal.)

                        You can click into the container field and use insert file to insert a reference to the document. You can then double click the container field to open the document. No reason this can't take place all in the portal. You can even delete document records from the portal if you enable that option in portal setup...

                        • 9. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                          MatthewPiscitelli

                          So I have added the portal and by adding the file reference directly in this portal that is situated in my "Source" table, it should created a corresponding record in the "Analysis Documents" table with the actual file in the container field in the "Analysis Documents" table.  If I have formed a relatoinship between two ID fields, then the "Source" ID which is auto-entered should also appear in the appropriate ID field in the "Analysis Documents" table?

                          My other question refers to any other fields in the "Analysis Documents" table.  Say these records are generated, if I want to fill in the other fields for "Analysis Documents," I have to do this manually, correct?

                          • 10. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                            philmodjunk

                            Yes, you'll need to fill them in manually. If it's a manageable number of fields, you can add these to your portal so that you can use the portal to edit them as well.

                            I think, at this point, you should try inserting some documents and see how this process works for you.

                            • 11. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                              MatthewPiscitelli

                              I am working on that right now.  Thanks for your help, updates later today.

                              • 12. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                                MatthewPiscitelli

                                SUCCESS!  I have successfully created a relationship database that links "Source" records to a separate table that stores "Analysis Documents."  I am just fiddling around with the layout options and thinking of easy ways to input the data (it's a whole other issue of running through all of my files and seeing what I have).  But this was all for a relational database I am putting together to organize/manage 10 years worth of archaeological research.  There is plenty of application of the software to such ventures and I would be more than happy to share the results of my research in Peru with either of you.  Just let me know.  Thanks for your help!   

                                • 13. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                                  mgores

                                  Good.  other suggestions for making the data entry easier

                                  make the container field a button that runs an insert file script and have the first step be to check that it is empty.  With the script you also can specify to insert as reference so that you don't have to remember to check that each time.  You can add a little button next to the container that will allow you to delete or replace the file if needed.

                                  If the database is going to be shared on a server, you need to have all the files on the server too.  A reference to a file on your computer won't be accessable to someone else on the network.  In similar applications here I wrote a script that takes the file, saves it to the folder on the server then re-inserts itself as a reference to the server version of the file.  I also add the recordID number to the filename to keep files with the same name from overwriting older files.

                                  • 14. Re: Attaching multiple documents of different types (e.g. .xls, .pdf, .doc) to a single record?
                                    MatthewPiscitelli

                                    Well then here is another potential issue.  Right now, the FMP file and the files that I am inserting and referencing are part of these documents on an external hard drive.  If I copy and paste that whole set of files to another external hard drive, will the paths to those files be maintained?

                                    1 2 Previous Next