Discussions

Expand all | Collapse all

Formula to Summarize Multi Select Text by Group rather than Groupings

Johanna Evans

Johanna Evans04-25-2019 17:48

Johanna Evans

Johanna Evans04-26-2019 12:24

  • 1.  Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 14:36

    Hi QuickBase Friends,

    I have a multi select text field that I need to summarize by group rather than by grouping.

    I started the formula with the following: (I am getting a message expecting a textlist, which is what I thought I provided). I have lost myself in my own formula. Have others run into this?

    var textlist counterif=textList(",",ToBoolean([Linked to Priority Strategic Aim]))
    If (Contains([PC 1.0 Readmission rate],"PC 1.0 Readmission rate"),1,0),
    If (Contains([PC 2.0 Hospital Acquired Infection rate],"PC 2.0 Hospital Acquired Infection rate"),1,0),
    If (Contains([PC 3.0 Clinical Care Coordination],"PC 3.0 Clinical Care Coordination"),1,0),


  • 2.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 14:50
    Can you explain further.

    Are you saying that you have a list of say 6 choices but in your mind some of them are Group A and some are Group B and you want to know how many A's and how many B's?


  • 3.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 14:58

    I have a list of multi text selections that have about 25 items.

    When I summarize the fields, a table displays the selections by grouping:

    Item 1      = 1
    Item 1, 5  = 1
    Item 1, 3  = 1

    This is not what I am looking for, therefore, I thought I could write a formula that recognized the group rather than the grouping.

    Item 1 = 3
    Item 3 = 1
    Item 5 = 1



  • 4.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 15:03
    Are you trying to make 25 fields each to hold the count of a 1 if the multi select contains the nth choice and a 0 otherwise?


  • 5.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 17:37
    Yes - of the 25 rows I want a count of how often item 1 is selected (in the example is 3), rather than item 1, then item 1 and 3 counted as a grouping, and item 1 and 5 as a grouping.


  • 6.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 17:44
    Are you willing to make 25 formula fields if I help you with the first one?


  • 7.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 17:48
    Sure will


  • 8.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 17:55
    I realized that this is simpler than i thought

    Try this

    var text Selections = ToText([My Multi select field]);
    var text SearchTerm = "PC 1.0 Readmission rate";

    IF(Contains($Selections, $SearchTerm),1,0)

    Then if that works OK, just copy the field 24 times and replace the formula variable for the SearchTerm.




  • 9.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 18:06
    The formula is not recognizing my multi text field. I changed the formula to textlist but that is not working either.


  • 10.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 18:08
    Don't worry about the yellow warning. It's a false warning. Does the formula save OK?


  • 11.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 18:10
    Thank you for the support. I will try a few other things.


  • 12.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 18:18
    oh! Sorry it is not saving I am getting a formula syntax error


  • 13.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 18:25
    Please post your formula and the error.  I can't see your screen from here. :)


  • 14.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 19:58

    Test 1

    var textlist Selections = ToTextlist([Linked to Priority Strategic Aim]);
    var text SearchTerm = "PC 1.0 Readmission rate";

    IF(Contains($Selections, $Search Term),1,0)

    If (Contains([PC 1.0 Readmission rate],"PC 1.0 Readmission rate"),1,0)


    Formula syntax error

    Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.

    Test 2

    var text Selections = ToText([Linked to Priority Strategic Aim]);
    var text SearchTerm = "PC 1.0 Readmission rate";

    IF(Contains($Selections, $SearchTerm),1,0)


    If (Contains([PC 1.0 Readmission rate],"PC 1.0 Readmission rate"),1,0)



    Formula syntax error

    There are extra characters beyond the end of the formula.


    var text Selections = ToText([Linked to Priority Strategic Aim]);
    var text SearchTerm = "PC 1.0 Readmission rate";

    IF(Contains($Selections, $SearchTerm),1,0)


      If (Contains([PC 1.0 Readmission rate],"PC 1.0 Readmission rate"),1,0)




  • 15.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-25-2019 20:01
    Why do you have the last line in the formula at all???

    It should just be

    var text Selections = ToText([Linked to Priority Strategic Aim]);
    var text SearchTerm = "PC 1.0 Readmission rate";

    IF(Contains($Selections, $SearchTerm),1,0)




  • 16.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-26-2019 11:49

    var text Selections = ToText([Linked to Priority Strategic Aim]);
    var text SearchTerm = "PC 1.0 Readmission rate";

    IF(Contains($Selections, $SearchTerm),1,0)


    Formula error
    The field is text but the formula returns a number result.



  • 17.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-26-2019 11:52
    The point of this field is to be used in a summary report to count the number of selections of that term. Hence it should be a formula numeric field type.


  • 18.  RE: Formula to Summarize Multi Select Text by Group rather than Groupings

    Posted 04-26-2019 12:24
    Got it! thank you for your support!