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

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

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:

var Numeric ERPCounter = List(",",ToText[ERP Products]));   // to get the text list of the products, then have a "," delimited, so that the next (Count) function would work.

Then used:

Count($ERPCounter)

That doesn't seem to be working.

Trying to find a way to count those products. Any ideas?

Thanks!

Photo of George

George

  • 30 Points

Posted 3 years ago

  • 0
  • 2
Photo of George

George

  • 30 Points
I'm not sure if this is the best way to do it, but I did figure a way. It requires a bit of maintenance to the formula as products are added to the list, but here's what I did, in case it's helpful to anyone:

My [ERP Products] field is a multi-select text
Created a new Formula Text field [ERPProductsText]
List:
√ Product1
√ Product2
Formula: ToText(ERPProductsText]
Will result in:
Product1;Product2

Create a Formula - Numeric field [ProductCounter]
Sum(
If (Contains([ERPProductsText],"Product1"),1,0),
If (Contains([ERPProductText],"Product2"),1,0)
)

For every new entry in the Product list, a new line is needed in the counter formula.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

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

)
Photo of George

George

  • 30 Points
Thanks Mark! this works a treat, and removes the management aspect of that field. Cheers!
Photo of David

David

  • 20 Points
Leveraging Mark's awesome formula to pull the Sum...

var text value = ToText([MultiSelectFieldName]);
Sum(
ToNumber(Trim(Part($value,1,";"))),
ToNumber(Trim(Part($value,2,";"))),
ToNumber(Trim(Part($value,3,";"))),
ToNumber(Trim(Part($value,4,";"))),
ToNumber(Trim(Part($value,5,";"))),
ToNumber(Trim(Part($value,6,";"))),
ToNumber(Trim(Part($value,7,";"))),
ToNumber(Trim(Part($value,8,";"))),
ToNumber(Trim(Part($value,9,";"))),
ToNumber(Trim(Part($value,10,";"))),
ToNumber(Trim(Part($value,11,";"))),
ToNumber(Trim(Part($value,12,";"))),
ToNumber(Trim(Part($value,13,";"))),
ToNumber(Trim(Part($value,14,";"))),
ToNumber(Trim(Part($value,15,";"))),
ToNumber(Trim(Part($value,16,";"))),
ToNumber(Trim(Part($value,17,";"))),
ToNumber(Trim(Part($value,18,";"))),
ToNumber(Trim(Part($value,19,";"))),
ToNumber(Trim(Part($value,20,";")))
)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,024 Points 20k badge 2x thumb
>This formula will require no maintenance.

When QuickBase finally replaces the Formula Language with JavaScript that "no maintenance" Formula will have to be replaced with JavaScript. It is inevitable!

Atwood's Law
http://cryptochaos.com/atwoods-law-any-application-that-can-be-writt
Photo of David

David

  • 20 Points
It's good to be a consultant...