Skip to main content
Question

Removing duplicate values from a formula field

  • August 13, 2020
  • 0 replies
  • 67 views

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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
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
------------------------------
Mark Shnier. Your Quickbase Coach
Terms & ConditionsAccessibility statement