Code

Re: Using EVAL and concat for concatenating fields in repeating tables

giustis5 — Wed, 18 Sep 2013 13:48:19 GMT

Hi Ann

Thank you for the above, it certainly has my head spinning a little in trying to accomplish my goal.

I am trying to do the following:

have a user open an infopath form, if that user is a member of a specified security group, have all the members of that group output into a people picker control within the form.

Ive managed to create the UserGroup web service data connection to get the info and hacked it so that it actually works and pulls in the info I need for the specified security group, i can even get it to output the account id's or login names of all members of the group to the person picker control, but unfortunately it only ever outputs the first persons name it finds for each occurence, i.e. using a count function i can see there are 5 people in the security group, but when i output their account id's i get the first persons account id five times instead of each of the individual accounts.

My Data Connection is called HR_UK - Permission Check

currently just testing this, so i have a just called the People Picker control - People

I am trying to set the accountid field of the people picker with the LoginName of each person it finds in the group, heres my XPATH so far which does return the value, only the first value it finds repeated for however many people are actually in the group. Currently i am not setting any filters, so essentially i just want it to output the people into the control from that group whenever the form loads. The following is an on form load action rule to set the accountid of the people picker repeating group to the returned value.

xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User, 'concat(substring(xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User/@LoginName, 1, (current() = xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User) * string-length(xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User/@LoginName)), substring(concat("; ", xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User/@LoginName), 1, (not(current() = xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User)) * string-length(concat("; ", xdXDocument:GetDOM("HR_UK - Permissions Check")/dfs:myFields/dfs:dataFields/tns:GetUserCollectionFromGroupResponse/tns:GetUserCollectionFromGroupResult/tns:GetUserCollectionFromGroup/tns:Users/tns:User/@LoginName))))'), "..")

Any ideas?

Re: Using EVAL and concat for concatenating fields in repeating tables

stara — Wed, 22 May 2013 18:01:41 GMT

Hi Ann,

I'm using a multi-select list box and would like to concatenate selected values and store in text box?

Can I use the same formula : xdMath:Eval(xdMath:Eval(../my:Group/my:RepeatingGroup, 'substring(concat(my:Value, ";"), 1, (my:Select = string(true())) * string-length(concat(my:Value, ";")))'), "..")

Also, what should I use for my:Value and my:Select?

Thanks so much

Stara

Re: Using EVAL and concat for concatenating fields in repeating tables

Johan — Wed, 13 Mar 2013 12:29:04 GMT

Thank you for this!!!!!!!

Re: Using EVAL and concat for concatenating fields in repeating tables

Frenchy_c — Tue, 21 Feb 2012 15:55:52 GMT

Hello,

Thanks for this post it was very helpful, i am using it and it seems to work and populate the text box but i'm also getting the error below. is there anybody that can help

xdMath:Eval(xdMath:Eval(../my:RMS/my:RMS_Role, 'substring(concat(my:Role, xdXDocument:GetDOM("crlf")/characters/@crlf), 1, (my:Selection = "1") * string-length(concat(my:Role, xdXDocument:GetDOM("crlf")/characters/@crlf)))'), "..")

Error

System.Xml.XPath.XPathException
'
d:Balanced_Collections_1
Z1BW_BEX_WORKBOOKS
Z1BW_END_USERS_BASIC_ACCESS
Z2CRM_RSBC_BR_PSCD_FCC_WOCONT
Z4CRM_END_USER_AND_RFCS
Z4CRM_UIU_FRAMEWORK
Z4ECC_END_USER_AND_RFCS
Z5CRM_RSBC_PR_ADDRESS_UPDATE
Z5ECC_RSBC_BP_ACCOUNT_DISPLAY
Z5ECC_RSBC_BP_CHG_ADDR_INFO
Z5ECC_RSBC_BP_CHG_ASSET_INFO
Z5ECC_RSBC_BP_DISPLAY
Z5ECC_RSBC_CA_DISPLAY
Z5ECC_RSBC_CO_CHG_DUN_LOCK
Z5ECC_RSBC_CO_DISPLAY
Z5ECC_RSBC_CO_MASS_LOCKS
Z5ECC_RSBC_COLLECTION_REPORTS
Z5ECC_RSBC_COLLECTION_RESEARCH
Z5ECC_RSBC_PRIVATE_COLL_ADMIN
Z5ECC_RSBC_WRITE_OFF_ADMIN
ZBW_EASMGMT
ZBW_HDACLAS
ZBW_PAYMENT_SEARCH_HIST_ARCH

' has an invalid token.
   at MS.Internal.Xml.XPath.XPathParser.ParseXPathExpresion(String xpathExpresion)
   at MS.Internal.Xml.XPath.QueryBuilder.Build(String query, Boolean allowVar, Boolean allowKey)
   at MS.Internal.Xml.XPath.QueryBuilder.Build(String query, Boolean& needContext)
   at System.Xml.XPath.XPathExpression.Compile(String xpath, IXmlNamespaceResolver nsResolver)
   at System.Xml.XPath.XPathNavigator.SelectSingleNode(String xpath, IXmlNamespaceResolver resolver)
   at Access_request.FormCode.RMS_Profile_Changed(Object sender, XmlEventArgs e)
   at Microsoft.Office.InfoPath.Internal.XmlEventHost.GenericProxy(Object genericDelegate, DataDOMEvent dataDOMEvent, InfoPathEvents type)
   at Microsoft.Office.InfoPath.Internal.XmlEventHost.ChangedProxy(DataDOMEvent dataDOMEvent)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._DataDOMEventSink_SinkHelper.OnAfterChange(DataDOMEvent pDataDOMEvent)

Re: Using EVAL and concat for concatenating fields in repeating tables

Scott Rooke — Sat, 10 Sep 2011 17:47:50 GMT

Jimmy -

Thank you so much! I was trying follow smaus's formula and it got a little complicated. I tried many variations except the one you sent. That resolved the issue.

Much appreciated!!!!

Scott

Re: Using EVAL and concat for concatenating fields in repeating tables

Jimmy — Sat, 10 Sep 2011 16:57:06 GMT

I'm having trouble following your formula, but I would think this would be enough:

xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:Active = string(true())], 'concat(d:email, ";")'), "..")

Re: Using EVAL and concat for concatenating fields in repeating tables

Scott Rooke — Sat, 10 Sep 2011 14:37:45 GMT

Thank you Ann! This has been very helpful getting me closer to want I need to implement. After many attempts of building the formula in the "Insert Formula" window with no success, I copied pasted your code at the top and added my own values. Listed below.

xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW, 'substring(concat(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:email, ";"), 1, (xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Active = string(true())) * string-length(concat(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:email, ";")))'), "..")

This is the same formula with the filter applied to the data connection on the "Active" = TRUE

xdMath:Eval(xdMath:Eval(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:Active = string(true())], 'substring(concat(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:email, ";"), 1, (xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Active = string(true())) * string-length(concat(xdXDocument:GetDOM("TA")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:email, ";")))'), "..")

Secondary Data Source: TA
- List: TA
-- Column: Title (Single line of text - out of the box)
-- Column: Active (yes/no - bool)
-- Column: email (single line of text)
-- Column: ID (out of the box)

Currently there are three rows in this list.

Title          Active     email
John Doe     Yes     john@doe.com
Jane Doe     Yes     jane@doe.com
Jimmy Doe     Yes     jimmy@doe.com

The main data source: myFields
It has one field: txtEmailAddresses (single line of text)

Let me state my objective. I have a List with a Name, Active, and email address fields. I want to concatenate a list of email addresses for those items in the list where the "Active" checkbox is = True. Can you see where I'm going wrong with my current formula.

Thanks in advance!

Scott
(Chicago, IL)

Re: Using EVAL and concat for concatenating fields in repeating tables

sharepoint2010 — Tue, 26 Oct 2010 08:18:58 GMT

Hi @all

I tried the manual from smaus... But I have a repeating table in a repeating table. So it doesn't work.

It's a Sharepoint 2010 list with two colums. Title (String) and User (Multiple user Field from Active Directory)

Now I want to geht the field "AccounfID" for enable/disable Fields in the InfoPath 2010 Form if the user witch ist logged on in a group.

Does anyboy have an idia? I cant find any helpful things...

Thank's so much

Re: Using EVAL and concat for concatenating fields in repeating tables

radamesort — Wed, 09 Dec 2009 15:12:36 GMT

Ann, I cannot thank you enough for this!

Re: Using EVAL and concat for concatenating fields in repeating tables

Ryan Beesley — Wed, 07 Jan 2009 03:37:49 GMT

Interesting post as this was what I was hoping to do without code. My usage was a little different since I have a repeating section of 0 or more values. Additionally I only wanted to seperate the values with "; " if it was required, meaning 2 or more values. Based on your lead, I arrived at the following.

xdMath:Eval(xdMath:Eval(my:RepeatingGroup, 'concat(substring(my:Value, 1, (current() = ../my:RepeatingGroup[1]) * string-length(my:Value)), substring(concat("; ", my:Value), 1, (not(current() = ../my:RepeatingGroup[1])) * string-length(concat("; ", my:Value))))'), "..")

The breakthrough for me, was wrapping two strings with a concat, but using the above IF-like statement to select mutually exclusive strings.

The first string uses the condition:

(current() = ../my:RepeatingGroup[1])

Whereas the second string negates the condition:

(not(current() = ../my:RepeatingGroup[1]))

As the first string doesn't use the "; " seperator, I simply return a substring that is the entire length of the string. The second string prepends the seperator. In both cases we are evaluating whether or not the current node is the first node. This means that the first time through the concated string looks like:

concat(my:Value, "")

And with additional passes it looks like:

concat("", concat("; ", my:Value))

As long as the tested strings have carefully selected conditions, you should be able to combine an indefinite number of string nodes in this fashion and even apply format hinting to the result; I won't go so far as to say you can apply formatting, as this is probably not where it should be applied. But you should be able to construct strings in any way you desire using this technique. I hope this proves useful to someone else.

/Ryan

Re: Using EVAL and concat for concatenating fields in repeating tables

concentratedawesome — Mon, 01 Dec 2008 15:14:24 GMT

You are a God - that is some hardcore functionating. You bailed me out of a jam, thanks.

Re: Using EVAL and concat for concatenating fields in repeating tables

Hilary Stoupa — Thu, 10 Jul 2008 15:41:56 GMT

Hi, Ann:

Thanks so much for posting this -- it was exactly what I needed to solve a problem!

Re: Using EVAL and concat for concatenating fields in repeating tables

smaus — Thu, 10 Jul 2008 03:28:38 GMT

Trying to build InfoPath forms with complicated logic without code is pushing InfoPath beyond some of its limits. Nonetheless, I did some research and was able to come up with an expression that will return a concatenated string of filtered values from a repeating table.

Using a hacked together IF-like statement from http://blogs.msdn.com/infopath/archive/2006/11/27/conditional-default-values.aspx and the double eval() trick in http://blogs.msdn.com/infopath/archive/2006/04/05/569338.aspx, I arrived at this:

xdMath:Eval(xdMath:Eval(../my:Group/my:RepeatingGroup, 'substring(concat(my:Value, ";"), 1, (my:Select = string(true())) * string-length(concat(my:Value, ";")))'), "..")

This return a semi-colon-delimited list of Values where Select has been checked and evaluates to TRUE

With this schema:

Group

RepeatingGroup

Select
Value

The double eval() trick merely loops through a repeating list. The key to filtering is in the calculation performed in the nested eval() function. That's where the IF-like statement comes in.

It starts with the substring() function.

substring (string, start-position, end-position)

Where

string = concat(my:Value, ";")
start-position = 1
end-position =(my:Select = string(true())) * string-length(concat(my:Value, ";")))

Here's what I want in my concatenated string:

concat(my:Value, ";")

But I only want it if my:Select = TRUE so to evaluate whether my:Select = TRUE:

my:Select=string(true())

If it's this operation returns TRUE, the result will be a 1

If the operation returns a FALSE, the result will be a zero (0)

Using the length of the text I want to return as a multiplier, I'll either get a zero or I'll get the length of the entire string:

(my:Select=string(true()))* (string-length(concat(my:Value, ";")))

Pulling that together with the substring() function, it will return either a zero-length string or the entire text I want returned:

substring(concat(my:Value, ";"), 1, (my:Select=string(true()))*(string-length(concat(my:Value, ";"))))

Drop this into the calculation parameter in the nested eval() function and you're done.

My example returns one field based on the value of another field in that same repeating node. I use it to return a list of the selected values in a hacked-together, browser-enabled version of a multi-select list box. If you want to return only non-blank fields, you can move some stuff around and make it simpler. Well, simpler maybe to some.

eval(eval(RepeatingGroup, 'concat(my:Value, substring(";", 1, string-length(my:Value)))'), "..")

-Ann

Re: Using EVAL and concat for concatnating fields in repeating tables

Yurnero_ — Mon, 26 May 2008 13:24:16 GMT

Hello, I got a 'problem' with eval() function

I got a repeating table with number. Those data are imported from a sharepoint form library. And extract from forms.

My problem is I can't sum them

I used

sum(eval( <@data source>, '<@number_field>')) and it crash infopath.

any idea ?

Fact is if I drag'n drop the data source field, number appear properly

Best regards.

edit :I open a new topic, because it's different.

Re: Using EVAL and concat for concatnating fields in repeating tables

boverton — Fri, 01 Feb 2008 05:56:05 GMT

Thank you both very much for your efforts. I still don't know why this will work on a secondary data source but not the main data source. Someone smarter than me will have to figure it out.