Send 1 email to multiple addresses based on related records

[fmscoops]

Here's what I would like to do:

I have created a simple form to fill out whenever we receive new shipments for our customers. Once all the information gets filled out, I want to click a button that sends 1 email to all of the techs for that particular customer.

Current setup:

I have a shipments table that pulls data from 2 other tables(external source:mysql) called techs and clients. The first field on the "shipments" table is called customer. Its a drop down menu that auto populates values from the "clients" table. Now the "techs" table has a field called "email" which contains all of the email addresses for each of the techs for all of our customers. Now technically I have it working. When you click the button to send the results it opens up the email client and depending on the customer that is chosen it is correctly filling out the To: field (based on the relationship between the two).

The problem is its only filling out the To: field with one email address. Each of our customers can have anywhere between 1-15 techs (email addresses). I would like to cc: all of the other techs(email addresses)automatically. Is there a way to do this?

Edited March 13, 2010 by Guest

[David McQueen]

When you select a client, it should set up a relationship to techs. Have a script set a global field with the Client email address and then run through a portal to techs gathering email addresses into that global field as a comma delimited list. The results of the Global field are then placed into one of the email locations ( To, Cc, Bcc).

If you want to split it up, collect the client address in one field and the tech addresses in another (To, cc).

[fmscoops]

Thanks for the reply David. I ended up using the Substitute calculation to make this work.