In-App Formulas - Removing Duplicates From A List or String
I'm working on a formula to concatenate values from multiple multi-select text fields while removing any duplicates in the output. My goal is to create a unique, semicolon-delimited list that’s easy to process further. Here’s a breakdown of the fields I’m working with:
- [Related Modification - Operator Access]
- [Related Project - Operator Access]
- [Related Phase - Operator Access]
- [Note Replying To - Operator Access]
- [Related User - Affiliation]
Quickbase’s formula language doesn’t support loops, assignments, or a direct Unique() function to filter out duplicates. Here are some of the approaches I’ve tried:
- Using Conditional If Statements
I set up If statements that check each field’s value with Contains to see if it’s already present in the accumulating list. While this approach works in theory, I have been having trouble with type and syntax errors. It also quickly becomes verbose and difficult to maintain as the list of fields grows.
var text initialList = ToText([Related Modification - Operator Access]);
var text withProject =
If(not Contains(ToText(initialList), ToText([Related Project - Operator Access])),
List("; ", ToText(initialList), ToText([Related Project - Operator Access])),
ToText(initialList));
... and so on for each field- Using List and Split Functions with Unique() Equivalent
I attempted to split the concatenated list and simulate unique functionality by adding fields conditionally. Unfortunately, Quickbase lacks a direct Unique() function for removing duplicates from lists, so this approach didn’t work as expected.
Help Requested
Has anyone found an efficient way to concatenate and de-duplicate multi-select text fields in Quickbase? Ideally, I’d like a solution that’s concise, scalable, and doesn’t require manually adding each field with repeated If and Contains checks. Are there workarounds, or perhaps Quickbase functionality I’m overlooking, that could streamline this task?
I would much prefer to keep the processing in the table as it pulls from other tables where the data can change. So it needs to be quite responsive and I don't want to have to declare an excessive amount of triggers.
Any insights, examples, or alternative approaches would be greatly appreciated!
