exporting a list of e-mail adddresses

[KarlaB]

I simply want to get a list of e-mail addresses exported so I can repeatedly send e-mail to members of that list. Ii am currently trying to export to a tab-deliminted file and then opening this in Excel. The addresses are contained in different fields. So, when I export the data I need, I end up with multiple columns in the spreadsheet. This makes it difficult to check for duplicates.

I am open to a varienty of approaches. I have been working on figuring this out for days, and I am sure there is a simple solution.

Thanks! Karla

[QuinTech]

Hi, Karla. You could skip the Excel step by exporting to a tab-delimited file with the "Summarize" option turned on. Sort the records by email address, then export. In the export dialog, click the "Summarize by" button and put a check next to emailAddress. This should give you a unique set of addresses.

Jerry

[KarlaB]

Yes, but. . . Perhaps a few more words about specifics.

I run a small fencing school. My little DB now has about 7 tables, with more than that replicated for setting relationships.

I have a contact table with a contactID (key) and e-mails, phones, etc.

I have a student table with various data. Each entry in the student table has the studentContactID of the student (the key), and up to two others -- one for parent1ContactID and another for parent2ContactID.

I have a ClassList table with records that contain a particularClassID, a studentContactID, and other information.

To obtain a list of students in a particular class, I simply find all entries from the ClassList table with a particularClassID.

I regularly need to send e-mails out to all students in a particular class, as well as their parents.

So, with my found set I can use related tables and export the e-mails from the contact table, using the studentContactID, parent1ContactID, and parent2ContactID.

So far, so easy.

There is no way to sort these on e-mail address, since I have up to three for each record. Moreover, the e-mail for the student is often the same as the e-mail for one (or both) of the parents.

To make things even worse, some parents are also students.

The objective is to get a list of unique e-mails so I can send out a class-related e-mail.

I like the summarize idea. Now, all I need to do is get all of the e-mail addresses into a single field. Right?

Thanks, so much.

I would be happy to find someone who I could pay to provide assistance such as this. Do you or anyone you know provide such services?

Karla

[KarlaB]

Sorry. I see that I am in the wrong area for this question.

Apologies.

[QuinTech]

There is no way to sort these on e-mail address, since I have up to three for each record.

When you say "each record"-- i am guessing that is a record from the Student table. What you might do is, for each table that represents a type of person (Students, Parents, whatever else you have) maintain one email field. Then, on the student table, make a calc field, something like DefaultEmail=

Case ( not IsEmpty ( Student::EmailAddress ) , Student::EmailAddress ,

not IsEmpty ( Parent1::EmailAddress ) , Parent1::EmailAddress ,

not IsEmpty ( Parent2::EmailAddress ) , Parent2::EmailAddress )

This or something similar will give you one email address for each record. Alternatively, you could make your calc:

Student::EmailAddress & Case ( not IsEmpty ( Student::EmailAddress ) , ", " ) & Parent1::EmailAddress & Case ( not IsEmpty ( Parent1::EmailAddress ) , ", " ) & Parent2::EmailAddress

This results in a concatenated field with all addresses you may want to use, in a format usable by most email clients ("[email protected], [email protected]").

But this still gives you dupe addresses in cases where two students from the same family are in one class. A completely different alternative (which may present other problems, but we'll worry about that later) is to keep parents in the same table as students. Relate PersonTO1::ParentID to PersonTO2::ID to indicate a student's parents. Also, relate PersonTO1::ID to PersonTO2::ParentID to indicate a parent's students.

Now, think of students: you can find an entire class by searching PersonTO1::ClassID (obviously). Then, think of parents: You can find all the parents concerned with a particular class by searching PersonTO2::ClassID (because, remember, PersonTO2 looks at a parent's ID number and returns the related students). Do both those searches at the same time, and you now have a set of all people concerned with a prticular class.

Also notice that you have one email address field.

I wish i could do a screenshot of the relationship graph, but my FM7 machine is currently on strike.

J

[QuinTech]

Sorry. I see that I am in the wrong area for this question.

Apologies.

Well, you'll learn the ropes eventually.