4.5.1 Suddenly documents arent appearing but they ARE there...

[jasonsot]

I was updating the documents section when i went to return to the Document List but suddenly no documents were listed. I cleared the search filters and they still did not appear. I checked the database and they are all there and not deleted. I clicked on the Select button in the search form and they also appear in the window that pops up but not in the List View.

Any ideas what could be causing this?

[andopes]

Hi Jason

Probably it is due a query error in MySQL.
Edit the file include/ListView/ListViewData.php and add this line:

 echo "<LI>$main_query"; 
  

Just after the line:

 $main_query = $ret_array['select'] . $params['custom_select'] . $ret_array['from'] . $params['custom_from'] . $ret_array['where'] . $params['custom_where'] . $ret_array['order_by'] . $params['custom_order_by']; 
  

Let me know the query in the ListView of Documents.

Cheers

[jasonsot]

Hello André,

Thank you for the response. I added that line and am getting this response:

SELECT documents.id , documents_cstm.*, documents.document_revision_id , documents.document_name , documents.category_id , documents.subcategory_id , jt0.user_name created_by_name , jt0.created_by created_by_name_owner , 'Users' created_by_name_mod, documents.active_date , documents.exp_date , documents.created_by FROM documents LEFT JOIN documents_cstm ON documents.id = documents_cstm.id_c LEFT JOIN users jt0 ON jt0.id= documents.created_by AND jt0.deleted=0 AND jt0.deleted=0 where documents.deleted=0 ORDER BY last_rev_create_date ASC

[jasonsot]

any ideas?

[andopes]

Hi Jason

Run this query into phpMyAdmin logged in the database used by SugarCRM.
You should see an error.
I suppose the table documents_cstm does not exist.

Regards

[jasonsot]

Thanks André,

I checked and the documents_cstm table is there. When I run that query in phpMyAdmin, I get this response:

#1054 - Unknown column 'last_rev_create_date' in 'order clause'

When i remove the order clause, it returns accurate results.

[jasonsot]

I returned to the documents section and it was still blank. Since the sorting seemed to be causing the error in phpMyAdmin, I resorted the blank list by Document Name and they all appeared.

Thanks for your assistance, André... wouldnt have been able to figure it out without ya!