Text List Cannot Be Used In A Formula?

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

So, the new "text list" field type cannot be used in a formula?

Photo of JRogerson


  • 490 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
If you use

ToText([My Multi-Select field])

then it will be a text in a formula.
I don’t understand in English what you are trying to test for. The formula looks incomplete.
Photo of Wayne Major

Wayne Major

  • 340 Points 250 badge 2x thumb
I have again recently tested with converting a Text List using ToText() and it worked fine for me, Jim maybe there is another area of your formula that is causing the data type error. If there is more to your formula than the line you commented above can you show it?
Photo of Jim Harrison

Jim Harrison

  • 1,926 Points 1k badge 2x thumb
Here's the entire thingy. I commented out all the other code to pinpoint only that one piece of code and it doesn't work.

//21 fields
//List(", ",
//If([Acid Suit],"Acid Suit"),
//If(Contains([Boot Type(s)], ToText([Boot Type(s)])),
//If(ToText([Eye Wear Type(s)])
//If([Face Shield],"Face Shield"),
//If([Fall Arrest System],"Fall Arrest System"),
//If([Fire Retardant],"Fire Retardant"),
//If([Hard Hat],"Hard Hat"),
//If([Hearing Protection],"Hearing Protection"),
//If([Kevlar Sleeves],"Kevlar Sleeves"),
//If([Metatarsal Guards],"Metatarsal Guards"),
//If([Reflective Vest],"Reflective Vest"),
//If([Rubber Boots],"Rubber Boots")
If(Contains(ToText([Eye Wear Type(s)]),ToText([Eye Wear Type(s)])))
//& If([Boots], ", Boots: " & [Boot Type(s)])
//& If([Gloves],", Gloves: " & [Glove Type(s)])

Plain english:
If the multi-select field named Eye Wear Type(s) has any selections checked, then display those selections in this formula rich text field.
Try this

If(ToText([Eye Wear Type(s)]),<> "", ToText([Eye Wear Type(s)])),
Photo of Jim Harrison

Jim Harrison

  • 1,926 Points 1k badge 2x thumb
If(ToText([Eye Wear Type(s)])<>"", ToText([Eye Wear Type(s)]))

Photo of G.Macri


  • 878 Points 500 badge 2x thumb
Why not make a formula text field that parses the multi-select and returns the types minus the semi-colons.

BTW alot of my old formulas using ToText(multi-select field) have broken recently.
Photo of Jim Harrison

Jim Harrison

  • 1,926 Points 1k badge 2x thumb
Because that negates the multi-select and I might as well use check boxes. A multi-select is a way to clean up the form from a buzillion check boxes. I have no interest in parsing and then having to maintain that parse. Maybe this would be a little less painful if they would allow array 0-19 instead so we don't have to target specific select options based upon name.

Anyhow Coach found the workaround until QB fixes the root.