AnsweredAssumed Answered

Working with a found set in one table, how do I reference specific fields in a specific record in another table?

Question asked by priceski on May 18, 2018
Latest reply on May 18, 2018 by priceski

I'm reasonably certain there's five ways to do this but I'm trying to keep things simple so I'll ask this in the context of HOW I'm trying to do it...


Context: I have a DB where I'm trying to send an email to email addresses in a found set of "customer" records. I have a table of  thousands of "customers" and a table of about 10 emails. I'm using the BE_SMTP_Send function in the Base Elements plug-in (love it, btw) and I'm not struggling with getting the plugin to work, but I am struggling to figure out how to best cobble the email together. Creating a relationship between the two tables didn't seem intuitive or necessary but if that's the way to do it then OK. But I'm basically dealing with a found set from one table and the contents of multiple of fields in a single record of another table.


Bottom line, I need to send one of the emails contained in one of the records of the email table to a found subset of my customers table and then paste into a field in the customer DB when that email was sent. There's a field in my customer table for each of the emails (mostly).




Table 1: Records of "customers" with multiple fields including email address and a number of fields that documents the date a particular email is sent. Each of the emails will be sent once to each customer.


Table 2: Records that contain each of the emails with fields for each of the subject, text message and html message. 10 records total.


I've written the below script but realized I don't know how to specify which record out of the email table (table 2) to reference.


Set variable [BE_SMTP_Server ( {server stuff in here} ) ]

Go to record/request/Page first


     Set variable [BE_SMTP_Send ( "name <>" ; customer_table::contact_email_address ; email_table::email_subject ; email_table::email_content_text ; "" ; "" ; "" ; email_table::email_content_html ; "" ) ]

     Insert current date [Select ; {table::field} ]

     Go to record/request/Page [ Next; Exit after last: On]

End Loop

Show Custom Dialog [ {message ; message} ]


Sending this to the FMP universe...  Thanks.