Forum Discussion

KarenHenke's avatar
KarenHenke
Qrew Cadet
5 years ago

Brain Teaser: Translating data from a multi-select text field to formula text field

Hello,
I'm going to to my best to explain a super complicated situation :) I'm hoping one of you brilliant people can help me.

There are values in Field A that I need translated (for lack of a better word) and put into Field B.
Field A is a multi-select text field. 

I need the formula in Field B to say (for example)

If "Dog" appears in the list in [field A] , then add "Poodle;Yorkie;Doberman" to [field B],
If "Cat" appears in the list in [field A], then add "Tabby;Siamese" to [field B],
If "Turtle" appears in the list in [field A], Then add "Box" to [field B],
otherwise leave the field blank
Separate the values with a semi-colon

I don't know if this would be an If formula, or a List formula, or Case formula??? SearchandReplace formula?
Thanks in advance for any thoughts!

------------------------------
Karen Henke
------------------------------
Formulas and functions
Tables and fields
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    np,  try this

    var text MultiInTextFormat = ToText([[Field A]);

    List(";",
    IF(Contains($MultiInTextFormat, "Dog", "Poodle;Yorkie;Doberman"),
    IF(Contains($MultiInTextFormat, "Cat", "Tabby;Siamese"),
    IF(Contains($MultiInTextFormat, "Turtle", "Box"))



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • KarenHenke's avatar
      KarenHenke
      Qrew Cadet
      5 years ago
      Thank you Mark!
      When I try this formula, the first comma after "Dog" is highlighted with an error that says "Expecting )"

      Would love your thoughts on how to fix that.

      ------------------------------
      Karen Henke
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        Right, it was expecting more brackets

        fix fix this part here to add three missing  ),  one on each line 

        List(";",
        IF(Contains($MultiInTextFormat, "Dog"), "Poodle;Yorkie;Doberman"),
        IF(Contains($MultiInTextFormat, "Cat"), "Tabby;Siamese"),
        IF(Contains($MultiInTextFormat, "Turtle"), "Box"))




        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------