Referencing linked table in script
This is driving me batty. I'm assuming that there's an easy way to do what I want to do, but I can't figure it out.
Say I have two tables, clients and matters. Clients has fields name and email address. Matters has fields number and client. The two tables are linked as Clients::name to Matters::client.
Now, for a current record in matters, I want a script that will send an email to the email address of the linked-to record in clients.
As an example, say there is a client record with name acme and email email@example.com. There is a matter having a number 6789 with client acme.
A user is currently in the matter record with number 6789, and selects the script. The script needs to look up firstname.lastname@example.org from the client record that has the same value for the field name as the matter record does for the field client.
What am I missing??