Solved

Use generated columns in default sort

  • 18 October 2022
  • 4 replies
  • 50 views

Userlevel 2

hi,

I added the dynamic model for tracing data, but the extra columns cannot be used for sorting the list:

 

As tracing data is at best when sorting at the updated time, how can I use these columns for default sorting?

 

icon

Best answer by Renée Evertzen 19 October 2022, 11:52

View original

4 replies

Userlevel 4
Badge +1

Hey Tejo,

While it is true that any generated column cannot manually be included in the columns used for sorting, it is theoretically possible to modify the dynamic control procedure to include these columns in the sorting order of the table, depending on how this functionality was implemented in your project. Unfortunately this is something that I cannot determine based on your post alone. 

If this is also not an option you can considering adding a hidden expression field to the table that selects the trace value and use that for sorting instead.

Hope this helps!

Userlevel 2

Thanks Renée, I’ll take this question with me to TEC to figure out the dynamic model.

Userlevel 5
Badge +2

@Tejo van de Bor We use Dynamic code for this particular purpose in our Project too, here's a slightly adjusted script for your purpose. Add the table name(s) to which you wish to apply this in the where clause (or remove the clause if you wish to apply it to all tables). Of course you could use different sort_order and sort_no values than below.

Tables:

declare @update_date_time varchar(50) = 'update_date_time'
update c
set c.default_sort = 1
, c.sort_order = 1 -- Descending
, c.sort_no = 10
from col c
where c.tab_id in ('<table name>')
and c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and c.col_id = @update_date_time

Note that for Variants which have Variant-specific Sort enabled you need to update another table and include both tab_id and tab_variant_id

update c 
set c.default_sort = 1
, c.sort_order = 1 -- Descending
, c.sort_no = 10
from tab_variant_sort_overview c
where c.tab_id in ('<table name>')
and c.tab_variant_id in ('<variant name>')
and c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and c.col_id = @update_date_time

 

Userlevel 2

Thanks @Arie V this worked!

Reply