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

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.

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,";"))

)

Thanks Mark! this works a treat, and removes the management aspect of that field. Cheers!

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,";")))
)

>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

It's good to be a consultant...

It seems that the above solution has worked for people previously, but I am having a hard time with it. When I insert my multi-select text field after the ToText command, I get an error stating that was not expecting a textlist. It will still save, but doesn't return any results. I'm fairly new to this platform, so I'm not sure if I'm just doing something wrong or if this really isn't possible. Any help you can offer would be greatly appreciated. Thanks

I hope my memory serves me correct.  I **think**, there is a bug in the ToText function with the "Text Lists". The way to know is, if it saves anyway, despite the error, you might be ok. 

Hopefully someone from the QuickBase can confirm or deny this.

There is no bug in the ToText function other than the preview of the syntax editor while you were entering the formula gives a false warning. But it will save just fine and it will work just fine

Thanks for confirming! I recall having seen something in another thread a couple days ago ( I think from you ). Just didn�t remember if it was the function itself or the formula editor itself.

Count(";",ToText([Field]))+1

------------------------------
Charlene Wilbur
------------------------------