General

Re: Extracting values between specific characters in string

jcollins — Thu, 12 Dec 2013 20:34:06 GMT

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!

Re: Extracting values between specific characters in string

J-e-s-s-154 — Mon, 25 Feb 2013 18:55:19 GMT

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

Re: Extracting values between specific characters in string

jacooper — Wed, 06 Feb 2013 15:38:00 GMT

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

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\"))

Re: Extracting values between specific characters in string

Hilary Stoupa — Wed, 06 Feb 2013 15:25:38 GMT

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\"))

Re: Extracting values between specific characters in string

jacooper — Wed, 06 Feb 2013 08:22:37 GMT

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\"))

Re: Extracting values between specific characters in string

Jimmy — Fri, 08 Jul 2011 18:20:06 GMT

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.

Re: Extracting values between specific characters in string

sofusok — Fri, 08 Jul 2011 12:11:57 GMT

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\"))

Re: Extracting values between specific characters in string

Jimmy — Thu, 07 Jul 2011 08:29:42 GMT

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.

Re: Extracting values between specific characters in string

sofusok — Thu, 07 Jul 2011 06:40:31 GMT

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()?

Re: Extracting values between specific characters in string

Jimmy — Wed, 06 Jul 2011 12:55:15 GMT

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?

Re: Extracting values between specific characters in string

sofusok — Wed, 06 Jul 2011 12:21:08 GMT

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.

Re: Extracting values between specific characters in string

ErnestoM — Wed, 06 Jul 2011 12:07:16 GMT

Take a look at the translate() function.

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

Good luck.

Extracting values between specific characters in string

sofusok — Wed, 06 Jul 2011 09:44:13 GMT

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.