InfoPath Dev

Hi there,

Hope you can help me on this one:

I have a field ("strAccountIds") containing a concatenated string delimited by semicolon, e.g. DOMAIN\user1;DOMAIN\user2;DOMAIN\user3.

From this string I would like to extract this string:

user1;user2;user3

I've tried using concat and substring-after and substring-before like this:

 concat(substring-before(substring-after(strAccountIds; "\"); ";"); ";")

This formula only returns "user1;"  - I need it to return everything in between every set of "\" and ";", i.e. all users, not just the first one.

I haven't been able to find relevant forum posts since the length of the concatenated string varies (as it is based on the number of users selected in a people picker).

I took a look at this post http://www.infopathdev.com/forums/p/12278/43085.aspx#43085

I can understand how to parse a string when there is a fixed number of delimeters but how to do it when the number of delimeters varies?

Hope you can direct me to relevant posts, links or the like .. I'd be really grateful.

 Take a look at the translate() function.

 Something like this should work: translate(yourfield, "DOMAIN\", "")

Good luck.

 Thanks a bunch..worked like a charm..and results in a more elegant (I hope) solution, since I no longer have to get the usernames via the GetUserProfileByName webservice but can simply get them from the AccountId in the People picker.

I'm afraid that using translate() here will not only remove the part you want, but will also remove the letters from the domain name that appear anywhere else, so if you had:

DOMAIN\Dom Delouise;DOMAIN\Ned Flanders

You would wind up with

om elouise;ed Flanders

Is that not the case?

 Hi Jimmy,

Having done some more through testing I can see you are right..there's an issue with removing too many characters..thanks for telling me.

For now I can't really find any logic in it..  user1, user2 etc. are initials

If I have DOMAIN\fn , it results in 'f' (cutting off the n)

If I have DOMAIN\nd  , it results in 'nd' (as it should)

So it is not all the letters from the domain name appearing elsewhere that are removed.

Anyone has any thoughts on this? / An alternative to using translate()?

translate() is case-sensitive and that might account for your seemingly conflicting results.

I'm attaching a form that uses a secondary XML data source as a staging area for breaking down the list and then building it back up.  I've included the secondary source's contents in the view so you can see what's going on behind the scenes.  The operation is limited by the number of <User> nodes in the secondary source, but you should be able to arbitrary increase that number to what seems like more than enough.

I inserted a textbox to view the content of the strAccountIds field and it turned out that the user-part (DOMAIN\"user1") was a mix of upper- and lowercase letters.

I wanted to avoid using a secondary data connection and 'extract' the values by using formulas.

Being inspired by this thread http://eai.ittoolbox.com/groups/technical-functional/tibco-l/how-can-i-remove-20-from-variable-using-xpath-1655345 , I ended up with  the following solution:

On the field strAccountIds I applied a rule with the condition "strAccountIds contains "DOMAIN\"

and the action:

set a fields value .=concat(substring-before(.; "DOMAIN\"); substring-after(.; "DOMAIN\"))

I'm not sure why you'd want to avoid using a secondary XML file connection.  They don't really have any downsides to speak of.  Your approach will work to process up to about 16 or less names, at which point InfoPath will give up with an error.  So if you do go with that approach, please test it to ensure that it can support the maximum number of names that someone might use in that field.

This looks good for my needs but the code doesn't like the semi colon, any help appreciated

sofusok:

I inserted a textbox to view the content of the strAccountIds field and it turned out that the user-part (DOMAIN\"user1") was a mix of upper- and lowercase letters.

I wanted to avoid using a secondary data connection and 'extract' the values by using formulas.

Being inspired by this thread http://eai.ittoolbox.com/groups/technical-functional/tibco-l/how-can-i-remove-20-from-variable-using-xpath-1655345 , I ended up with  the following solution:

On the field strAccountIds I applied a rule with the condition "strAccountIds contains "DOMAIN\"

and the action:

set a fields value .=concat(substring-before(.; "DOMAIN\"); substring-after(.; "DOMAIN\"))

Hi - do you mean your rule doesn't like the semi-colon? I'm not suprised.... this formula would use commas instead:

concat(substring-before(., "DOMAIN\"), substring-after(., "DOMAIN\"))

I'm actually following this suggestion from the threads but it's still displaying the domain with the accountid, I tried the comma option

I have tried following the suggestions in this post and am not getting the result I need. I have concatenated three data sets in a drop down box (pulling from a list) and when I make a selection, each data set needs to populate the corresponding box (via the calculated field function).  

The name of my drop down field is Part_Number and the options look like this "21TMP9 / Wires / Mechanical wires".
The three node fields are PartNumber / Nomenclature / Description.
I am able to have PartNumber and Description populate correctly via a substring but cannot get the middle node to substring data.
I have tried the following: substring-after(substring-before(Part_Number, " / "), " / ") and substring-before(substring-after(Part_Number, concat(Part_Number, " / ")), " / ") and both result in a blank entry for the middle node "Wires". Any suggestions how to pull the middle node in this case since it is using only "/" to separate them? TIA

Jimmy wrote the following post at 07-07-2011 04:29 AM:

translate() is case-sensitive and that might account for your seemingly conflicting results.

aaargh! After spending about an hour trying to accomplish this, I finally saw Jimmy's comment about translate being case sensitive. Thanks - got it working now!