TeamDesk Knowledge Base & Support

Kirill Bondar  Staff  1/10/2012 1:16:49 PM

Here we are tied to underlying database (namely MS SQL Server R2) features. So far they do not support ordering for custom aggregate functions such as Concatenate. Once they do this will open us the way to implement a lot of statistical calculations as well.

Philipp Matuschka (MMB) 1/11/2012 4:06:08 AM

Have they announced any plans to do so?

Kirill Bondar  Staff  1/11/2012 6:41:58 AM

The functionality is split between two products: the function is coded using .NET and they have documented an attribute to specify whether aggregate function is invariant to order of the data or not, but documentation states it is "reserved for future use". Yet, the attribute by itself does nothing - it is merely a flag for SQL to know whether the data it feeds to a function needs an ordering or not - but SQL documentation says nothing about the attribute.

Upcoming SQL 2012 release notes mention some improvements they've made for aggregate functions but its not clear whether they are applicable for custom aggregates. Yet the product is in early stage and documentation is obviously is not complete.

Rafael Muñiz 10/22/2015 11:11:55 AM

When you use "Concatenate" in a Summary, you may need to obtain the result sorted alphabetically...

It will be great if you can add this "sort" option.

Thanks
Rafael

Slava Shinderov  Staff  10/22/2015 11:19:07 AM

Merged with:
915 - Concatenate Summary, with the ability to sort.

Scott Miller 3/18/2016 9:43:32 AM

@Kirill Is there any update on this as I now find myself in need of this functionality.

cooper collier  2/14/2018 8:11:10 AM

Please recheck on this as its very important.

basenine 2/14/2018 6:53:50 PM

Grouping by filter would be nice too

Anton Avguchenko 2/17/2018 4:07:55 PM

Slava, Kirill - can you suggest a method or a work-around to implement a sorted concatenated summary? Thanks,
Anton

cooper collier  2/17/2018 4:20:24 PM

I have a very time-consuming solution I just performed for a client.
There is a very big limitation that you can only get as many records and you want to program in.
In this case, I did 5 total

Step one create index summaries, sort by created or modified date. whichever is appropriate.
Pick index 1,2,3 etc.

see this screen shot
https://goo.gl/UcyR7e

Now you combine the index in the order you want them in a new formula column
In my case, I used a xhtml column.

https://goo.gl/Cgbb2X

The last part, if the number of records is greater than 5, I display a warning that the rest of the records are in the sub view.

I hate to say this, but it's really the only solution for now.

Cooper
waterswebworks

Anton Avguchenko 2/17/2018 4:21:49 PM

Thank you Cooper!

Scott Miller 8/25/2021 10:54:26 AM

Fell over this one again today. Would be a nice addition!

Luison Lassala 9/8/2021 12:05:24 PM

It would be very useful for sorting (detail) records like CHILDREN of a FAMILY (main record) in order of (calculated column) AGE.

Pierre 7/5/2023 10:06:51 AM

same need here

Luison Lassala 12/7/2024 4:11:52 AM

Will this ever become possible??

Jorge Solá 12/9/2024 5:39:30 PM

Here is a workaround that you can try.

Create a Call URL action in the master table. Select GET as method. In the URL box, type something like this :

https://www.teamdesk.net/secure/api/v2/AppId/Token/DetailsSingularTableName/select.json?column=ValueColumn&sort=ColumnToSortBy

Replace AppId, Token, DetailsSingularTableName, ValueColumn and ColumnToSortBy with your particular values.

If the details table name or column names contain spaces, replace them with %20

You may also add a filter at the end of the URL, in case you want to restrict the concatenate to a subset of records. For instance:

&filter=[Status]="Pending"

In the Assignments section of the call URL, in the From box, type:

List("\n",
Response("$[0]['ValueColumn']"),
Response("$[1]['ValueColumn']"),
Response("$[2]['ValueColumn']"),
Response("$[3]['ValueColumn']"),
Response("$[4]['ValueColumn']"),
Response("$[5]['ValueColumn']"),
Response("$[6]['ValueColumn']"),
Response("$[7]['ValueColumn']"),
Response("$[8]['ValueColumn']"),
Response("$[9]['ValueColumn']")
)

And in the To box, select the name of the column where you want to write the concatenate,

This example concatenates the first 10 records. You can very easily expand it to concatenate more than 10 records. It doesn't matter if you include in the List formula more Responses than the number of records retrieved.

To keep the concatenate always updated in the master table:
- Set up a trigger in the details table so that, if ValueColumn or ColumnToSortBy are modified, Now() is written in column [Now] in the master table. Set Execute triggers to Yes.
- Set up a trigger in the master table so that, if column Now is modified, the call URL above is executed.

Luison Lassala 12/10/2024 4:31:39 PM

Muchas gracias, Jorge!!
I have two questions - for now :-)
- do I need the Token in the Call URL, and if so, where do I get it from?
- I presume the 'ValueColumn' is what is being concatenated. In my use case, it is already a Summary Concatenate column from a different table (records from a details table of the details table in this use case).
When I tried to create a Trigger for the details table, the value column (the one I want to concatenate) was not listed in the Update Record trigger dropdown of columns. Maybe I could use any other column that gets updated or none: execute every time the details record is updated?

Jorge Solá 12/11/2024 3:52:33 AM

Luison, yes, you need an API token when making the call. You can read about it here:
https://www.teamdesk.net/help/rest-api/authorization/

What I called ValueColumn is what is being concatenated.

You can't create a trigger based on a summary column. Triggering the action every time a details record is modified won't do the trick, because changes to the subdetails table, where you get your values from, will not trigger the action. There are different approaches that you can use in this case. One of them is to add one more trigger before the other two:

- Set up a trigger in the subdetails table so that, if any of the columns that contribute to the ValueColumn is modified, Now() is written in column [Now] in the details table. Set Execute triggers to Yes.
- Set up a trigger in the details table so that, when column Now is modified, Now() is written in column [Now] in the master table. Set Execute triggers to Yes.
- Set up a trigger in the master table so that, if column Now is modified, the call URL above is executed.

Luison Lassala 12/11/2024 6:04:43 AM

Thanks again @Jorge Solá
I made all the necessary adjustments. Now I'm getting the error:
"The remote server returned an error: (403) Forbidden"
presumably because the Call URL does not recognise the Token.
I generated one in Integrations API > REST API; Impersonate as "My User" (i.e. "Luison Lassala")
and pasted it in the Call URL's:
"https://www.teamdesk.net/secure/api/v2/95904-1/14D0E65AA46F490993DC2DE01F885E46/WEEKLY%20PARTICIPANT/select.json?column=Participant_Full_Details_for_Bus&sort=Accomm_Details"
Could it be because I am testing it in a Development Branch??

Pierre 12/11/2024 7:42:23 AM

Hello Luison
I do not know about your error 403
But
1) Write your API Key in the header, not in the https line:

Authorization: Bearer 14D0E65AA46F490993DC2DE01F885E46

Your call will look like this:
https://www.teamdesk.net/secure/api/v2/7YYYY/t_753968/select.json?column=%

2) Instead of typing the table name in the https line (WEEKLY%20PARTICIPANT), use its identification number in the format t_XXXXX
where XXXXX is the table number
you will find your table number by looking at any page displaying a record of this table ; the https line will include the parameter table=XXXXX
If you ever change the table name, the formula will remain valid

3) Instead of using 95904-1, use AppId()

Pierre