Sorting Custom Fields ... Next button -> Syntax Error

[danimal94x]

I have created 3 custom fields in the accounts module ...

I edited the custom/modules/MODULENAME/metadata/listviewdefs.php
( as mentioned in http://www.sugarcrm.com/forums/showthread.php?t=32850 )
to include 'sortable' => true, for each of my custom fields.

The sort works in the first lookup, but when I press the Next or Last Page button, I get a syntex error
SQL Error : Incorrect syntax near ','.

I would appreciate any help .. Thanks
Dan

[andopes]

Hi Dan

Which version are you using?
is there any message at end of sugarcrm.log related to that issue?
There is a fix related to that in the SugarCRM 5.1.0a released yestarday.

Cheers

[danimal94x]

We are on Version 5.1.0 (Build 4732)

Sugarcrm.log

10/03/08 15:51:08 [8088][9681547a-b9ce-1133-bf79-48bd69b3e999][FATAL] Incorrect syntax near ','.: SELECT TOP 26 * FROM
(
SELECT ROW_NUMBER()
OVER (ORDER BY ,accounts_cstm.td_company_c) AS row_number,
accounts.id ,accounts_cstm.td_company_c,accounts_cstm.td_custo mer_c,accounts_cstm.td_first_invoiced_c,accounts_c stm.td_last_invoiced_c,accounts_cstm.td_sales_c, accounts.name , accounts.phone_office , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod, accounts.assigned_user_id FROM accounts LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c LEFT JOIN users jt0 ON
jt0.id= accounts.assigned_user_id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN users jt1 ON
jt1.id= accounts.created_by AND jt1.deleted=0
AND jt1.deleted=0 where accounts.deleted=0
) AS a
WHERE row_number > 25

10/03/08 15:51:08 [8088][9681547a-b9ce-1133-bf79-48bd69b3e999][FATAL] SQL Server error: Incorrect syntax near ','.

10/03/08 15:51:08 [8088][9681547a-b9ce-1133-bf79-48bd69b3e999][FATAL] SQL Server error: Incorrect syntax near ','.




And Thanks for the fast response ..

[andopes]

Hi Dan

There is a missed field here: "OVER (ORDER BY ,accounts_cstm.td_company_c)" just after "ORDER BY".

I don't know what is causing it but let me know the content of the listviewdefs.php been loaded.
Does it happen with any user or only with your user?
If it happen only with some users then reset the user preferences for these one.

Cheers

[danimal94x]

This happens for all users ... I attached the sugarcrm.log and listviewdefs.php file
.

Thanks again for your help

[danimal94x]

Also, I have created 3 custom fields in the Contacts Module and this too produces the same error

Attached log with error in accounts and contacts

[andopes]

Hi Dan

I analyzed the files you attached and I could not identify any bug you had introduced.
Honestly I don´t know what is breaking the query, but lets try another test.

Create a new clean installation, exactly the same version you are using, the same database type (MSSQL?) and the same ldap credentials. Populate the database with demo data.

Go to ListView of Accounts before any customization and make sure it works great.
Create a single custom field in Accounts, exactly the same you created in the current installation and populate it with some values in some records.
Try again the ListView.
Set it to sortable and try again the ListView.

Check if you are able to reproduce the issue.
If yes then you found a bug and you can file another bug into bugs.sugarcrm.com

Cheers

[danimal94x]

Thanks for the response .. I will try it and report back in 2 days

[danimal94x]

André,

I tried a fresh install and a new database in MSSQL ..

Created an integer custom field and a textfield custom field ...

Both return a "SQL Error : Incorrect syntax near ','." when sorting the custom field and pressing next

I guess I will submit it as a bug, unless you have any other suggestions.


BTW - sorry about the response lag, wife had a baby last week

[zurti]

As far as i know there is no way to make a custom field sortable... am I wrong??