Conditional Concat - InfoPath Dev
in

InfoPath Dev

Conditional Concat

Last post 09-13-2010 09:01 AM by Jimmy. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next

  • 03-29-2010 09:43 AM

    Conditional Concat

    Reply Contact
    I have several dropdowns with duplicated data. There are 25 different departments that can be assigned one of the 16 dropdown options (tasks).  I want a field below them to populate with the names of the organizations assigned to that task. I used the concat function to display the identified department name but right now it is yes/no. I need to be something to the effect of  "IF assigned [this task] then display concat rule  else (display nothing). This needs to be applied to all 25 dropdowns and be collected in that one txt box. This is similar to the SUMIF function in Excel, only instead of SUMIF it would be "displayconcatIF" :)What I would like is a txt box that shows all of the department names that meet those two criteria. I read many of the blogs here and have danced around the solution a couple of times now. Any help would be appreciated. Thank you in advance.

    PMDBERIO

    "Everytime I read Jane Austin, I want to dig up her bones and hit her in the head with her own femur"
    — Mark Twain

  • 03-30-2010 09:19 AM In reply to

    Looking for a SUMIFS equivaliant in InfoPath

    Mark as Answer
    Reply Contact

    I posted this message earlier and didn't get any response, and I assume that is do to how I worded the question:

     I work for a large business with 25 different departments. These departments can be given one of 15 tasks in addition to an optional task (which makes 16). I have created check boxes for each department and then a corresponding drop-down box with the 16 tasks to select from.  In my InfoPath form (tied to SharePoint) I would like 16 “repository fields” that display each department assigned to a specific task. Multiple departments can be assigned the same task. For example: Accounting, Publicity and Outreach departments may all be tasked with providing “INFO” on a subject. In the corresponding “INFO” field I would like it to auto populate with “Accounting, Publicity, Outreach” so at a glance, or in SharePoint oversight as to who is assigned to a specific task is easy.I have solved the Concat function issues as well as all conditional formatting but how to have the repository cell search all 25 departments for selected ones. then searching for a specific task (RULE criteria) to populate said repository cell has left me at my wits end. I have gone as far as to try to edit in VB but either it is not is simple as I want it to be or it is above my abilities. My strengths are in Excel and Access and I would compare this dilemma to the SUMIFs function in Excel only I do not need a summation, just a representation of ALL fields meeting that criteria.Any help would be appreciated, and if I have worded this request poorly I apologize.

     

    "Everytime I read Jane Austin, I want to dig up her bones and hit her in the head with her own femur"
    — Mark Twain

  • 03-30-2010 09:53 AM In reply to

    Re: Looking for a SUMIFS equivaliant in InfoPath

    CommaList.xsn
    Mark as Answer
    Reply Contact

    I think the attached XSN does what you describe.  All of the logic is in the fairly complicated formula on the default value of my:field4, which I'll reproduce below, but you only need to modify the underlined parts to adapt this to your own scenario:

    concat(xdMath:Eval(xdMath:Eval(/my:myFields/my:group1/my:group2[my:field1 = "yes"][position() != last()]/my:field2, 'concat(., ", ")'), ".."), /my:myFields/my:group1/my:group2[my:field1 = "yes"][last()]/my:field2)

    What you need to replace is:

    /my:myFields/my:group1/my:group2  - The path to your repeating group

    my:field1 - The node bound to the checkbox

    yes - The value the checkbox has when it's checked

    my:field2 - The node with the value you want to display

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software

  • 03-30-2010 05:49 PM In reply to

    Re: Looking for a SUMIFS equivaliant in InfoPath

    Mark as Answer
    Reply Contact

    Jimmy,

    Many thanks. Your formula looks great and helps plenty. However I do not have the departments set-up in a repeating field. They are all independent. How can I modify what you gave me and add all the departments to it collectively. Can I just put them in 1 by one and have them seperated by a comma or parentheses?

    Thanks for the reply. I greatly appreciate it.

     
    "Everytime I read Jane Austin, I want to dig up her bones and hit her in the head with her own femur"
    — Mark Twain

  • 09-13-2010 07:02 AM In reply to

    Re: Looking for a SUMIFS equivaliant in InfoPath

    Mark as Answer
    Reply Contact

    Jimmy,

    This formula is a lifesaver.

     I've been trying for ages to figure out how to concatenate values from the DisplayName of a contact selector into a single text node without code, and this worked like a charm! I had no idea that you could use two qualifying conditions like this.

    Thanks for your efforts here.

     

    Steve

  • 09-13-2010 09:01 AM In reply to

    Re: Looking for a SUMIFS equivaliant in InfoPath

    Mark as Answer
    Reply Contact

    Glad to help, Steve!

     This question has come several times over the past few weeks, and over time, I have found a simpler way to express this formula.  What I currently recommend is:

    substring(xdMath:Eval(xdMath:Eval(path to field (with any relevant filtering), 'concat(", ", .)'), ".."), 3)

    So the formula in my example above would become:

     substring(xdMath:Eval(xdMath:Eval(/my:myFields/my:group1/my:group2[my:field1 = "yes"]/my:field2, 'concat(", ", .)'), ".."), 3)

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