Multiple "froms" in the e-mail body text

[Angela10]

Hello and thanks for taking a stab at this post.

I developed a basic contacts database for a law firm. A one to many relationship exists between a contact and the attorney(s) who know him or her. I would like to use the database to send an e-mail announcement to about 700 contacts. (I have researched the send mail script step and also have the demo of SMTPit, but have never used that plug-in before). So, I want to send one e-mail to each contact, but in the text of the e-mail body indicate which attorneys know this contact via a signature block. For example:

"ANNOUNCEMENT TEXT"

Sincerely,

John B. Smith

Jane M. Doe

Bob E. Johnson

Now, here's the glitch, if one contact is related to 5 attorneys, but only 3 of those attorneys want the announcement sent to that one contact, how do I accomplish only including the names of the three attorneys in the e-mail text signature block? In the related attorney file, I have a text field formated as a check box for use to indicate that they want the announcement sent.

Any light you can shed on this dilema would be useful.

[Fenton]

Create an "If" ID. This is a calculation that only produces the ID when its conditions are true. A relationship to it. A Value List based on the relationship. A calculation using ValueListItems (Status(CurrentFileName), "VL Name").

It is too complex to explain in words, easier to see. I would do it in 7 though; 6 is clunky; I won't use it for complex multi-file examples, sorry.

But there's a question. Does this attorney not want his name on an email sent to this particular Contact? Or to ANY Contact?

If the former, then the checkbox and "If" calculation field has to be in the "join" file between Attorneys and Contacts (which you didn't mention; but some mechanism is needed to join them).

If the latter, NO Contact emails, then the checkbox can be in the Attorney file.

[Angela10]

Well.... I only have FMP6 available. 7 on the way at the firm, most likely next year.

I see what you mean by the join file. Yes, there is a <contact file> <contactattorney file> <attorney file> scenario going on. Originally, just coded attorney initials, now we need all their contact info.

Attorneys have the ability to pick and choose which particular contacts they want the announcement to go to. That field resides in the contactattorney file.

I will take the clues you offered on how to get er dun in 6. Thanks, where there is a will there is a way.

[Fenton]

Since you asked nicely :-/ OK, this is a way to do it in 5. It uses 2 related value lists. One, to the join file to get the IDs, the other using those obtained IDs to go back to the Attorney file and get the names.

There could possibly be a more direct way, using only one VL. But that would involve looking up the Attorney Name into the join file. That would not be necessary in 7. But this works, it's just "double" unstored, so rather slow. But fast enough for email I would think.

BTW, don't use these files for anything but learning. I had to Force Quit once, when I changed the name of one of the 2 value lists, creating some kind of circle that FileMaker couldn't get out of. Oops.

AttorneyContacts5.zip