email addresses from a found set to another db

[gnarlydude]

I have been wrestling with this for a few days now and I am not even sure where to start. I have two databases; one with all my sales people in it and another set up as an email database. What I want to do is to be able to find a group of sales people, all of them in Iowa for example, and then export or somehow get all of thier email addresses into a single CC field in my email database. I can then compose my email message and the "Blast" email them all at the same time. I am using SMTPit for the actual emailing and I have no problem emailing individual sales people through the relationships I have set up between the two databases. I just can't figure out how to email to a found set of records. Anyone have any suggestions?

Thanks,

Joe

[djgogi]

Assuming that there's an rel 1-many from sales to email DB and that is established by unique IDs

In sales people create an global field gID.

Define rel emailRel::gID-->ID

Define value list "emailList" in sales as values from field email( or whatso) from relationship "emailRel"

define calc in sales

cc=Substitute(ValueListItems(Status(CurrentFileName), "emailList"), "PP", ", ")

Now, after creating found set sales perform the following Script

Go to layout (layout with only ID on it)

Copy All records

Go to Layout (with gID on it)

Paste (gID,select)

That's it

Dj

[gnarlydude]

Thanks DJ for your reply. Unfortunatly I have no idea what you are telling me to do here. So I'll break down your reply line by line and tell you what I did

DJ: "Assuming that there's an rel 1-many from sales to email DB and that is established by unique IDs"

I have the relationship set up by Salesperson ID in both databases. So, if I want to email just one person I hit the "Email" button in SalesDB and the Salespersons ID number is pasted into a corresponding field in the EmailDB. That way I can track each email to any particular salesperson through a portal back in SaleDB. I think that this might be part of my problem.

DJ: "In sales people create an global field gID."

I did that and assumed that it would be a text field?

DJ: "Define rel emailRel::gID-->ID"

I am assuming that you meant for me to define a relationship in SaleDB called "emailRel" that is based on the gID field in SalesDB and the ID field in EmailDB, which is what I did.

DJ: "Define value list "emailList" in sales as values from field email( or whatso) from relationship "emailRel""

This is where I started to confuse things I think. I defined the value list in SalesDB like you said and named it "emailList". I wasn't sure what the rest of your sentence was trying to tell me so I am sure that I selected the wrong field in EmailDB. Also, there are no values in any fields in the EmailDB to base a value list on. What am I doing wrong here?

DJ: "define calc in sales

cc=Substitute(ValueListItems(Status(CurrentFileName), "emailList"), "PP", ", ")"

For this step I defined a new calculation field in SalesDB called "CC" with the above calculation (just curious, but what is the "PP" for and what does it do?).

DJ:" Go to layout (layout with only ID on it)"

Here I created a new layout in SalesDB with only the salesperson's ID field on it.

DJ: "Copy All records"

No problems there

DJ: "Go to Layout (with gID on it)"

For this step I went back to my Original Layout that has all the demographic data for each salesperson on it and added the gID field.

DJ: "Paste (gID,select)"

Did that.

What I ended up with is all the salesperson ID numbers of the found set gets pasted in the gID field. Assuming this is what I want, then what?

Ultimately I want the found set of salespeople's email addresses pasted into the CC field in the EmailDB. Am I on the right track? Can you see where I goofed up? Sorry this is so long winded but I figured that the more descriptive I was in what I did to follow your advice, the better.

Joe

[djgogi]

Yes , I was a little bit telegraphic.

Now, you did everything just fine, except defining the value list.

First, question: Are you using FM 5 or higher?

If so then all you need is an conditional value list, ie an value list that get values from field in related file.

The relationship that you have to use is emailRel defined as you did so.

When you create an value list you have diferent options for doing it, ok?

Select "Use values from field" and click on specify, new dialog will open.

Now here you have two options:

All values

and

Only related values

Select the second one and than from pop-up menu select the relationship to use that is emailRel.

The list of available fields in related file(emailDB) should appear.

Select the one that contains e-mail addresses (this field has to be indexable to make value list functioning, ie no unstored calc , no globals or related fields inside)

Now after performing find in salesperson DB (or after you have created by any mean found set in salesperson DB) perform the script I gave you.

Finally look at calc field cc it should contain all emails separated by comma (if your e-mailer needs different separator than change it in formula)

And finally (finally ) "PP" stand for carriage return so the formula ready to paste is:

Substitute(ValueListItems(Status(CurrentFileName), "emailList"), "

[Pupiweb]

You might use a looping script in the Sales Persons db:

Find the persons you want, then run this script:

Set field (gEmails,"")

//gEmails is a global text field

Go to record first

Loop

Set field (gEmails,gEmails & "PP" & Relation to Email addresses::email address)

Go to record [next, exit after last]

End Loop

Set field (gEmails,LeftWords(gEmails,99999))

//Clears the first return in gEmails

At the end of the script you will have in gEmails the first related email address for each person in the found set, assuming that you don't want to write a person more than once ...

[gnarlydude]

Thanks DJ! I finally got it to work, and it works slick!

Joe