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!

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 textCreated a new Formula Text field [ERPProductsText]List:� Product1� Product2Formula: ToText(ERPProductsText]Will result in:Product1;Product2Create 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.

not tested but try this.If there is a syntax error please post the your formula and the whole error message.var text value = ToText();Count(Trim(Part($value,1,";"))<>"",Trim(Part($value,2,";"))<>"",Trim(Part($value,3,";"))<>"",Trim(Part($value,4,";"))<>"",Trim(Part($value,5,";"))<>"",Trim(Part($value,5,";"))<>"")------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

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

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!

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

30 Replies

GeorgeKhairalla
Qrew Cadet
9 years ago
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.
CharleneWilbur
Qrew Trainee
4 years ago
Count(";",ToText([Field]))+1

------------------------------
Charlene Wilbur
------------------------------
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
  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
    Qrew Legend
    4 years ago
    Can you post your whole formula?
    
    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
GeorgeKhairalla
Qrew Cadet
9 years ago
Thanks Mark! this works a treat, and removes the management aspect of that field. Cheers!
ArchiveUser
Qrew Captain
9 years ago
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,";")))
)
_anomDiebolt_
Qrew Elite
9 years ago
>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
- JosephReynolds
  Quickbase Staff
  3 years ago
  This comment aged like milk.
  
  ------------------------------
  Joseph Reynolds
  ------------------------------
ArchiveUser
Qrew Captain
9 years ago
It's good to be a consultant...
KevinBoyle
Qrew Trainee
6 years ago
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
- QuickBaseCoachD
  Qrew Captain
  6 years ago
  I suggest you post your formula if you say it doesn�t work.
- KevinBoyle
  Qrew Trainee
  6 years ago
  Sorry for the delay. I have just copied the formula that you have shared above and changed the multi select field to the field in my app...
  
  var text value = ToText([Caller Needs, Interests, Drivers]);
  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,";")))
  )
  
  I have this in formula numeric field and trying to hopefully get a pie graph out of the data.
  - FadyIbrahim
    Qrew Member
    6 years ago
    Hi, I am trying this out but am getting a Syntax Error: Extra characters beyond the end of the formula. I copied the formula below. I am a bit of an amateur so forgive any silly mistakes. Any ideas?
    
    New_variable = ToText();
    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,";")))
    )
    
    ------------------------------
    Fady Ibrahim
    ------------------------------
- QuickBaseCoachD
  Qrew Captain
  6 years ago
  Try changing Sum to Count
GeorgeKhairalla
Qrew Cadet
6 years ago
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.
QuickBaseCoachD
Qrew Captain
6 years ago
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