Using EVAL and concat for concatnating fields in repeating tables - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Using EVAL and concat for concatnating fields in repeating tables

Last post 09-18-2013 06:48 AM by giustis5. 28 replies.
Page 2 of 2 (29 items) < Previous 1 2
Sort Posts: Previous Next
  • 05-26-2008 06:24 AM In reply to

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

    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.

  • 07-09-2008 08:28 PM In reply to

    • smaus
    • Not Ranked
      Female
    • Joined on 05-03-2008
    • Bellevue, WA
    • Posts 6

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

    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
  • 07-10-2008 08:41 AM In reply to

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

    Hi, Ann:

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

    Hilary Stoupa

  • 12-01-2008 07:14 AM In reply to

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

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

  • 01-06-2009 07:37 PM In reply to

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

    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

  • 12-09-2009 07:12 AM In reply to

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

     

    Ann,  I cannot thank you enough for this!

  • 10-26-2010 01:18 AM In reply to

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

     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

  • 09-10-2011 07:37 AM In reply to

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

    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)
  • 09-10-2011 09:57 AM In reply to

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

    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, ";")'), "..")

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 09-10-2011 10:47 AM In reply to

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

    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
  • 02-21-2012 07:55 AM In reply to

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

     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)

  • 03-13-2013 05:29 AM In reply to

    • Johan
    • Not Ranked
    • Joined on 01-24-2008
    • South Africa
    • Posts 17

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

    Thank you for this!!!!!!!
    Johan Bredell
  • 05-22-2013 11:01 AM In reply to

    • stara
    • Top 200 Contributor
    • Joined on 10-20-2010
    • Posts 54

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

    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

     

  • 09-18-2013 06:48 AM In reply to

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

     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?
Page 2 of 2 (29 items) < Previous 1 2
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.