Email Submit "To" line (loops in formulas)


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
Comments (69)

  1. InfoPath makes it trivial to track totals for repeating structures, such as customer orders. Just create

  2. voquan says:

    How do I submit the form to the email using the browser version (form services in MOSS) I can do it in infopath but not in the web browser. I uses the email submit option.

  3. abcoura says:

    I’m trying to use the formula shown in the text but it says that the field doesnt exist. And i’m changing the names of the fields to match the ones in my form. What can be wrong??

    Thanks

  4. boverton says:

    This function gives me an error referrence to undeclared namespace my:

    eval(eval(Skill[Skill_Level = "1"], ‘concat(my:Skill_Name, ";")’), "..")

    It works if I take out the Skill_Level filter.   The exact same code works if I’m referencing a secondary data source dfs:.

    Ay ideas?

  5. n_thobois says:

    Problem with french infopath ;-)

  6. jtravism says:

    How about resolving emails conditionally? IE, if phone is blank do not resolve email in recipient line (or resolve nothing – "").

    I believe I will need to nest a substring function into the evals but i am at a total loss here. Any assistance is appreciated!!

  7. debb66 says:

    I tried the "eval" approach but it’s not working.  eval(eval(person, "concat(my:email, ‘;’)"), "..")

    I’m using InfoPath 2003.  Below is the error I’m receiving:

    "my:Contributors/my:Contributors/my:Person/my:Email" does not point to a valid location path of a field or group.

    I have a repeating table that I need to be able to pull all email addresses from.  Please help!

  8. infopath says:

    Hi debb66,

    Please provide the data structure to your repeating table, such as like this:

    myFields

        group1

             group2 (this may be your repeating table)

                  NameField

                  EmailField

                  Etc.

    I need to get an overview of how your data source is setup.

    Scott

  9. debb66 says:

    Thank you Scott:

    myfields

     Contributors

        People

           Person (repeating table)

             Email

    I have it in a section then repeating table.

  10. infopath says:

    Hi debb66,

    I am not sure what you mean by "I have it in a section then repeating table." So to be sure I am clear, now that I see the structure please do this:

    - Open your XSN in Design view

    - Display the Data Source Task Pane

    - Right-click on your "Email" node and choose Copy XPath

    Paste the XPath here so I can see the complete structure.

    Scott

  11. infopath says:

    Hi debb66,

    I decided to create the same data structure to see if this helps – here is what I have:

    myFields

        Contributors (Group, Non-Repeating)

             People (Group, Non-Repeating)

                  Person (Group, Repeating)

                      Email (Field)

    I then added a text box to my View simply to show the results – here is what I have as the default value for the text box:

    NOTE: If you want to copy/paste this make sure you enable the "Edit XPath" check box!

    substring(xdMath:Eval(xdMath:Eval(../my:Contributors/my:People/my:Person, ‘concat(my:Email, ";")’), ".."), 1, string-length(xdMath:Eval(xdMath:Eval(../my:Contributors/my:People/my:Person, ‘concat(my:Email, ";")’), "..")) – 1)

    When I Preview the form and add e-mail addresses to the "Email" field, each name in each row is added to the text box.

    Let me know if this helped!

    Scott

  12. debb66 says:

    Scott:

    Thank you so much for  your quick response however, it didn’t work…  Here’s the error:

    MSXML5.DLL

    Reference to undeclared namespace prefix: ‘my’.

    Error occurred during a call to property or method ‘Eval’.

    I verified that the .dll is in place and sure enough it is.

    Thanks again for  your help.

  13. infopath says:

    Hi debb66,

    Did you create the data source yourself or is it being created from an existing XML/XSD, database, etc. file?

    I just tested the same form using InfoPath 2003 and it worked fine.

    Scott

  14. debb66 says:

    Hi Scott:

    I have to apologize I’m not a novice or programmer so you may have lost me.  

    I’ve created the form and have posted to a sharepoint site that collects the information.  I’ve not created a database, etc.

    I was hoping it was a matter of just copying and pasting the code.  Arrrggghh! :(

    Debbie

  15. infopath says:

    Hi Debbie,

    No problem – and you actually answered my question! :)

    If you would, do this for me:

    - Open your XSN in Design view

    - Display the Data Source Task Pane

    - Right-click on your "Email" node and choose Copy XPath

    Paste the XPath here so I can see the complete structure.

    Thanks!

    Scott

  16. debb66 says:

    Hi again Scott:

    I’m using 2003 InfoPath – it doesn’t give me the Copy XPath option.

    Deb.

  17. infopath says:

    Hi Debbie…just consider me thick…I forgot you were using 2003. :)

    How about this:

    - On the View, drag an Expression Box and drop it outside of the section and repeating table (i.e. just somewhere in a clear area on the view)

    - Click the "Fx" button

    - Click Insert Field or Group

    - Drill down and select your Email field and click Ok

    - Enable the "Edit XPath" box on the Expression Box

    - Now, select and copy what is in the "Formula" box and post that here.

    Scott

  18. debb66 says:

    You are a very patient person! :-)  Thank you!

    my:Contributors/my:People/my:Person/my:Email

    That is what I have.

  19. infopath says:

    Thanks Debbie!

    Well – what I gave you should definitely work. Unfortunately at this point, the best solution may be to open a support case so we can take a look at your XSN (and possibly at your machine) to try and see why this is failing.

    That XPath expression that you provided is identical to the sample I created on my 2003 machine and it works without issue…so something is going on in your environment.

    Do you have another machine where you can test this?

    Scott

  20. debb66 says:

    I will try it on another machine and see what happens.  Again thank you for your help!  It is greatly appreciated!

  21. debb66 says:

    Hi Scott:

    I tried a different machine unfortunately does not work.  So put in a helpdesk ticket to see what my IT group can do.  

    Thank you again for you patience and help!

    Debbie

  22. infopath says:

    Hi Debbie…

    That is quite odd…if your Help Desk runs into a wall, please don’t hesitate to open a support incident with Microsoft so we can look at this for you.

    Scott

  23. Bressen says:

    I have designed a form with a send button who has this formula: eval(eval(Adresslist; ‘concat(@Email, ";")’); "..") where the Adresslist is a list on a share point server

    And it works just fine, but there seems to be a limit on 100 addresses in the formula or in the InfoPath send function and my list has 140 mail addresses so that the last 40 will not get the message.

    Can anyone tell me if there is such a limit in the formula or in InfoPath and how to make a workaround?

  24. AFawver says:

    Hello,

    I am attempting this for the first time and am getting an error. I’m attempting to pull a list of email addresses based on what is selected in a Multiple-Selection List Box.

    My structure is (secondary data source – SQL DB)

    myFields

     dataFields

       d:tblDistributionListMembers

         :Contact_Name

    The error I’m getting:

    The following XPath expression is not valid: xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("tblDistributionListMembers")/dfs:myFields/dfs:dataFields/d:tblDistributionListMembers/@Contact_Name[../@Distribution_List = xdXDocument:get-DOM()/my:myFields/my:group6/my:group32/my:DistList], ‘concat(my:Contact_Name, ";")’), "..")

    Any help would be greatly appreciated!

    Amber

  25. ConorMcC says:

    Just wanted to say Thanks! I am a non-pragramming InfoPath newbie and this blog has been awesome!

  26. cahubin says:

    Hello,

    This solution was a life saver for me a few months ago, but now the user wants a repeating section within a repeating section.  So instead of just delimiting the one repeating section, I now have to grab those other repeating fields and delimit them.  The reason I’m having to do this is to promote one long delimited field for a programmer to grab in order to create a report.

    Here is the structure:

    InstrumentDetails (group, repeating)

     InstrumentType (field)

       PayeeDetails (group, repeating)

         PayeeType (field)

    Can this be done?

    Thank you!

  27. speeddaimon says:

    I have a similar (if not the same) problem to cahubin and I have been all over the internet and haven’t found a solution. If I have a repeating section nested in another repeating section, and each has a drop down list, how do I access the selected elements of both? I want to grab the contents and shove them into a Rich Text box. Using eval(eval iterates through one repeating section. Is it possible to use another double eval to iterate through the outer repeating section? Please help!

  28. tawright says:

    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

  29. Derek says:

    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.

  30. Derel says:

    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.

  31. slheim says:

    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

  32. Ron says:

    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,

  33. Derek says:

    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.

  34. Greg says:

    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

  35. susn says:

    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?

  36. Ramya says:

    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.

  37. David says:

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

  38. Charles M says:

    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?

  39. Scott Heim says:

    Hi Charles M,

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

    Scott

  40. Charles M says:

    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.

  41. Scott Heim says:

    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

  42. Vaishnavi Seshadri says:

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

  43. Manuel says:

    Hi,

    First of all, thanks for very useful post.

    It works very good, but I have just a little lack.

    The structure of my secundary dataconnection is like that:

    myFields

    dataFields

     ns1:GetAdmittingDiagnosisResponse

      GetAdmittingDiagnosisResult

       AdmDiagnosis

        Code

        ClinicalPriorityDesc

        …

    My formula is:

    eval(eval(AdmDiagnosis[ClinicalPriorityDesc != "Secundario"]; 'concat(ns1:Code, "~", ns1:Description, "¬")'); "..")

    In XPath:

    xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("GetAdmittingDiagnosis")/dfs:myFields/dfs:dataFields/ns1:GetAdmittingDiagnosisResponse/ns1:GetAdmittingDiagnosisResult/ns1:AdmDiagnosis[ns1:ClinicalPriorityDesc != "Secundario"], 'concat(ns1:Code, "~", ns1:Description, "¬")'), "..")

    This formual works perfect, but if I try the equal filter

    [ClinicalPriorityDesc = "Secundario"]

    This error raised:

    "msxml5.dll

    Reference to undeclared namespace prefix: 'ns1'.

    Error occurred during a call to property or method 'Eval'."

    I workaround this issue using the not equal comparision instead the equal, but I'm curious if there is a way to fix that…

    Thanks

  44. Laurel says:

    I am receiving the same issue with the formula: "../my:RepeatingTable/my:NomineesHidden/my:Person" does not point to a valid location path of a field or group.

    I first tried the formula in the main body of the post. When that didn't work, I copied the formula that was given to someone else and adjusted the group names to fit my .xsn. Here is the XPath formula:

    substring(xdMath:Eval(xdMath:Eval(../my:RepeatingTable/my:NomineesHidden/my:Person, 'concat(my:AccountID, ";")'), ".."), 1, string-length(xdMath:Eval(xdMath:Eval(../my:RepeatingTable/my:NomineesHidden/my:Person, 'concat(my:AccountID, ";")'), "..")) – 1)

    My data structure is this:

    NomineeTopGroup (non-repeating Group)

      RepeatingTable (repeating table)

         NomineeHidden (non-repeating Group)

           pc:Person (repeating person group)

              display name (string)

              accountid (string)

              accounttype (string)

    I have tried everything I can think of to fix the formula so that it recognizes the AccountID field, but I have failed.

    I'd appreciate any help you could provide.

  45. usman says:

    does not work with people picker sp 2010

  46. Jan says:

    Hi, thanks for this post, I managed to solve one of my problems :).

    I was trying to get te same result from a multiple-selection listbox, but can't get it to work.

    This is the datastructure :

      group1

        repeating field1

      field2

    I would like to see the checked item from field1 in field2, ';' seperated.

    all help appreciatied :)

    Jan

  47. Mahesh Kunadia says:

    I have been looking for answer to send email to multiple people using InfoPath function ""Person/Group Picker" and data connection.

    Finally, using this formula and adding some tricks I was able to do it.

    My Data Source Looks like this:

    Notification-group

      Notification-send-to

      Notification-send-to-group (Person/Group Picker)

         pc:Person

             DisplayName

             AccountId

             AccountType

    Set default value for "Notification-send-to"

    eval(eval(Person, 'concat(pc:AccountId, ";")'), "..")

    Setup data connection for email and enter following formula to remove domain name in “To” using “fx”

    translate(Notification-send-to, "DOMAIN NAME", "")

    Thank you,

  48. i cant seem to make this work. says:

    first of all hi!.i have a forma and a people list.

    Structure:

    myFields

       Group

           pc:Person (repeating group)

                   DisplayName

                   AccountId

                   AccountType

    i want so send emails to several DisplayName and cannot make it work :(

  49. asdy says:

    I am having a form with different sections and the momment the user hits the submit button I need each section in this form to be sent to different e-mails, I mean If I am having 3 sections in the form I need each of these 3 sections to be sent to 3 different e-mails. Any ideas how can I achieve this goal ?

  50. Anonymous says:

    Does anybody besides me thinks that InfoPath is not the great tool they promote?

  51. Niraj says:

    Exactly what I needed. Thanks Bunch!!!

  52. tanx a lot says:

    hi scott

    I really admire you for your good answer

    tanx a lot

  53. melih says:

    It worked for me. I used my secondary data source instead of "person" and I used "." (current field value) instead of "my:email". Thanks

  54. Nitesh says:

    Solved it with this formula

    eval(eval(Person, 'concat(concat(substring-after(pc:AccountId, ""), "@domain.com"), ";")'), "..")

  55. Angie says:

    I am facing a weird issue and do not know how to solve. Please help.

    I am using Infopath and I have one multi-selection listbox with secondary data connection with total 4 columns. There are ID, Title, Email Address, Description.

    The listbox is displaying Title value.

    Purpose: user need to check the listbox and decided which listbox to select. Once tick, the eval formula will look up Email Address column and send email out. This is the formula – eval(eval(Email, 'concat(., ";")'), "..")

    Question: why the email display without check any check box?

    Expectation: User should check the box then email address only show in email To list

    Thank you.

  56. Lun says:

    Hi Scott,

    I have a master/detail repeating table in my form, and one of fields is "EmailAddress" which shows customer's email address. I created a submit button and put the "EmailAddress" field to "TO" object. When I tested it, the submit button only would return the first row of emailaddress from the repeating table, but not the rest of it.. Even thought I put the XML code like this: current()/dfs:dataFields/d:vw_HZLeadLists/@EmailAddress

    It still just return the first record of emailaddress… Could you give me some ideas? Thanks a lot!

    -Lun

  57. Rustam says:

    Thank you very much! It was a lot of help

  58. Perla says:

    Hi My formula has not errors but is not working :(

  59. Perla says:

    Hi to all, currently my formula has not errors but is not working does not concat nothing

    eval(eval(FORMATIONGROUP, 'concat(../my:FORMATIONGROUP/my:group4/my:ContactFormations, "")'), "..")

  60. Brett says:

    Would you happen to have a solution where I don't send an email to multiple people in a repeating table, but the last line item in the table.

    Some info, I have a form with repeating table that has Primary & Secondary analysts.  The first line item (run 1) fires off the email to the primary & secondary when Status equals Completed.  Now the next line item (run 2) has different people associated with Primary & Secondary and need to send the email to only them when the status condition is met, but not the folks in run 1.

    Possible?

  61. PaulB says:

    Hi, I've been going round in circles with this. My xpath doesn't return any errors but only pulls through the first entry in the repeating table, not all of them. Xpath is:

    xdMath:Eval(xdMath:Eval(../my:InjuredPersonsReportingManager/pc:Person, 'concat(pc:DisplayName, ";")'), "..")

    Any suggestions as to what I'm doing wrong.

  62. Steve says:

    Worked PERFECT!  Thank you so much!

  63. JJ says:

    What is the 2013 version for this. There is no "Myemaill" for 2013?

  64. Venkatesh says:

    Instead of an email 'To' field, I am trying to populate a text field with a concatenated set of people's id's

    However, it does not populate with the value and I have tried various versions of the same eval formula.

    This is my current formula –

    eval(eval(Person, ‘concat(xdXDocument:GetDOM(“FIM Approvers”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Approver_x0020_Name/pc:Person/pc:AccountId, “;”)’), “..”)

    where Person – Repeating Group

    Account ID – to be concatenated based on the person populated in the repeating table

    Approver Name – Secondary data source which has the Person name in a people picker field.

    Any help on this will be appreciated.

    Thanks,

    Venkatesh

  65. Venkatesh says:

    The formula didnt show correctly in the previous post.

    eval(eval(Person, ‘concat(xdXDocument:GetDOM(“FIM Approvers”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Approver_x0020_Name/pc:Person/pc:AccountId, “;”)’), “..”)

  66. Nexcompac says:

    Sorry to drag up another old thread, but I am having issues with this particular function.

    When I create the loop, and publish, I get the following effect:

    The first email address is listed twice in the CC field.

    I am using the people picker option, however, I created a manual table and manually entered emails and got the same result.  

    Here is my xpath:

    xdMath:Eval(xdMath:Eval(/my:myFields/my:email/my:Change_Implementer_Primary/pc:Person, 'concat(/my:myFields/my:email/my:Change_Implementer_Primary/pc:Person/pc:DisplayName, ";")'), "..")

    My repeating table is set as follows: (people picker)

    myFields

     email (this is a repeating table)

       Change_Implementer_Primary (this is a required field)

         pc:Person

           DisplayName

           AccountId

           AccountType

    I am way stuck as to why the loop is picking the same email name twice when there are two email names selected.

    I have tried this in two forms:

    Repeating table with emails in separate boxes

    One box allowing multiple selections.  

    Both do not work for me.

    Thx for any help.