Sort is not working for email field in contact module list view

[jyotip]

Hello,

Can anyone help how we can make sort working for the email field?

Thanks
Jyoti

[burndata]

Hello!!!

I think I found the solution for this little issue. Try it and let me know.

Edit:
/modules/Contacts/metadata/listviewdefs.php

and change these values.

Line 95 & Line 125
‘sortable’ => false,
into
‘sortable’ => true,

whoo hoo!

time passed… tested… hmm. Hold off on trying this… maybe it will not work. It was working… hmm

Yeah no go here… Just seen this awful message in the suitecrm.log

Tue Jul 14 19:12:03 2020 [21321][1][FATAL] Mysqli_query failed.
Tue Jul 14 19:12:03 2020 [21321][1][FATAL]  Query Failed:  SELECT  contacts.id , contacts.assigned_user_id , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) as name, contacts.first_name , contacts.last_name , contacts.salutation  , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, contacts.date_entered , contacts.created_by  FROM contacts  LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c   LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0

 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0  LEFT JOIN  users jt1 ON contacts.assigned_user_id=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0 where contacts.deleted=0 ORDER BY email1 LIMIT 0,21: MySQL error 1054: Unknown column 'email1' in 'order clause'

yup. nightmare. If you do the above. and then actually Click on the “Email” Header to do a “sort”

LOOK OUT!!

by doing that… you then tell the system that you want to sort… it will sort once… and then BANG. things go really bad. There will ne NO way to recover out of this to get your contacts back visible because the system wants to sort by the email1 field… This sorting causes a “ORDER BY email1” query string that causes the above error log to bark out the FATAL messages and never actually call into the contact database to populate the list. hmm

I think a join isn’t happening that should… as the sql statement is saying

“MySQL error 1054: Unknown column”

so thus the email1 is not getting located in the initiated SELECT… hmm…

I will see if I can dig more into this… not sure if I can climb this mountain.

**** DING DING ****

Lets say you ended up doing the above… without reading my LOOK OUT. then well… here is the answer. omg. it wasn’t easy finding this but I did. well it was a little easy… little not.

you will need to go into the /data/sugar/SugarBean.php and scroll down to line 4094 and add a
`

return “”;

Then run your suiteCRM and then load up your previously empty contacts list and click on ANY of the other columns except “email” … and then go back to the

/data/sugar/SugarBean.php

and delete your previoulsly placed

return “”;

then BINGO. All your contacts will no appear again. Whewww… nightmare. I was worried for a bit.

wow. like what was thinking… " yeah fix the email sorting issue"

ahahah i will let time pass and see what happens…

after a few minutes I will see if I can continue tackling this… I think I have a idea on how it might be possible.
`

[pgr]

Emails are stored in a separate table (it’s a one-to-many relationship, there can be many emails per record). The email1 is a hack to facilitate grabbing the primary email.

How are Email addresses stored?

Remember that changing things in core code is not upgrade-safe. You should always keep your customizations strictly to the custom folder.

[jyotip]

Hello @burndata,

Thanks for your reply :).

Yes before posting here i tried this solution but it didnt work. even if you change false to true it will not work.

If i change ‘sortable’ => false, to true and then click on sort button, in result nothing has come, so to get all record back I filtered and click on clear button and then click on search button, by doing that i got all records back.

So still finding a solution.

[jyotip]

Hello @pgr,

Thanks for reply :).

Can you guide me how we can make sort option which works for email field?

[pgr]

Sorry, no. It is not simple and I don’t know how to do it.

[burndata]

I wish I could find this “Clear” button. hahah I was forced in a email1 sorting nightmare.

with a little work I did end up getting the emails to show up and the query not to error out anymore…
but like everything that is good… It is hard. Very very tricky stuff here.

like @pgr has noted… it is a one-to-many relationship. just take a look in the /data/Link.php to really get a mind-altering wtf.

I believe it is WAY above my pay grade at this time to be doing this. I have told myself, “Do I really really need to sort the email addresses that I will have in there?”

"I say no. " haha . on to other tasks that maybe I can solve.