Forum Discussion

GeorgeKhairalla's avatar
GeorgeKhairalla
Qrew Cadet
9 years ago

Trying to ""Count"" the number of selections in a multi-select text field

Hi, I have a multi-select text field : [ERP Products] I'm trying to have a Formula - Numeric field, to count the number of products that were selected. I have tried something like this: ...
Formulas and functions
Tables and fields
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
9 years ago
This formula will require no maintenance.  Just don't have a semi colon in one of the choices though.



Try this

var text value = ToText([Multi select]);



Count(

Trim(Part($value,1,";")),

Trim(Part($value,2,";")),

Trim(Part($value,3,";")),

Trim(Part($value,4,";")),

Trim(Part($value,5,";")),

Trim(Part($value,6,";")),

Trim(Part($value,7,";")),

Trim(Part($value,8,";")),

Trim(Part($value,9,";")),

Trim(Part($value,10,";")),

Trim(Part($value,11,";")),

Trim(Part($value,12,";")),

Trim(Part($value,13,";")),

Trim(Part($value,14,";")),

Trim(Part($value,15,";")),

Trim(Part($value,16,";")),

Trim(Part($value,17,";")),

Trim(Part($value,18,";")),

Trim(Part($value,19,";")),

Trim(Part($value,20,";"))

)
GeorgeKhairalla's avatar
GeorgeKhairalla
Qrew Cadet
4 years ago
I know this is an old thread, but I was searching for a solution and stumbled on my own question :)

I have a different scenario requiring the same formula. In this case, I am creating a Summary Report, with a Report Formula containing the above, to DISTINCT count the number of items in the text list.

It's working great, except for one detail: It's counting the empty values. Is there a way to adjust the above formula to avoid the empty values?

I have tried the Nz() but that didn't seem to do the trick.
I also tried to add an if statement on each Trim Line
If(Trim(Part($value,1,";")) <> "", Trim(Part($value,1,";")),"") 

That also didn't seem to work.

Any other ideas?

------------------------------
George Khairallah
CTO
gotomyerp, LLC
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Can you post your whole formula?

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • GeorgeKhairalla's avatar
      GeorgeKhairalla
      Qrew Cadet
      4 years ago
      Sure.. it's actually exactly the one suggested in this thread:

      var text value = ToText([Product Add-On Apps]);
      Count(
      Trim(Part($value,1,";")),
      Trim(Part($value,2,";")),
      Trim(Part($value,3,";")),
      Trim(Part($value,4,";")),
      Trim(Part($value,5,";")),
      Trim(Part($value,6,";")),
      Trim(Part($value,7,";")),
      Trim(Part($value,8,";")),
      Trim(Part($value,9,";")),
      Trim(Part($value,10,";")),
      Trim(Part($value,11,";")),
      Trim(Part($value,12,";")),
      Trim(Part($value,13,";")),
      Trim(Part($value,14,";")),
      Trim(Part($value,15,";")),
      Trim(Part($value,16,";")),
      Trim(Part($value,17,";")),
      Trim(Part($value,18,";")),
      Trim(Part($value,19,";")),
      Trim(Part($value,20,";"))
      )

      This formula is being used as a report formula field in the Summary Report.

      ------------------------------
      George Khairallah
      CTO
      gotomyerp, LLC
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        try this

        var text value = ToText([Product Add-On Apps]);
        Count(
        Trim(Part($value,1,";"))<>"",
        Trim(Part($value,2,";"))<>"",
        Trim(Part($value,3,";"))<>"",
        etc etc


        Trim(Part($value,20,";"))<>"")

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------