Two (3?) Questions on Concatenation - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Two (3?) Questions on Concatenation

Last post 06-13-2012 10:59 AM by Jimmy. 18 replies.
Page 1 of 2 (19 items) 1 2 Next >
Sort Posts: Previous Next
  • 05-22-2012 10:39 AM

    Two (3?) Questions on Concatenation

    I have been banging my head on this one.  I've found a number of replies about one of these questions, but the formulas provided to others... we'll I can't figure out how to replicate them in my form.

    1.  I have a multi-selction check box... we'll call it Organization that pulls from a secondary datasource.  I'd like to set a text field's value (we'll call that field Operations_concat) with a concatenated view of the selections made in the multi-selection field separated by semi-colons (and not to show the "extra" semi-colons.  I've seen various formulas... including this for another form:

     substring(eval(eval(field1[. != ""], 'concat(";", .)'), ".."), 2)         Where the field1 would be my Organization field.   However, I run into issues determining which of the periods are periods and which are representing fields in the form...  so I just am not sure of the steps to set that up in my form.

    Can anyone help?  Sorry that I need this one almost spoon fed, but I'm clueless on the [. != ""] portion and what is going into the concatenation formula.

     Similarly, I'd like to do this with repeating dropdowns that are pulling from a data source... is that possible?

     

    2. My other concatenating issue is with individual check boxes... how can I write the concatenating formula where items are separated by commas or semi-colons where there are only as many commas/semi-colons as needed.  i.e. I don't end up with....   choice1, choice 2, , , ,  choice 6, , ,   in the field.

     Thank you!!!

  • 05-22-2012 10:49 AM In reply to

    Re: Two (3?) Questions on Concatenation

    1.  I suggest using the Add Function... button to add the two eval()s, and using the Select Field... button to select the field.  the rest of it (the substring and  [. != ""], 'concat(";", .)'), ".."), 2) part) you can just enter manually; it shouldn't change.

    2. Here again you can use the double-eval trick, but instead of a single field, you would have something like this:

    substring(eval(eval((firstField | secondField | thirdField | ... | lastField)[. != ''], 'concat(",", .)'), ".."), 2)

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 05-22-2012 12:02 PM In reply to

    Re: Two (3?) Questions on Concatenation

    Thanks Jimmy... I tried testing with the text  boxes, but am getting an error message on the formula (which is what was happening before as well).  Here's what I'm entering:

    eval(eval(ImpctAudBllng, ImpctAudBllngAllnc, ImpctAudBllngEmpAcct, ImpctAudBllngNE)[. !=""], 'concat(";", .)'), ".."), 2)

    And the error I'm initially getting is...

    Expected value type: end-of-string

    Actual value: ,
    eval(eval(../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllng, ../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllngAllnc, ../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllngEmpAcct, ../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllngNE)[. !=""], 'concat(";", .)')-->,<-- ".."), 2)

    It appeared the comma was causing the issue so I tried removing the space and got the same error.  Thoughts? 

     

  • 05-22-2012 12:25 PM In reply to

    Re: Two (3?) Questions on Concatenation

    It appears you're missing the substring( at the beginning (that was my mistake), a second left parenthesis after the second eval(, and the fields should be separated by pipe symbols |, not commas.
    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 05-22-2012 02:15 PM In reply to

    Re: Two (3?) Questions on Concatenation

    Looks like I'm running into the same problem another person had... when I set the default formula on the field and try to preview, I get an error saying it isn't a valid form template and won't preview.  If I remove the formula, the preview works just fine.  The formula now looks like this:

    substring(eval(eval((ImpctAudBllng | ImpctAudBllngAllnc | ImpctAudBllngEmpAcct | ImpctAudBllngNE)[. != ""], 'concat(",", .)'), ".."), 2)

    Xpath:  substring(xdMath:Eval(xdMath:Eval((../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllng | ../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllngAllnc | ../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllngEmpAcct | ../../../../my:SecImptAud/my:SecImptAudBREC/my:ImpctAudBllngNE)[. != ""], 'concat(",", .)'), ".."), 2)

    Not sure if it's a formula issue with me typing something wrong, a placement issue (putting in the Default Value field of text box), or something else bizarre.

    Haven't even been able to test the multi-selection one yet... oh technology and learning it...

     

  • 05-22-2012 04:57 PM In reply to

    Re: Two (3?) Questions on Concatenation

    The formula certainly looks like it's alright.  Could you try saving, closing and reopening the template?  Sometimes InfoPath seems to just get into a bad mood and need a fresh start.
    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 05-23-2012 08:00 AM In reply to

    Re: Two (3?) Questions on Concatenation

    I've noticed IP's tendency to get irritable when open too long.  I actually restarted computer beside shutting down IP, but I still get the same error when trying to preview.  That the form template isn't valid and there's an unspecified error.  It's definitely directly connected to the formula in the default value field as the error goes away when I remove it.

     I also just got a chance to test the concatenating with the multi-select field.  Getting error...

     The formula is in like this:  substring(eval(eval((.[. !=""], 'concat(";", .)'), ".."), 2)

    The error I'm getting is: 

    Expected value type: )

    Actual value: [
    substring(eval(eval((.-->[<--. !=""], 'concat(";", .)'), ".."), 2)

    If I take out the double (( after the 2nd eval, I get this error:

    Expected value type: ,

    Actual value: [
    substring(eval(eval(.-->[<--. !=""], 'concat(";", .)'), ".."), 2)

    Thoughts?

  • 06-07-2012 07:16 AM In reply to

    Re: Two (3?) Questions on Concatenation

    Anyone able to assist with this?  Still struggling with this stupid formula... I've tried another found in an IP book, but the same issue occurs with the bracket. 

  • 06-07-2012 08:00 AM In reply to

    Re: Two (3?) Questions on Concatenation

    Please try replacing that first dot with the following:

    ../my:MultiSelectFieldName

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 06-07-2012 09:40 AM In reply to

    Re: Two (3?) Questions on Concatenation

    It looks like you also have an extraneous left parenthesis after the second eval.
    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 06-07-2012 10:21 AM In reply to

    Hi Jimmy, thanks for responding...and catching me on the other post.  Are you talking about the first dot in the entire formula or from the 'not an empty string' portion of the formula?  Either way... the first dot in the formula is the multi-select field and I have actually tried entering the field into the 'not an empty string' portion as well and it still freaks out about the bracket. 

    I just don't get what the heck is going on.  IP just doesn't like [. != ""] when I enter it...

     

     

  • 06-07-2012 10:28 AM In reply to

    Re: Two (3?) Questions on Concatenation

    I changed it to this, and it worked:

    substring(xdMath:Eval(xdMath:Eval(../my:RqtType[. != ""], 'concat("; ", .)'), ".."), 2)

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

    Re: Two (3?) Questions on Concatenation

    Ok... I'm so confused.  So when you entered it that way... were you using the Insert Field Group button?  I literally feel like banging my head against something incredibly hard.

  • 06-07-2012 10:38 AM In reply to

    Re: Two (3?) Questions on Concatenation

    Oh, oh, oh... just saw/learned I can copy the Xpath of the field.  Got it.  So, I put it in my real form and IP now accepts the formula BUT if I uncheck something, it stays in my text field...  so, how do I remediate that? 

    And, FYI, you are a rockstart... a patient, patient rockstar

  • 06-07-2012 10:41 AM In reply to

    Re: Two (3?) Questions on Concatenation

    To fix that, you need to add a similar formula as the default value for the text field, except instead of this time, instead of manually using ../my:RqtType, you would use the select field button.
    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
Page 1 of 2 (19 items) 1 2 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.