How do you create a formula to count the number of items selected in a multi select list and return 0 if there are no items selected?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm trying to create a formula that will count the number of items selected in a multi-select list.  I created a new formula - numeric field that will return a total score depending on the number of items selected in a multi-select list field however I can't find a count function that will work when items are selected randomly on the list.

Photo of Sarah

Sarah

  • 20 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
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,";"))

)
Photo of Sarah

Sarah

  • 20 Points
Perfect - thank you - that worked a treat :smile:
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,064 Points 20k badge 2x thumb
This will not work if one of the choices has a semicolon in it. Better to use script and a regular expression:

var str = '";";a;b;e';
var num = str.match(/(".*?"|[^";\s]+)(?=\s*;|\s*$)/g).length;
console.log(num);