I have a multi-select text field parsing

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
I have a multi-line text field, which can contain the names of 0 or more products. I need to find out the number of products in it. If that is not possible, I need to find out if it has 0, 1 or more than 1 product. What is the best way to do this?
Photo of Naveed Ahmed

Naveed Ahmed

  • 230 Points 100 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
Is the multi-line text field the best way to capture/store this information?  Are you able to use multi-select text fields, which are more easily referenced in formulas.  Can you leverage a table of products associated to a record?  Can you use checkboxes to represent products? 

Are your "products" the only content of the multi-line text field separated by semi-colons?
Photo of Naveed Ahmed

Naveed Ahmed

  • 230 Points 100 badge 2x thumb
yes... just to specify it is a summary multi-select text field
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
[Report Items 1] is my multi-select text field.  You will need to replace the formula with yours.

var number one=If(Part(ToText([Report Items 1]),1,";")<>"",1,0);
var number two=If(Part(ToText([Report Items 1]),2,";")<>"",1,0);
var number three=If(Part(ToText([Report Items 1]),3,";")<>"",1,0);
var number four=If(Part(ToText([Report Items 1]),4,";")<>"",1,0);
var number five=If(Part(ToText([Report Items 1]),5,";")<>"",1,0);
var number six=If(Part(ToText([Report Items 1]),6,";")<>"",1,0);
var number seven=If(Part(ToText([Report Items 1]),7,";")<>"",1,0);
var number eight=If(Part(ToText([Report Items 1]),8,";")<>"",1,0);
var number nine=If(Part(ToText([Report Items 1]),9,";")<>"",1,0);
var number ten=If(Part(ToText([Report Items 1]),10,";")<>"",1,0);
var number eleven=If(Part(ToText([Report Items 1]),11,";")<>"",1,0);
var number twelve=If(Part(ToText([Report Items 1]),12,";")<>"",1,0);
var number thirteen=If(Part(ToText([Report Items 1]),13,";")<>"",1,0);
var number fourteen=If(Part(ToText([Report Items 1]),14,";")<>"",1,0);
var number fifteen=If(Part(ToText([Report Items 1]),15,";")<>"",1,0);
var number sixteen=If(Part(ToText([Report Items 1]),16,";")<>"",1,0);
var number seventeen=If(Part(ToText([Report Items 1]),17,";")<>"",1,0);
var number eighteen=If(Part(ToText([Report Items 1]),18,";")<>"",1,0);
var number nineteen=If(Part(ToText([Report Items 1]),19,";")<>"",1,0);
var number twenty=If(Part(ToText([Report Items 1]),20,";")<>"",1,0);

var number sum=Sum($one,$two,$three,$four,$five,$six,$seven,$eight,$nine,$ten,$eleven,$twelve,$thirteen,$fourteen,$fifteen,$sixteen,$seventeen,$eighteen,$nineteen,$twenty);

$sum


Photo of Naveed Ahmed

Naveed Ahmed

  • 230 Points 100 badge 2x thumb
I am getting this error? Is it because it is a summary field from another table?
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,844 Points 3k badge 2x thumb
I cannot see the image very well; it's too grainy/blurry.  Can you snapshot just the image of the error? 
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
When I do the formula on the summary combined text field; even though it gives me an error, the formula still works
Photo of Naveed Ahmed

Naveed Ahmed

  • 230 Points 100 badge 2x thumb
Thanks. That was fruitful.