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.

Possible??
ID
919
Category
Setup
Author

basenine
Date Created
11/3/2015 3:04:01 AM
Date Updated
10/11/2017 2:53:26 AM
Status
New Idea
Score
120
Promoted By
Daniel Vazquez GerPatricio BustosNick Ashcroft
Philipp MatuschkaGii SystemsJorge Solá
calvin petersDale Olivermartin oliver
basenineFiles Records ManagmentNathan Phillips (CW)
Comments
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
http://www.1keydata.com/sql/sqldistinct.html

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

Presto!
Nick Ashcroft 10/11/2017 2:53:26 AM
bump !

I just posted the same idea here not realising it was already requested
https://teamdesk.crmdesk.com/answer.aspx?aid=25260

However, since it dates from 2013 I guess its not coming any time soon :-(
Feedback
 
Back to Search Results