Issue when ordering by...

[trivoc]

Hello,

I've installed a module called JCRMInvoices and it works fine, althought when i cilc on the column name in order to order by something the invoices list i get this error:

Error retrieving JCRMInvoice list: Query Failed:SELECT users.user_name assigned_user_name, accounts.name account_name, concat(contacts.first_name, ' ', contacts.last_name) contact_name,jcrminvoices.* FROM jcrminvoices LEFT JOIN users ON jcrminvoices.assigned_user_id=users.id LEFT JOIN accounts ON jcrminvoices.account_id=accounts.id LEFT JOIN contacts ON jcrminvoices.contact_id=contacts.id WHERE jcrminvoices.deleted=0 ORDER BY JCRMInvoices.invoice_number asc LIMIT 0,20::MySQL error 1109: Unknown table 'JCRMInvoices' in order clause

I have looked for an answer in differents forums, google, etc. and i've understood that the error means that the JCRMInvoices doesn't exist or something like this. But i have take a look at the databse in phpmyadmin and the table does exists and it's well writen (with an "s" at the end of the word).
I have also repaired the database, the extensions, the relationships, and the indexes, but the error still appear and i don't know what as could i do to solve this issue.

May be someone had the same problem and solved it? i will appreciate any help please.

Regards.

[ruchida]

Is "JCRMI" upper case or lower case? What is the exact table name?

[trivoc]

Hi ruchida,

the exact name of the table is jcrminvoices, lower case... may this be the problem? if so, do i have to change the name of the table to JCRMInvoices? and this change would not affect to other relationships?

Thank you veeeery much

Regards.

[andopes]

Hi, trivoc.

The table name in Order By must be lower case, equals to table name in select.

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[ruchida]

Hi ruchida,
if so, do i have to change the name of the table to JCRMInvoices?

Hi,

No, I don't think so.

In the first part of the query, it correctly points to the table name like ".... FROM jcrminvoices ....", but in the latter part, the table name becomes "JCRMInvoices".

I think "JCRMInvoices" is a module name, not a table name but it is set in this query by some codes in the JCRMInvoices module.

Where can you find the string "JCRMInvoices" in your custom module?

[trivoc]

Hi,

I can find the string "JCRMInvoices" in all the php files of the JCRMInvoices module, so i don't know exactly in what file should i have to replace it by "jcrminvoices" (if this were the solution). However i have tried this change in some files (index.php, JCRMInvoice.php, vardefs.php, ...) but new errors have appeared so i have let the module as it was.

On the other hand, i've noticed that the "order_by" function works ok if i order the columns wich are text (account name, contact, assigned to, status...). It only gives the error if i try to order by the columns wich are numbers (i.e.: invoice number, invoice date, internal reference, amount).

I have to say that the module was only in english language, and i have translated it to spanish language. But the error is displayed in both languages when i try to "order_by" the invoices list.

My SugarCRM is Version 4.5.1e (Build 1049).

Best regards.

[andopes]

Hi, trivoc.

Some scripts you could look for the wrong word is:

1. Bean of module: look at the functions: get_list_view_data, create_list_query;
2. mdoule/YourModule/metadata/listview_defs.php

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[trivoc]

Hi andopes,

I've found the problem: in /modules/JCRMInvoices/listview.html i have this code:

<td scope="col" width="8%" class="listViewThS1"><slot><a href="{ORDER_BY}JCRMInvoices.invoice_number" class="listViewThLinkS1">{MOD.LBL_LIST_INVOICE_NUM BER}{arrow_start}{JCRMInvoices_invoice_number_arro w}{arrow_end}</a></slot></td>

--- and the same lines but referred to invoice date, amount, etc.

I just have changed JCRMInvoices (in red) by jcrminvoices and now i can "order by" correctly.

Thank you very very very much for your help, to you and to ruchida, sincerely.

Cheers.

[andopes]

Great job, trivoc.

Wonderful search.

Congratulations!

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br