TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
New value for Summary Columns - Unique Value as Text
Currently, we can have # Unique Values as a summary.

I have requirement to List (concatenate) from a series of records, unique values (as text).

i.e. Parent record with list of 10 related records...Summary Column A has 3 Unique values over the 10 records.

Currently, the summary will bring through the 10 values concatenated...I'd like it to bring through just the three unique values.


Date Created
11/3/2015 3:04:01 AM
Date Updated
6/29/2021 3:01:46 AM
New Idea
Promoted By
Ansar Ahmad戴晓Werner Lategan
Jacques du PlessisDaniel LuzScott Miller
Daniel Vazquez GerPatricio BustosJames Ross
Nick AshcroftPhilipp Matuschka (MMB)Gii Systems
Jorge Solácalvin petersDale Oliver
martin oliverbasenineFiles Records Managment
Liquid Rapid
Dale Oliver 11/8/2013 10:41:32 AM
When you create a Concatenate Summary Column, often the summary column could result in many duplicates depending on what you are trying to achieve.

I would like to display a summary of suburbs on our delivery trip-sheets. However, when using this column and there are multiple delivery addresses in the same suburb, the suburb will be repeated many times.

martin oliver 11/5/2015 10:03:13 AM
We could certainly use this
calvin peters 11/13/2015 10:17:26 AM
This is what I`ve been looking for as well. Even better if we were able to reference those unique values and select one to fill in for new records.
See Dropdown Functionality
Kirill Bondar  Staff  11/24/2015 9:00:11 AM
Merged with:
689 - Concatenate Summary Column
basenine 9/1/2016 7:46:56 PM
Not sure if there's a coding relation here...but the (new) Ask the User function is able to show a filter list of values to trim the duplicates and only show one value.

i.e. you may have 10 records with "Sputnik", 15 records with "Apollo"....the ask the user will show one of each where as a concatenated summary will show them 10 and 15 times respectively.
basenine 9/1/2016 7:47:58 PM
really need this feature right now...trying to create a doc but the implications of bringing through a listed concat kill it.
basenine 9/1/2016 8:11:12 PM

Syntax for SQL is SELECT DISTINCT to extract unique values
martin oliver 9/4/2016 8:08:20 AM
I again endorse this would be very helpful
calvin peters 9/22/2016 8:40:13 PM
Boy I could really use this feature now.

It would make a lot of my issues working around this in other ways far easier...

Philipp Matuschka (MMB) 10/21/2016 5:25:49 AM
So I've just hit this one and would really like to see it implemented. Thanks
Jorge Solá 10/21/2016 7:44:27 AM
I've implemented a workaround solution. I'm aware it only works in a particular case, but just in case it might help you, here it is:

Let's say you want to have a summary column with a concatenate of [Column A] unique values.

1) Create in the Master table a [Concatenate with repeated values] summary column of [Column A], with space-separated values.

2) In the Details table create a [Lookup Concatenate with repeated values] column that will read the values in the column you just created in the Master table.

3) In the Details table create a [Unique Column A values] column that has an initial value formula like:
If(Contains([Lookup Concatenate with repeated values], [Column A]), "", [Column A])
In other words, when a new Details record is created, this column will be blank if the value in [Column A] in the new record is a repetition of the value of [Column A] in another record.

4) Now you can create in the Master table a [Concatenate with unique values] summary column of the [Unique Column A values] column. Add a filter so that the only records added to the summary are those where [Unique Column A values] is not blank.

The crucial step is 3), because if there are going to be changes to [Column A] after the record has been created, then the initial value formula will not be effective. But if the value in [Column A] is not going to change, then it will work.
basenine 10/21/2016 5:13:44 PM
Hi Jorge

Nice work. I'll give that a go - Thank you 😊
Philipp Matuschka (MMB) 10/22/2016 3:08:09 AM
"The crucial step is 3), because if there are going to be changes to [Column A] after the record has been created, then the initial value formula will not be effective. But if the value in [Column A] is not going to change, then it will work."

You can solve this by having a formula column with the same formula as the initial value. You then have a Time Based trigger which triggers on Date Modified and checks if the value of the two columns are the same and if not transfers the value from the formula columns to Column A. That way you never be wrong for more than a few minutes

I would still prefer a proper solution
Jorge Solá 2/26/2017 12:26:56 PM
Another way to achieve this, arguably simpler & more elegant, is this:

1) In the Details table, create a many-to-many relationship to itself. Set as Match Conditions [Id]<=[Id] & [Column A]=[Column A].

2) In the same table, create a [Column B] # of records summmay column. The first record that holds in [Column A] a particular value will have a value of 1 in [Column B]; the next record that holds that same value in [Column A] will have a value of 2 in [Column B], etc.

3) In the Master table, create a [Concatenate of Column A] summary column with a [Column B]=1 Filter condition.

Nick Ashcroft 10/11/2017 2:16:55 AM
Currently the following are possible in a Summary Column

My suggestion is to make a new option which is a combination of '# of unique values' and 'Concatenate' called 'Concatenate Unique Values'.

This works the same as Concatenate but only returns each unique value once rather than repeating it for as many times as it exists in the related table.

basenine 10/11/2017 2:49:55 AM
Nick Ashcroft 10/11/2017 2:53:26 AM
bump !

I just posted the same idea here not realising it was already requested

However, since it dates from 2013 I guess its not coming any time soon :-(
Slava Shinderov  Staff  6/14/2018 4:14:26 AM
Merged with:
1136 - 'Concatenate Unique Values' as Summary Column Aggregate option
Jacques du Plessis 1/11/2019 1:26:54 AM
Bump !
Nick Ashcroft 2/3/2021 5:31:28 AM
any news on this ? sounds pretty simple to implement!
Nick Ashcroft 6/7/2021 2:25:31 AM
Dale Oliver 6/7/2021 2:53:21 AM
Yes please
Nick Ashcroft 6/29/2021 3:01:46 AM
Perhaps a quicker fix could be to introduce a 'Deduplicate' function into the TeamDesk functions list

something where you define the separator and the text field to deduplicate and only unique values are returned with the same separator between.

e.g. Deduplicate([fruits],",") where [Fruits] contains "Apples, Apples, Pears, Bananas, Bananas, Apples, Pears, Grapes"

output = "Apples, Pears, Bananas, Grapes"
Back to Search Results