Discussions

Expand all | Collapse all

Removing duplicate values from a formula field

  • 1.  Removing duplicate values from a formula field

    Posted 08-13-2020 12:41

    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
    ------------------------------


  • 2.  RE: Removing duplicate values from a formula field

    Posted 08-13-2020 13:12
    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
    ------------------------------



  • 3.  RE: Removing duplicate values from a formula field

    Posted 08-13-2020 15:46
    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
    ------------------------------