Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

Email Submit "To" line (loops in formulas)

Email Submit "To" line (loops in formulas)

Rate This
  • Comments 60
Everyone likes InfoPath's email data connection because it lets you collect forms using email only, no other infrastructure required (no need for Windows SharePoint Services, SQL Server, or even a file share). We've built even more Outlook integration in InfoPath 2007 Beta, but since most of you don't have that yet, let me share a tip that will work in both InfoPath 2003 and 2007.
 
The basics: Single dynamic email address
As your probably know, the To and CC line of the email data connection can come from a textbox in the form by using a formula. To do that, just use the Fx button next to the To line in the data connection wizard:
 
 
The trick: Multiple email addresses from repeating controls
Some forms have a list of names they want to send to, but the simple formula above won't work for that.
 
For example, consider a repeating table that looks like this:
 
 
With this data source (note that "person" is repeating):
 
 
So you want to produce this semicolon-separated list of e-mails:
 
 
 
A good instinct is to use the "concat" function, but unfortunately that only works on the first element in a repeating structure.
 
So then comes the team insight: Our "eval" function returns a list of nodes which actually share an anonymous parent. That means you can use one eval functions to create a list of the email addresses, then wrap it in another eval function that gets the parent of that list.
 
Voila, here's the formula to solve the problem:
eval(eval(person, "concat(my:email, ';')"), "..")
 
(Note that "person" can be inserted from the data source, but "my:email" needs to be typed by hand or you'll get an error.)
 
For the curious: Here's how it's done
Let's break down that XPath formula from the inside out:
 
  • "concat(my:email, ';')" - Adds a semicolon to each email address.
  • eval(person, "concat(my:email, ';')") - Loops through each person to create a list of email addresses
  • eval(eval(person, "concat(my:email, ';')"), "..") - Gets the anonymous parent of the email addresses, and converts them to a string.
 
So the end result returns the contents of that anonymous parent, which is a series of semicolon-delimited email addresses. Phew!
 
In summary
We are using two tricks here:
  • The fields returned by eval() all have the same anonymous parent (feature of InfoPath's function)
  • The string value of a parent is the concatenation of all its children (W3C spec’ed)
 
- David Airapetyan (Software Design Engineer) and Ned
Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • What if you do not need to do any concating

    I need to compare what I have in a field with what is in a custom list.  I also need to translate both into lower case so they match.  Here is what I have:

    translate(my:myFields/my:MyCurrentUser, "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz") = eval(eval(/dfs:myFields/dfs:dataFields/dfs:SustainApprovers, "translate(my:myFields/my:dataFields/my:Person, 'ABCDEFGHIJKLMNOPQRSTUVWYXZ', 'abcdefghijklmnopqrstuvwyxz')",".."))

    I get an error when the form loads.  

    Thanks for any help

  • Hi,

    This is wonderful!  It works beautifully for me.  However, I do have a question...is there any way we can add a carriage return after each entry?

    Right now I have this:

    eval(eval(User_Details, 'concat(my:Full_User_Name, ";")'), "..")

    and rather than having each name in the same row in my text box, I'd like them to be in seperate rows, like the repeating table.

  • Hi,

    This is wonderful!  It works beautifully for me.  However, I do have a question...is there any way we can add a carriage return after each entry?

    Right now I have this:

    eval(eval(User_Details, 'concat(my:Full_User_Name, ";")'), "..")

    and rather than having each name in the same row in my text box, I'd like them to be in seperate rows, like the repeating table.

  • Hi Derek,

    If you just want the e-mail addresses to be on separate lines in a text box, then take a look at this post: blogs.msdn.com/.../385577.aspx

    You will need to create that file and add it to your XSN. Then in the expression for concatenating the emails, you will remove the ";" from the expression and select the "crlf" option from this new data connection. It is important that you actually select that item and not just hand enter it. In the end, your expression will look like this when you initially select it:

    eval(eval(eval(attendee[selected = string(true())]/attendeeEmail, "."), 'concat(., @crlf)'), "..")

    Once you click the Verify formula button, it will look like this:

    eval(eval(eval(attendee[selected = string(true())]/attendeeEmail, "."), 'concat(., xdXDocument:GetDOM("Characters")/characters/@crlf)'), "..")

    NOTE: This will only work if you are using the InfoPath client to open your forms. If you are using the browser, the only way to do this is with code.

    One last item: in order for your text box to reflect these on different lines, you will need to enable the "Multi-Line" property of the text box.

    Scott

  • Very helpful - works like a charm!  In my case, I was using a repeating table with only one column, and needed all the values chosen concatenated into a separate field.  This solution worked well.  Thanks,

  • Thanks Scott, I'll give it a go.  I was aware of the carriage return xml file (and am using it in non-repeating tables), but I couldn't get it to work with this eval function.

  • I have tried this method however the value returned is only the first value of the repeating array.

    Example, Name = Mary, John, Ken

    Value returned = Mary; Mary; Mary

    Its note worthy that the field the value is returned to is within the footer of the repeating table. I'm not sure if this makes a difference. I will test some more.

    Thanks,

    Greg

  • Hi there, and thank you for a fantastic post! The only thing is that I also, just like Greg, get the first value repeated instead of the individual values... Any ideas?

  • Dear Scott,

    Thanks a lot for your suggestion on how to eliminate the last ";" in one of the replies able. You really saved me.

    Regards,

    Ramya.

  • I've tried to replace the repeating group with one from a SharePoint list. Can anyone shed some light on this?

  • Good day Scott,

    I have found your description above to be exactly what I was looking for.  I also read through the blog below, and discovered I encounter the same error as Debb66 has.  You have referred her to ther IT team.

    Was there ever any resolution found to this?

    Unfortunatley my IT team won't support InfoPath queries and troubleshooting.  So I am at the mercy of my findings here.

    Can you assist me, please?

  • Hi Charles M,

    Can you share with me what "error" you are referring to?

    Scott

  • My DataSource Looks like this:

    myFields

        RecipientColumn

             RecipientRepeatingTable

                  CCRecipient

             EmailRecipients

    On this form, I have a one-cell table.  This cell contains a repeating table with a single textbox field.  Emails are typed in this field.

    Below this repeating table, I have a single text box that I wish to concatenate all the emails entered in the repeating table above.

    This is the formula entered as the DefaultValue of this textbox field:

    eval(eval(RecipientRepeatingTable, "concat(CCRecipient, ';')"), "..")

    This is the error returned when this formula is validated:

    "../my:RecipientColumn/my:RecipientColumn/my:RecipientRepeatingTable/my:CCRecipient" does not point to a valid location path of a field or group.

    Thank you.

  • Hi Charles M,

    Thank you for the clarification. So I setup a sample XSN like this:

    myFields

      RecipientColumn (Group, non-repeating)

         RecipientRepeatingTable (Group, repeating)

            CCRecipient (Field, Text data type, non-repeating)

    EmailRecipients (Field, Text data type, non-repeating)

    If your data source is setup like this, here is the XPath expression you will need as the Default Value for the "EmailRecipients" field (note: I clicked the "Edit XPath" check box in the formula box so you could see the entire expression):

    xdMath:Eval(xdMath:Eval(xdMath:Eval(../my:RecipientColumn/my:RecipientRepeatingTable/my:CCRecipient, "."), 'concat(., ";")'), "..")

    What this looks like without the "Edit XPath" checked is this:

    eval(eval(eval(CCRecipient, "."), 'concat(., ";")'), "..")

    So if your data source is indeed exactly what I have described above, you can copy/paste the expanded version of the expression above directly into your XSN.

    I hope this helps!

    Scott

  • I was just looking for this formula to send multiple email from repeating table and this information is very useful. Thank you.

Page 3 of 4 (60 items) 1234