Newbie Questions

Re: Looking for a SUMIFS equivaliant in InfoPath

Jimmy — Mon, 13 Sep 2010 16:01:09 GMT

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)

Re: Looking for a SUMIFS equivaliant in InfoPath

SteveJones — Mon, 13 Sep 2010 14:02:11 GMT

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.

Re: Looking for a SUMIFS equivaliant in InfoPath

PMDBERIO — Wed, 31 Mar 2010 00:49:15 GMT

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.

Re: Looking for a SUMIFS equivaliant in InfoPath

Jimmy — Tue, 30 Mar 2010 16:53:40 GMT

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

Looking for a SUMIFS equivaliant in InfoPath

PMDBERIO — Tue, 30 Mar 2010 16:19:38 GMT

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.

Conditional Concat

PMDBERIO — Mon, 29 Mar 2010 16:43:36 GMT

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