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.
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?
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.
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.
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?
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.
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.
Perfect, all sorted, I forgot to change the reference in the script to the new table.
Thanks again Phil.