Forum Discussion

KarenHenke's avatar
KarenHenke
Qrew Cadet
5 years ago

Removing duplicate values from a formula field

Hello!

I have a formula that says:

var text MultiInTextFormat = ToText([Breeds]);

List(";",
If(Contains($MultiInTextFormat, "Yorkie"), "Dog"),
If(Contains($MultiInTextFormat, "Doberman"), "Dog"),
If(Contains($MultiInTextFormat, "Clydesdale"), "Horse"),
If(Contains($MultiInTextFormat, "Amnesia"), "Cat"),
If(Contains($MultiInTextFormat, "Hound"), "Dog"),
If(Contains($MultiInTextFormat, "Persian"), "Cat"),
If(Contains($MultiInTextFormat, "Morgan"), "Horse"),
If(Contains($MultiInTextFormat, "American Warmblood), "Horse"))

Often times, this formula generates a list like this:
Dog;Dog;Dog   or   Cat;Cat;Dog;Horse

How can I remove the duplicates from that list? Can I add something to this formula? I'm ok with adding a new field that will remove the duplicates, I just don't know how to do that.

Also, how can I get that de-duped list to put each value on a separate line within the field (instead of a string separated by ";")  

So if the values selected in [Breeds] are
American Warmblood;Hound;Morgan;Persain

I want the field to look like this:
Cat
Dog
Horse

Thank you for any assistance!!




------------------------------
Karen Henke
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    This app begs for a table solution where the children are the animals instead of being drawn from a multi select field, but this works,
    .

    var text MultiInTextFormat = "American Warmblood;Hound;Morgan;Persian";


    var text Yorkie = If(Contains($MultiInTextFormat, "Yorkie"), "Dog");
    var text Dobmerman = If(Contains($MultiInTextFormat, "Doberman"), "Dog");
    var text Clydesdale =If(Contains($MultiInTextFormat, "Clydesdale"), "Horse");
    var text Amnesia = If(Contains($MultiInTextFormat, "Amnesia"), "Cat");
    var text Hound = If(Contains($MultiInTextFormat, "Hound"), "Dog");
    var text Persian = If(Contains($MultiInTextFormat, "Persian"), "Cat");
    var text Morgan = If(Contains($MultiInTextFormat, "Morgan"), "Horse");
    var text AmericanWarmblood = If(Contains($MultiInTextFormat, "American Warmblood"), "Horse");

    var text AnimalTypesOne = $Yorkie;

    var text AnimalTypesTwo = If(not Contains($AnimalTypesOne,$Dobmerman),
    List("\n", $AnimalTypesOne, $Dobmerman), $AnimalTypesOne);

    var text AnimalTypesThree = If(not Contains($AnimalTypesTwo,$Clydesdale),
    List("\n", $AnimalTypesTwo, $Clydesdale), $AnimalTypesTwo);

    var text AnimalTypesFour = If(not Contains($AnimalTypesThree, $Amnesia),
    List("\n", $AnimalTypesThree, $Amnesia), $AnimalTypesThree);

    var text AnimalTypesFive = If(not Contains($AnimalTypesFour, $Hound),
    List("\n", $AnimalTypesFour, $Hound), $AnimalTypesFour);

    var text AnimalTypesSix = If(not Contains($AnimalTypesFive, $Persian),
    List("\n", $AnimalTypesFive, $Persian), $AnimalTypesFive);

    var text AnimalTypesSeven = If(not Contains($AnimalTypesSix, $Morgan),
    List("\n", $AnimalTypesSix, $Morgan), $AnimalTypesSix);

    var text AnimalTypesEight = If(not Contains($AnimalTypesSeven, $AmericanWarmblood),
    List("\n", $AnimalTypesSeven, $AmericanWarmblood), $AnimalTypesSeven);

    $AnimalTypesEight

    ------------------------------
    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
      Mark!! 
      Once again, you are brilliant!! 
      I recreated the scenario using the exact data above (in a test table) and it works perfectly.

      When I try to use it in my real scenario, QB doesn't like the "$" after "AnimalTypesOne ="

      I would love to connect with you offline and ask for your help to fix this (I would compensate you for your time, of course). If you wouldn't mind emailing me directly, I can set up some time for us to talk.  Maybe there is a better/simpler way to accomplish what I'm trying to do.
      Thank you!
      kabarone@cisco.com

      P.S. I found out today that we have a mutual friend, Robin Hamilton of InEvidence  :)

      ------------------------------
      Karen Henke
      ------------------------------