Report module：Unknown column 'aos_quotes.assigned_user_id' in 'on clause'

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

Excuse me, how did this happen? Moreover, the A user of this report can view the report data normally, but the B user can not see the data, it is blank, and the A and B users have the authority.


LEFT JOIN doc_doc `aos_products_quotes:aos_quotes:doc_doc_aos_quotes` ON `aos_products_quotes:aos_quotes:doc_doc_aos_quotes`.id=`aos_products_quotes:aos_quotes|aos_products_quotes:aos_quotes:doc_doc_aos_quotes`.doc_doc_aos_quotesdoc_doc_ida AND `aos_products_quotes:aos_quotes:doc_doc_aos_quotes`.deleted=0
 AND ( doc_doc.assigned_user_id ='522cc13c-07eb-84f6-f83a-5dbad07ddcf9'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '522cc13c-07eb-84f6-f83a-5dbad07ddcf9'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'Doc_Doc'
                       WHERE   secr.record_id = `aos_products_quotes:aos_quotes:doc_doc_aos_quotes`.id
                               AND secg.deleted = 0) ) LEFT JOIN contacts `aos_products_quotes:aos_quotes:contacts` ON `aos_products_quotes:aos_quotes`.billing_contact_id=`aos_products_quotes:aos_quotes:contacts`.id AND `aos_products_quotes:aos_quotes:contacts`.deleted=0

 AND ( contacts.assigned_user_id ='522cc13c-07eb-84f6-f83a-5dbad07ddcf9'  or  EXISTS (SELECT  1
                  FROM    securitygroups secg
                          INNER JOIN securitygroups_users secu
                            ON secg.id = secu.securitygroup_id
                               AND secu.deleted = 0
                               AND secu.user_id = '522cc13c-07eb-84f6-f83a-5dbad07ddcf9'
                          INNER JOIN securitygroups_records secr
                            ON secg.id = secr.securitygroup_id
                               AND secr.deleted = 0
                               AND secr.module = 'Contacts'
                       WHERE   secr.record_id = `aos_products_quotes:aos_quotes:contacts`.id
                               AND secg.deleted = 0) ) LEFT JOIN `contacts_cstm` `aos_products_quotes:aos_quotes:contacts_cstm` ON `aos_products_quotes:aos_quotes:contacts`.id = `aos_products_quotes:aos_quotes:contacts_cstm`.id_c LEFT JOIN `aos_products_quotes_cstm` `aos_products_quotes_cstm` ON `aos_products_quotes`.id = `aos_products_quotes_cstm`.id_c  WHERE (  `aos_products_quotes:aos_quotes:doc_doc_aos_quotes`.date_entered BETWEEN '2019-11-01 00:00:00' AND '2019-12-01 00:00:00' AND (`aos_products_quotes:aos_quotes`.stage = 'Confirm' OR `aos_products_quotes:aos_quotes`.stage = 'Closed Accepted') AND `aos_products_quotes`.assigned_user_id = "522cc13c-07eb-84f6-f83a-5dbad07ddcf9" ) AND  aos_products_quotes.deleted = 0  ORDER BY `aos_products_quotes:aos_quotes:doc_doc_aos_quotes`.date_entered DESC LIMIT 0,20: MySQL error 1054: Unknown column 'aos_quotes.assigned_user_id' in 'on clause'
2

After Admin / Repairs / Quick Repair and Rebuild, scroll down to the bottom, if there is a button there to sync vardefs with database, press it.

3

Thank you. I have clicked on “Execute” and prompted “Database tables are synced with vardefs”, but it doesn’t seem to work.

4

Please remind my of your SuiteCRM version?

Can you be a little more specific? Which is the SQL code it proposes to run? Does it give an error? Which one?

5

SuiteCRM-7.11.8

Thank you. I found that when I set the permissions of all modules to be all visible, the report data will appear.