8 Replies Latest reply on Jan 13, 2015 1:41 AM by DanielPerry

    Choosing correct email address from field value

    DanielPerry

      Title

      Choosing correct email address from field value

      Post

      Hi Guys,

      First post, rapidly falling in love with Filemaker.

      I am creating an IMS database in Filemaker Pro 13. I have 3 tables, job tickets, clients, and contacts.
      The job ticket will pull a drop down of clients from the client database, and then a drop down of contacts within that client from the contacts database. I will then generate a PDF estimate using data from the job tickets table, and attach to a pre-filled email, using a script. The email pre-fills the to field with an email address. This address will always be the first contact in the contact list for that client, rather than the contact selected from the drop down list on the job ticket.

      Any ideas how I fix this?

      Thanks in advance

      Dan

      Screen_Shot_2015-01-09_at_14.40.29.png

        • 1. Re: Choosing correct email address from field value
          philmodjunk

          If your context is Finger_Print_IMS_V1 or Dummy Clients at the time you need this email, simply refer to Contacts::Email Address and you will be referring to the email address of the "first" contact to be related to the current Dummy Clients record. In the default standard relationship, the "first" related record is the first such record to be created. You may want to set a value in a field in contacts to designate the preferred contact record and then set a sort order on your relationship that sorts that related record to be the first in order to specify that contact record as the source of your email address.

          • 2. Re: Choosing correct email address from field value
            DanielPerry

            Hi Phil,

            Thanks for the speedy reply. You will have to excuse my limited understanding of filemaker (at this stage). I think I follow what you are suggesting. How would I make this work if the contact/email address was potentially different for each job ticket/estimate?

            Cheers

            Dan

            • 3. Re: Choosing correct email address from field value
              philmodjunk

              And what table in your relationship graph represents a "job ticket"?

              Are you now asking a completely different question?

              Either each job ticket is linked to a specific customer and you original scenario holds (and my answer stays unchanged) or you are linking each Job Ticket record to a specific contact in order to specify that contact's email address in order to have

              contact/email address potentially different for each job ticket/estimate

              In which case, you have a different data model than the one shown at the start of this thread.

              • 4. Re: Choosing correct email address from field value
                DanielPerry

                Apologies this is all new to me, I'm doing a terrible job of explaining myself, let me try and break it down....

                • Table - Fingerprint_IMS_V1 holds job ticket info
                • Table - DummyClients holds client info
                • Table - Contacts holds contact info
                • Each job ticket is assigned to a client
                • These clients are pulled from the DummyClient table, and displayed as a drop down using the dummyclients table and company field as a value list
                • Each job ticket is assigned a contact, this is also selected from a drop down, using the contact table and name field as a value list
                • Contacts are client specific, but each client has more than one contact, and a job ticket is assigned to the relevant contact within that client.
                • Estimate PDF is created using information from the above 3 tables. The Pdf is then attached to an email, using the email field from the contacts table.
                • The email address will be for the correct client, but will always revert to the first contact for that client, rather than the specific contact that is assigned to that job ticket.

                Thanks again

                Dan

                • 5. Re: Choosing correct email address from field value
                  philmodjunk

                  The email address will be for the correct client, but will always revert to the first contact for that client, rather than the specific contact that is assigned to that job ticket.

                  That's because you do not have a relationship linking a contact record to a Fingerprint_IMS_V1 record. The link is to a Client record and from there to contact. The link will return the address of the first related contact as that's all FileMaker has at this point.

                  But you can do the following to correct this:

                  Open Manage | Database | Relationships, select Contacts and click the duplicate button. This will create a copy of the table occurrence, but not an actual copy of the table it represents. Link this new table occurrence copy to Fingerprint_IMS_V1 by contact ID fields and you can refer to the correct email address by referring to it from this new table occurrence.

                  For more on the concept of table occurrences, see: Tutorial: What are Table Occurrences?

                  • 6. Re: Choosing correct email address from field value
                    DanielPerry

                    Thanks again for the help Phil, this all makes sense, and I think I have done this correctly. I'm still however getting the first contact email address, rather than the selected contacts email, really weird. Maybe I haven't done it quite right.

                    Cheers

                    Dan

                    • 7. Re: Choosing correct email address from field value
                      philmodjunk

                      If you refer to contacts::Email Address, you will get the results you report.

                      If you refer to contacts 2::email Adddress, you should get the email address of the specific contact specified for the current record in fingerprint_IMS_V1.

                      The difference lies in which occurrence of contacts is used in your reference to data from that table.

                      • 8. Re: Choosing correct email address from field value
                        DanielPerry

                        Perfect, all sorted, I forgot to change the reference in the script to the new table.

                        Thanks again Phil.