Why Relate fields aren't sortable in listviews?

The code just does not support sorting by relate field.
If you manage to set it as sortable via code (I tried),  you will get a 500 Error when sorting by it and it's a bit of a headache to get out of it because the user's sort preferences are saved and need to be manually deleted. I immediately regretted trying.

I opened a case/bug for it back in 2014 and it looks like it was closed as duplicate just a couple of weeks ago, but there is no link to the other Case, so I don't know the actual status. Maybe they fixed it in the latest release?

https://portal.sugarondemand.com/index.php#supp_Bugs/58dfaecd-9633-8c68-809b-52d5f4a71600

FrancescaS

Thanks Francesca.

Let me say we have also a shiny new Sugar Sell 13 and the problem it's still present.
For Sugar: <polemical>When are we going to have this problem fixed? In Sugar Sell 20?</polemical>

Thanks

I know you are being polemic.but, working in the software industry, I have a lot of sympathy for the people who try to prioritize what issues to work on.

In the grand scheme of things, from my point of view, this is not a "bug" and that big a deal 

FrancescaS

Looks like this may be the open one:
portal.sugarondemand.com/index.php

Thanks for your help and the ticket opened. 

I hope they'll "manage" it correctly soon

Because I think the UI needs to be coherent (or let me say, consistent) without the need of explain technical details to the users. 

anyway, if other people have found any workaround, let us know.

Thanks

Bye

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

Yes, it uses logic hooks to keep things in sync, so as long as you don't change the values from the database manually, and through save, it will keep them in sync (possible code bugs aside).