Formula for Separator in Text Box with Concatenated Fields - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Formula for Separator in Text Box with Concatenated Fields

Last post 12-07-2010 01:09 PM by rmsphoto. 33 replies.
Page 1 of 3 (34 items) 1 2 3 Next >
Sort Posts: Previous Next
  • 09-30-2010 08:12 AM

    Formula for Separator in Text Box with Concatenated Fields

    I have a form with many sections that have this type of scenario using multiple checkboxes whose values are concatenated in a text box for promotion to a SharePoint Library:

    Field1 (checkbox with Value when cleared=blank; Value when checked=Apple)
    Field2 (checkbox with Value when cleared=blank; Value when checked=Orange)
    Field3 (checkbox with Value when cleared=blank; Value when checked=Banana)
    Field4 (checkbox with Value when cleared=blank; Value when checked=Grape)

    Text Box with Properties:  concat(Field1, Field2, Field3, Field4)

    The end result of the Text Box will be various combinations of the checkbox values such as:  Orange Grape OR Apple Banana Grape OR just Grape

    I need to separate the items with a comma and do not want a comma showing at the end of the string or at all if only one item is checked.  Does someone know how I can do this?

    Many many thanks.

  • 10-01-2010 06:44 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    I imagine that rules could achieve this codelessly, but you would need a lot of rules and conditions. For example:

    Rule #1

    Condition: Field1 is not blank, Field2 is blank, Field3 is blank, Field4 is blank

    Action: target field = Field1

    Rule #2

    Condition: Field1 is not blank, Field2 is not blank, Field3 is blank, Field4 is blank

    Action: target field = concat(Field1, "," , Field2)

    And so on. I think this will add up to 16 or 17 rules. Pretty tricky - good luck!

    Ernesto Machado
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™


  • 10-01-2010 07:42 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    You are correct that Rules can handle this and I have done that in some of the sections.  However, this has grown enormously and now there are other sections that require this same control setup but the conditions possibilities would probably hit 1,000 or more and so this work-around I've been doing for those smaller sections is just not feasible for these others now. 

  • 10-01-2010 07:56 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    Yes, you're right, the solution fails for a large number of fields.

    The alternative is to write code that does this. I'd propose you give qRules a shot if you'd rather stay away from writing code. The command DelimitedList will let you accomplish this via rules quite easily. You can learn more about qRules here.

    Ernesto Machado
    Qdabra® Software/ InfoPathDev.com
    The InfoPath Experts – Streamline data gathering to turn process into knowledge.™


  • 10-01-2010 08:52 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    This formula should do the job:

     substring(eval(eval((Field1 | Field2 | Field3 | etc...)[. != ''], 'concat(", ", .)'), '..'), 3, 10000)

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-01-2010 12:04 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    Thank you so much for your guidance.  I have attempted this on a practice form before adding to my real form (just in case) and I cannot preview to test it because I get a message that "InfoPath cannot open the selected form" - before adding this formula it opened in Preview fine.  Here's how my formula reads:

     substring(eval(eval((field1 | field2 | field3 | field4 | field5)[.!='""], 'concat(", ",.)'), ".."), 3, 10000)

     The single quotes I added before the end bracket turned into double quotes on their own, same with the single quotes in the last part of the last parenthesis:  '..') became "..")  -- I don't know if this makes a difference or not. The part of the formula from field 1 through the end bracket also became linked together automatically.

  • 10-01-2010 06:09 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    It looks like you have an extra quotation mark here:

    [.!='""] 

    It appears to be SINGLE QUOTE  DOUBLE QUOTE  DOUBLE QUOTE, when it should just be two single quotes or two double quotes.  Could you try to fix that, and if that doesn't work, click the Show XPath checkbox and paste the contents here?

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-01-2010 06:56 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    I must have made a typo when I wrote the post because it isn't like that in the formula.  I have removed it and rewritten it several times, even trying to use the Insert buttons in case InfoPath didn't like my own typed entries, and even though it continues to verify that the formula does not have any errors, each time I try to Preview the page so I can test it out, InfoPath pops up with the "cannot open the selected form" message.  I also tried as you suggested checking the box next to Edit XPath (advanced) where I could get inside the formula and be sure the quotes were only two single quotes and they were although they turn into two double quotes automatically after clicking OK.  Do you have any other idea about what might be wrong?  I really appreciate your input.  Here's a copy of my formula as it is in the form:

    substring(eval(eval((field1 | field2 | field3 | field4 | field5)[. != ""], 'concat(", ", .)'), ".."), 3, 10000) 

     

    Nalani 

     

     

  • 10-01-2010 08:39 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    Would you mind attaching your practice form to this thread?

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-01-2010 09:07 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    The form I was using to practice with and had saved with the formula won't open at all now (like it is a corrupted file).  I recreated the practice form and saved it again (and hope this one will open after it's closed), but I don't see a way to browse my local files and attach it to this thread.  I could email it to you directly if that would be ok unless you have another idea.  I tried the "Contact" and Send Jimmy an Email option but the tools are the same there too - no local file attachment tool that I could see.

     Nalani

  • 10-01-2010 11:06 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    If you click the Options tab when posting in this thread, you can attach a file.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-02-2010 12:48 PM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    I have attached a practice form that has a small section of my very large form which has many similar sections except that there are many more options available for the user to choose from than the number I've carved out for this practice form.  When you check the box next to Environmental, some Submatter options will display and multiples can be selected.  If you check Air Topics or Water Topics under Environmental Submatters, then more options display and multiples of those can be selected.  The Submatters are concatenated in a text box below the check boxes and also the Topics are concatenated in another text box below there.  The contents of those text boxes need to have commas separating the items unless only one is displayed, and not have a dangling comma after the last item.  That content will be submitted to a SharePoint library.

    I tried the formula you provided above on some other practice forms, but after applying the formula, I could not open them in Preview to test the formula out and after saving them could not open them up again.

    I really appreciate your looking at this.

    Nalani

  • 10-03-2010 06:11 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    I don't see that formula in the form you attached.  Did you attach the version of the form that doesn't have that formula in it?  And is InfoPath providing a Show Details button where you can see a more specific error message?

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 10-03-2010 06:31 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    You're right that the form I attached didn't have the separator formula in it - it only has the original concat formula that doesn't include separators between the items.  I couldn't find a way to send the form with the separator formula because as soon as I save it, the form will not open again.  When attempting to open or Preview to test before saving a form with that formula in it, I get this error message:

    "InfoPath cannot create a new blank form. InfoPath cannot open the form.  To fix this problem, contact your system administrator."  Show Details:

    Form template: file:///\\HOUP01A\Home$\NCARSON\PracticeTemplate.xsn
    Unspecified error

     

  • 10-03-2010 07:05 AM In reply to

    Re: Formula for Separator in Text Box with Concatenated Fields

    Well, you can attach it to the thread without re-opening it in InfoPath, right?

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
Page 1 of 3 (34 items) 1 2 3 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.