Email List Calculation

[Jim Gill]

Hello,

I'm have an issue sending emails via SMTP Server using a calculation field in the "To:" field.  When I use the calc my outgoing emails are filtered as spam by the hosting service.   If I put the Email Field in the SMTP To:  emails are sent successfully.

Here is the setup:  

I have a Parent Table with the fields Email_Parent1, Email_Parent2, Email_Parent3 and a related SuperParent_Email.

Parents can opt-out/in of mailings by selecting flag_P{1-3}_Mailings.

I'm trying to to get the field Email_List output to look like this:

• [email protected]; [email protected]; [email protected]; [email protected]

alternatively if a record is missing an email:

• [email protected]; [email protected]

What I'm getting is this:

• [email protected]; [email protected]; 
• Note the semicolon at the end

Here is what I have for a calculation for the field Email_List 

List 

(

Case ( flag_P1_Mailings=1; 
  If ( IsValid (Email_Parent1);Email_Parent1 & 
  If ( IsValid (Email_Parent2);"; ";"")));

Case ( flag_P2_Mailings=1; 
  If ( IsValid (Email_Parent2);Email_Parent2 & 
  If ( IsValid (Email_Parent3);"; ";"")));

Case ( flag_P3_Mailings=1; 
  If ( IsValid (Email_Parent3);Email_Parent3 &
  If ( IsValid (__ParentData_SuperParent::SuperParent_Email);"; ";"")));

Case ( IsValid (__ParentData_SuperParent::SuperParent_Email);__ParentData_SuperParent::SuperParent_Email;"")

)

 

Can someone help me clean this up please?  

Thanks so much!

Jim

[Fitch]

First of all, don't use IsValid when you're actually testing for IsEmpty. I'd suggest further that this is unnecessary here, because List will remove the blanks for you.

Now to answer your question, you're on the right track with the List function, but you don't need to do all that calculating of the trailing semicolon. Just wrap the List calc in a Substitute function, replacing ¶ (carriage returns) with semicolons.

I'll go a little further and suggest that you put all the email addresses in a separate table. That way you just need one email field and one flag field (and maybe a 'sort' field). You could then grab them all through a (possibly sorted) relationship* like so:

Substitute( List( flaggedEmails::email ); ¶ ; "; " )

 

*Or you could do it via ExecuteSQL.

[Jim Gill]

Thanks Fitch - that was exactly what I needed!  Sometimes I over complicate when simple is much better.  Here is what I ended up with:
 

Substitute(
List(

If ( 
     flag_P1_Mailings=1; Email_Parent1);
If (
     flag_P2_Mailings=1;Email_Parent2 );
If ( 
     flag_P3_Mailings=1;Email_Parent3 );
If (
     not IsEmpty ( __ParentData_SuperParent::SuperParent_Email);__ParentData_SuperParent::SuperParent_Email))

; ¶ ; "; " )

9 hours ago, Fitch said:

I'll go a little further and suggest that you put all the email addresses in a separate table. That way you just need one email field and one flag field (and maybe a 'sort' field). You could then grab them all through a (possibly sorted) relationship* like so:

Are you talking about a self-join TO or a new Table?  I see the logic however my solution has many references/dependencies to _ParentData::P{1-3}email and to move the email addresses to a new Table::field would be a huge job.  Am I not understanding?   

Either way - thank you!  You made my job a lot easier.

Jim

[Fitch]

Glad I could help. And yes I meant a new table. Any time we see something like field1, field2, field3, that's generally a red flag that the database structure might need rethinking.