Partager via

Email Submit "To" line (loops in formulas)

  • Article

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

  • Anonymous
    December 04, 2006
    InfoPath makes it trivial to track totals for repeating structures, such as customer orders. Just create

  • Anonymous
    September 24, 2007
    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.

  • Anonymous
    January 17, 2008
    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

  • Anonymous
    January 24, 2008
    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?

  • Anonymous
    June 25, 2008
    Problem with french infopath ;-)

  • Anonymous
    December 29, 2008
    PingBack from http://sebastianatar.wordpress.com/2008/12/29/method-for-populating-sharepoint-designer-workflow-email-with-multiple-recipients-from-contact-selector-control/

  • Anonymous
    January 18, 2009
    PingBack from http://www.keyongtech.com/1239900-get-all-values-of-dropdownlist

  • Anonymous
    February 06, 2009
    The comment has been removed

  • Anonymous
    June 08, 2009
    PingBack from http://insomniacuresite.info/story.php?id=6264

  • Anonymous
    June 29, 2009
    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!

  • Anonymous
    June 29, 2009
    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

  • Anonymous
    June 29, 2009
    Thank you Scott: myfields  Contributors     People        Person (repeating table)          Email I have it in a section then repeating table.

  • Anonymous
    June 29, 2009
    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

  • Anonymous
    June 30, 2009
    The comment has been removed

  • Anonymous
    June 30, 2009
    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.

  • Anonymous
    June 30, 2009
    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

  • Anonymous
    June 30, 2009
    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

  • Anonymous
    June 30, 2009
    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

  • Anonymous
    June 30, 2009
    Hi again Scott: I'm using 2003 InfoPath - it doesn't give me the Copy XPath option. Deb.

  • Anonymous
    June 30, 2009
    The comment has been removed

  • Anonymous
    June 30, 2009
    You are a very patient person! :-)  Thank you! my:Contributors/my:People/my:Person/my:Email That is what I have.

  • Anonymous
    June 30, 2009
    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

  • Anonymous
    June 30, 2009
    I will try it on another machine and see what happens.  Again thank you for your help!  It is greatly appreciated!

  • Anonymous
    July 08, 2009
    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

  • Anonymous
    July 08, 2009
    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

  • Anonymous
    December 01, 2009
    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?

  • Anonymous
    March 10, 2010
    The comment has been removed

  • Anonymous
    March 24, 2010
    Just wanted to say Thanks! I am a non-pragramming InfoPath newbie and this blog has been awesome!

  • Anonymous
    April 13, 2010
    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!

  • Anonymous
    May 03, 2010
    The comment has been removed

  • Anonymous
    May 13, 2010
    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

  • Anonymous
    August 03, 2010
    The comment has been removed

  • Anonymous
    August 03, 2010
    The comment has been removed

  • Anonymous
    August 03, 2010
    The comment has been removed

  • Anonymous
    August 04, 2010
    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,

  • Anonymous
    August 04, 2010
    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.

  • Anonymous
    August 16, 2010
    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

  • Anonymous
    September 19, 2010
    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?

  • Anonymous
    October 26, 2010
    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.

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

  • Anonymous
    November 09, 2010
    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?

  • Anonymous
    November 10, 2010
    Hi Charles M, Can you share with me what "error" you are referring to? Scott

  • Anonymous
    November 10, 2010
    The comment has been removed

  • Anonymous
    November 10, 2010
    The comment has been removed

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

  • Anonymous
    March 22, 2011
    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

  • Anonymous
    April 15, 2011
    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.

  • Anonymous
    June 16, 2011
    does not work with people picker sp 2010

  • Anonymous
    June 19, 2011
    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

  • Anonymous
    July 25, 2011
    The comment has been removed

  • Anonymous
    August 02, 2011
    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 :(

  • Anonymous
    December 20, 2011
    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 ?

  • Anonymous
    August 16, 2012
    Does anybody besides me thinks that InfoPath is not the great tool they promote?

  • Anonymous
    October 12, 2012
    Exactly what I needed. Thanks Bunch!!!

  • Anonymous
    January 20, 2013
    hi scott I really admire you for your good answer tanx a lot

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

  • Anonymous
    October 29, 2013
    Solved it with this formula eval(eval(Person, 'concat(concat(substring-after(pc:AccountId, ""), "@domain.com"), ";")'), "..")

  • Anonymous
    November 29, 2013
    The comment has been removed

  • Anonymous
    December 23, 2013
    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

  • Anonymous
    February 02, 2014
    Thank you very much! It was a lot of help

  • Anonymous
    October 05, 2014
    Hi My formula has not errors but is not working :(

  • Anonymous
    October 05, 2014
    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, "")'), "..")

  • Anonymous
    December 29, 2014
    The comment has been removed

  • Anonymous
    January 25, 2015
    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.

  • Anonymous
    April 10, 2015
    Worked PERFECT!  Thank you so much!

  • Anonymous
    August 20, 2015
    What is the 2013 version for this. There is no "Myemaill" for 2013?

  • Anonymous
    August 30, 2015
    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

  • Anonymous
    August 30, 2015
    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, “;”)’), “..”)

  • Anonymous
    October 27, 2015
    The comment has been removed