Why Relate fields aren't sortable in listviews?

Hello Manuel M  and Francesca Shiekh ,

Your question is why, so let's start from there.

Normally the reason why you don't want to order by related fields is because it will literally kill your database and system if you have a lot of data.

Why? Because there are tons of table joins on the SQL queries behind the scenes for every listview action you do.

It is not as simple as it sounds to order by a field on the Nth join of an auto-generated list of left joins. You add custom fields, tables and relationships, on a system that lets you flexibly decide what to show and not to show and what to search on and not on, respecting all the visibility rules and being somewhat performant!

And it is not even easy to describe! 

Being pragmatic, if you had to choose, would you want to have a system grinding to a halt for every user and people not using it and complaining, or a system working well enough without a feature, users are using it and complaining anyway?   I know I would prefer the latter...

Solution?

I have not tried this approach that I am proposing for a long time, so I am shooting a little from the hip. Could you try the denormalisation feature?

In broad strokes what that does is to copy the value into text in the original module, and then MAYBE you should be able to sort on that new field. I'd suggest to even check if there is an index on that field automatically created and if not, add an index to the field if you need to sort on it. Then the feature should keep the two values in sync as the relationship values change.

And I do assume (maybe incorrectly) that the feature request mentioned, might never be fixed, as most likely the denormalisation feature is the way to fix this problem which has been implemented a long time ago, but probably is mostly unknown to most customers.

Hope it helps

Hello Manuel M  and Francesca Shiekh ,

Your question is why, so let's start from there.

Normally the reason why you don't want to order by related fields is because it will literally kill your database and system if you have a lot of data.

Why? Because there are tons of table joins on the SQL queries behind the scenes for every listview action you do.

It is not as simple as it sounds to order by a field on the Nth join of an auto-generated list of left joins. You add custom fields, tables and relationships, on a system that lets you flexibly decide what to show and not to show and what to search on and not on, respecting all the visibility rules and being somewhat performant!

And it is not even easy to describe! 

Being pragmatic, if you had to choose, would you want to have a system grinding to a halt for every user and people not using it and complaining, or a system working well enough without a feature, users are using it and complaining anyway?   I know I would prefer the latter...

Solution?

I have not tried this approach that I am proposing for a long time, so I am shooting a little from the hip. Could you try the denormalisation feature?

In broad strokes what that does is to copy the value into text in the original module, and then MAYBE you should be able to sort on that new field. I'd suggest to even check if there is an index on that field automatically created and if not, add an index to the field if you need to sort on it. Then the feature should keep the two values in sync as the relationship values change.

And I do assume (maybe incorrectly) that the feature request mentioned, might never be fixed, as most likely the denormalisation feature is the way to fix this problem which has been implemented a long time ago, but probably is mostly unknown to most customers.

Hope it helps

Ciao Enrico,

I think if you manage indexes correctly, you'll never kill your db with a join. 

I was thinking to something like "denormalization" you mentioned but why we need to put in place a workaround to compensate a possible slow down due to a not so shining db structure?

In addition: are we completely sure that the sync between the 2 fields is maintained across all the CRM features like api calls, import from csv, mass update and so on? 

Anyway, let me thank you for your suggested workaround that probably it's the only way to achieve what we need right now. 

Grazie