Data Connections

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

ncarson — Wed, 08 Sep 2010 17:03:37 GMT

The "simpler formula" you provided (first example above) did it! I cannot thank you enough. I've spent an unbelievable amount of hours trying to do this. Next time I won't wait so long before raising the flag and asking for help. Jimmy, you are my hero!

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

Jimmy — Wed, 08 Sep 2010 16:20:59 GMT

You're missing a parenthesis before the minus sign and that's why you had to append one to the end. That's most likely the reason you're only getting three characters.

There's a simpler formula to eliminate the extraneous separator character. It's:

substring(eval(eval(RepeatingGroup, 'concat(", ", FieldToConcatenate)'), '..'), 3)

You also shouldn't be using an absolute XPath in your concat statement. How's this?

substring(eval(eval(SpeakersGroup, 'concat(", ", my:LawyersSpeaking)'), ".."), 3)

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

ncarson — Wed, 08 Sep 2010 15:21:56 GMT

Hilary - I have a similar problem that I can't seem to get to work and thought maybe you could help me. I have a Repeating Table and I want the row contents concatenated and separated by a comma but not have a comma at the end of the string. My datasource is set up like this:

Main
myFields
EventPage (Folder)
    EventInfo (Folder)
      LawyerRoles (Folder)
        SpkrsGrp (Folder)
          SpeakersGroup (REPEATING GROUP)
            LawyersSpeaking (TEXT FIELD)

xPath properties of Repeating Group: /my:myFields/my:EventPage/my:EventInfo/my:LawyerRoles/my:SpkrsGrp/my:SpeakersGroup

xPath properties of Text Field: /my:myFields/my:EventPage/my:EventInfo/my:LawyerRoles/my:SpkrsGrp/my:SpeakersGroup/my:LawyersSpeaking

The Text Field (Lawyers Speaking) is a Drop-Down List Box connected to an external data connection so a user can select from a dynamic database of names and there is no limit to the number that can be selected, but in most cases probably won't be more than 6-8 names.

I added a text box and for the Properties I inserted this formula:

substring(eval(eval(SpeakersGroup, 'concat(/my:myFields/my:EventPage/my:EventInfo/my:LawyerRoles/my:SpkrsGrp/my:SpeakersGroup/my:LawyersSpeaking, ", ")'), ".."), 1, string-length(eval(eval(SpeakersGroup, 'concat(/my:myFields/my:EventPage/my:EventInfo/my:LawyerRoles/my:SpkrsGrp/my:SpeakersGroup/my:LawyersSpeaking, ", ")'), "..") - 2))

I know there's an extra closed paren at the end than on your formula, but I kept getting an error asking for an "end of string" so after adding the last paren InfoPath accepted the formula without any errors.

Only thing is all the field gets from this is the first three characters of the first name selected. Can you give me any guidance?

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

Hilary Stoupa — Mon, 14 Jul 2008 17:25:04 GMT

Great -- glad you have it all working. Yes, Notepad2 is *wonderful* for figuring out where all your parentheses go. Also, it has syntax highlighting availble, so if you open an XML file (for example) with it, it is easier to read.

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 17:15:06 GMT

Everything has been working great. Thanks again for all your help. That notepad2 is very helpful.

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

Hilary Stoupa — Mon, 14 Jul 2008 15:56:06 GMT

Your second fomula you are showing:

substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..") - 3)

Should be:

substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".." )) - 3)

Your last formula:

concat(substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..") - 3)), " - ", tbClientName)

Should be:

concat(substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".." )) - 3), " - ", tbClientName)

The reason you were only getting 3 characters returned is because of the way you have your closing parenthesis.

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 15:49:14 GMT

Ohhhhhh!!!!!

I need to put that extra parenthesis in front of the -3 like this:

concat(substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..")) - 3), " - ", tbClientName)

It seems to be working anyway.

Thanks so much!!

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 15:33:00 GMT

I'm sorry but I'm not sure I follow.

I took the first sample you sent me and it worked just fine: eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..")

When I applied that to your next line of code : substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..") - 3) is when I got that error message.

So you are saying I'm supposed to add another value in that concat line even though I didn't have to in the first example? I will want to concat another value but it's not part of the group or repeating section so I dont think it should do in this section. Should I not use concat if I only want to have name, name, name then another value out of the set?

This works except for the 3 characters but is very similar to what i want it to do:

I am going to download that editor now however, thanks for the advice.

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

Hilary Stoupa — Mon, 14 Jul 2008 14:54:56 GMT

Again, you have an error with your parentheses. You have closed the parentheses for the substring function, but not for the concat function. The error is saying 'Hey, weren't you concatenating here? Did you want to add another value?' It's looking for a comma and another value, and a closing parenthesis.

There is a great, free notepad replacement that has parentheses matching that I love for tracking down errors like these -- it is available here: http://www.flos-freeware.ch/notepad2.html

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 14:41:49 GMT

I did that and got this error:

Expected value type: ,

Actual value: end-of-string
concat(substring(eval(eval(../my:group5/my:rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(../my:group5/my:rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..") - 3), " - ", ../my:tbClientName)<--

Just so happens adding it in resolved the error but made it work incorrectly. I have no idea what this error is telling me....

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

Hilary Stoupa — Mon, 14 Jul 2008 14:40:11 GMT

You have a little issue w/ your parenthesis at the end of your formula. Instead of - 3)) try ) - 3)

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 14:28:52 GMT

I think I answered my own question, which I'll post later, but it seems my formula above doesnt quite give me the results I hoped for. Instead of removing the last 3 characters, it only posts 3 characters the first 3. I'm so close I can taste it. Any thoughts? This stuff is just a little over my head.

Just realized what you meant by trying that formula in a text box. Brilliant! ;-)

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

Hilary Stoupa — Mon, 14 Jul 2008 14:12:40 GMT

I'm glad you got your formula to work! Your text box is bound to a field. You can use that field in your subject line. To see what field your text box is bound to, select the text box and it will show you the name of the field. Of course, you can also use the formula that is working in the text box in the subject line of the email -- I had wanted you to try it in a text box because it is faster to see when you have it right -- you can just preview the form and fill out a few fields without having to take the extra step of submitting to see if it works.

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 14:03:23 GMT

Ok, I got it to work using both versions of code:

Formula: " substring(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), ".."), 1, string-length(eval(eval(rsResourceName, 'concat(my:tbResourceName2, " , ")'), "..") - 3)) "

Now for the million dolloar question: How can I put this repeating text box along with other static text boxes into a subject line?

Example: Subject = New Project Announcement: tbClientName - tbResourceName2(from the repeating section above)

or should this be another post?

Re: Problem with displaying repeating text box in an email subject / attachment name, upon form submission

e_stylzz — Mon, 14 Jul 2008 13:01:53 GMT

more info, sorry.

So i'm viewing my data source and realize i'm not referencing the repeating section directly, but the folder it puts it in.

group5

rsResourceName the repeating section

tbResoruceName the textbox in the repeating section.