8 Replies Latest reply on Jun 13, 2017 2:01 PM by philmodjunk

    How do I add a third table?

    mrc2013

      I have Filemaker ver 15. I have been using Filemaker for many years but only recently have had to create my own relationships between tables. I have a database for accessions (donations). Each accession is a line item with an accNumber, accItemDescription; Date Received etc. So these fields are in my Accessions table. Each accession is an item or group of items given to us by donors. So donor name, address, etc are in a separate table. I have these set up as one- to- many relationship; one accession is given by one donor, but one donor can have many accessions. That one donor can come to use on a different date with a different donation. Some of the donors will get a thank you letter. (I work in an archives. If the staff brings an item to the archives, they do not get a letter; only those that are not staff get a letter.) So do I create a one-to-many relationship (one letter per donor. As I am looking at it, maybe it should be one letter per line item for items that need a letter) OR do I create what I think is called a join table. This is where (I think) you have two tables that go into a third table which presents new information based on the other two tables. In the image below the Inventory table is the Accessions table and the DonorSignForm is the Thank You letter table. The two gray tables at the top are irrelevant, as is the ITEM ID MATCH field. I am using a starter solution and am scared to delete or rename things for fear of messing up the whole database.

       

      AccessionsTables.png

        • 1. Re: How do I add a third table?
          philmodjunk

          Do you want to log that a letter was sent, manage the actual letter content by sending the same "form letter" with data from your solution inserted into the letter, or do you want to send individually "hand crafted" letters?

           

          You might need more than one new table.

          • 2. Re: How do I add a third table?
            mrc2013

            The thank you letter will be a form letter that will pull the donor name and info from the donor table and the accession description etc from the accessions (inventory) table

            • 3. Re: How do I add a third table?
              philmodjunk

              Do you need to track who was sent a letter and when?

              • 4. Re: How do I add a third table?
                mrc2013

                No I don't think so. I might need to check that a letter was sent. So I would look up the accession then go to the letter layout to look at the date created or indicate on the accession layout that a letter was sent. The letter will be printed and handed to the donor, who will sign it and return it to us. We will file it. I might add a field on the letter layout that holds "date letter signed". "Who" sent it will always be the same and that info is printed on the form letter.

                • 5. Re: How do I add a third table?
                  philmodjunk

                  Then yes you do. Some approaches that you might use would not retain a copy of the letter to look up.

                   

                  For hatd copy letters (not email except as attached PDF), you could set up a letter as layout text with merge fields inserting data from your solution.

                   

                  But this his does not necessarily support keeping a record of who received such a letter nor the exact content of that letter. (It's pretty likely that you will decide to alter your "form text" at some point in the future and then you don't know who got the original nor who got the new version.

                   

                  There's a different approach that uses a text field and the substitute function that addresses those issues. It's beyond what I care to describe via iPhone, but I'll check back later and provide that info unless someone else does so before I do.

                  • 6. Re: How do I add a third table?
                    philmodjunk

                    If each donation (accession) could cause a thank you letter to be sent. (Since when does one sign and return a thani you letter?), you would need a different set of occurrences and relationships:

                     

                    Inventory::Accession Number = ThankYouLetters::Accession Number

                     

                    ThankYouLetters::_fkLetterID = LetterText::__pkLetterID

                     

                    This sets up ThankYouLetters as the join table that you asked about. You would create a text field, FormText in Letter Text and type in your basic "form letter" text with placeholder text marking the points where data from your other tables will be inserted into it:

                     

                    Dear <<DonorLastName>>,

                     

                    Thank you for donating <<Description>>  (and so forth....)

                     

                    Then you this calculation can merge form text and data from your solution into a message:

                     

                    Substitute ( FormText ; ["<<DonorLastName>>" ; Donors::DonorLastName ] ; ["<<Description>>" ; Inventory::Description ] )

                     

                    To keep a "snapshot" of the exact text sent, you can put a text field in ThankYouLetters and use either an auto-enter field option or a script to set that field to the value of the above type of substitute calculation. A Layout based on ThankYouLetters can then be used to create a given instance of the form letter.

                     

                    Notes:

                    Should you latter need to edit this letter, you can go to a layout based on LetterText and edit the content of FormText. You will not need to make any script or calculation changes unless your change requires inserting information not originally inserted into the letter.

                     

                    For what you describe here, you'd have exactly one such form letter record in the LetterText table, but this design support having many different form letters each of which can be used for different reasons and for which managing their content need not be a database developer skill level task as long as the message changes don't require inserting new information.

                     

                    In one of my own systems, I've been developing a "Messages" table along with a layout with buttons and scripts for editing such messages. I'm gradually transitioning the solution to move more and more email and hard copy messages into this system and will soon be training selected power users in how to edit the messages that fall within their department's area of responsibility.

                    • 7. Re: How do I add a third table?
                      mrc2013

                      Thank you very much for taking the time to answer my question. I am studying what you wrote and trying to understand it. (And to clarify, the letter started out as a sort-of thank you letter but is now a Deed of Gift Form which the donor signs acknowledging he is giving up rights to the donation.) I have been distracted with problems on my layout. I have a one (donor) to many (accessions) relationships with  a check in the "allow creation of records in accessions table). What I what is to start the data entry on the accessions (child) layout. On that layout enter the donor name then click a button to go another layout where I fill in other donor contact details. Because a donor could already be in the system, I don't know how to stop the user from creating a new record for a new donor and therefore entering the donor in the system twice.

                      • 8. Re: How do I add a third table?
                        philmodjunk

                        It would be better to start data entry on the donors layout. Then the user can do a find for the donor and start a new record when none is found. A portal to accessions could then be used to log new accessions or a button on the donor layout could create the new accessions record on the accessions layout and fill in the donor ID as part of the same script.

                         

                        You could also set up a form to fill out consisting of only global fields. When the user clicks 'save' or 'submit' the script then checks for a donor and creates one if one does not already exist--but this "check" could be both complex and fallible.