SecuritySuite - SuiteCRM and CE Teams

Control what your users can access and save time, money, and frustrations. Lock down sensitive data in SugarCRM or SuiteCRM to specific groups or teams. Supports unlimited assigned users, unlimited group assignments to records, custom layouts for each group, login/sudo capabilities and much more.

Free Trial

By clicking you consent to share your profile with the developer

#306 - Mass Assign - error in where clause

Open Bug? created by liran 10 years ago

When I try to mass assign the entire data set, I receive the following error: "MySQL error 1054: Unknown column 'jt0.first_name' in 'where clause'" The bug is created in the file MassAssign.php, the query in $_SESSION['export_where'] has a condition for jt0 but the query's join does not have an alias for the tables and thus the errors is thrown. How can I fix this issue? Note: I'm using sugar version 6.5.15.

eggsurplus Provider Affiliate
10 years ago

Which module are you doing this on and can you list out all of the columns that are displayed on that list view? Thanks!

reply
liran
10 years ago

It's a custom module, the list view columns include the name field, relate fields and other fields (date, text, ddl), would it help if we paste here the entire $_SESSION['export_where'] ?

reply
- eggsurplus Provider Affiliate
  10 years ago
  
  Custom relationships on a custom module list view can be a nightmare to support. I'm not looking forward to this case :)
  
  There is so much involved that it would be most helpful to zip up your /modules/{CUSTOMMODULE} and /custom/modules/{CUSTOMMODULE} folder and email them over to me at [solutions] - @ - [eggsurplus] [.] [com].
  
  reply
eggsurplus Provider Affiliate
9 years ago

We attempted to debug a bit over email. Were you able to find the cause of your issue?

reply

madmat Verified Purchase

9 years ago

Has this been addressed back then? Facing the same issue with a custom module - when trying to mass remove group assignments from records.

 Query Failed:  SELECT  pro1_vereinbarung.*  ,pro1_vereinbarung_cstm.bezeichnung_c , LTRIM(RTRIM(CONCAT(IFNULL(jt0.first_name,&#039;&#039;),&#039; &#039;,IFNULL(jt0.last_name,&#039;&#039;)))) modified_by_name , jt0.created_by modified_by_name_owner  , &#039;Users&#039; modified_by_name_mod , LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,&#039;&#039;),&#039; &#039;,IFNULL(jt1.last_name,&#039;&#039;)))) created_by_name , jt1.created_by created_by_name_owner  , &#039;Users&#039; created_by_name_mod , LTRIM(RTRIM(CONCAT(IFNULL(jt2.first_name,&#039;&#039;),&#039; &#039;,IFNULL(jt2.last_name,&#039;&#039;)))) assigned_user_name , jt2.created_by assigned_user_name_owner  , &#039;Users&#039; assigned_user_name_mod , jt3.name pro1_verein_projekte_name, jtl3.pro1_verei80aerojekte_ida pro1_verei80aerojekte_ida , LTRIM(RTRIM(CONCAT(IFNULL(jt4.first_name,&#039;&#039;),&#039; &#039;,IFNULL(jt4.last_name,&#039;&#039;)))) pro1_verein_contacts_name, jtl4.pro1_vereia9eaontacts_ida pro1_vereia9eaontacts_ida , LTRIM(RTRIM(CONCAT(IFNULL(jt5.first_name,&#039;&#039;),&#039; &#039;,IFNULL(jt5.last_name,&#039;&#039;)))) modified_user_name , jt5.created_by modified_user_name_owner  , &#039;Users&#039; modified_user_name_mod FROM pro1_vereinbarung   LEFT JOIN pro1_vereinbarung_cstm ON pro1_vereinbarung.id = pro1_vereinbarung_cstm.id_c   LEFT JOIN  users jt0 ON pro1_vereinbarung.modified_user_id=jt0.id AND jt0.deleted=0

 AND jt0.deleted=0  LEFT JOIN  users jt1 ON pro1_vereinbarung.created_by=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0  LEFT JOIN  users jt2 ON pro1_vereinbarung.assigned_user_id=jt2.id AND jt2.deleted=0

 AND jt2.deleted=0  LEFT JOIN  pro1_vereingra_projekte_c jtl3 ON pro1_vereinbarung.id=jtl3.pro1_vereia867nbarung_idb AND jtl3.deleted=0

 LEFT JOIN  pro1_migra_projekte jt3 ON jt3.id=jtl3.pro1_verei80aerojekte_ida AND jt3.deleted=0
 AND jt3.deleted=0  LEFT JOIN  pro1_vereinung_contacts_c jtl4 ON pro1_vereinbarung.id=jtl4.pro1_verei839fnbarung_idb AND jtl4.deleted=0

 LEFT JOIN  contacts jt4 ON jt4.id=jtl4.pro1_vereia9eaontacts_ida AND jt4.deleted=0
 AND jt4.deleted=0  LEFT JOIN  users jt5 ON pro1_vereinbarung.modified_user_id=jt5.id AND jt5.deleted=0

 AND jt5.deleted=0 where (((jt1.name like &#039;IQ- migra%&#039;)) AND pro1_vereinbarung.deleted=0) AND pro1_vereinbarung.deleted=0: MySQL error 1054: Unknown column &#039;jt1.name&#039; in &#039;where clause&#039;

eggsurplus Provider Affiliate
9 years ago

Sugar has some really buggy code in this area. It stems from the related records being on the list view and on the search. SecuritySuite can't work correctly in these cases. I'm not sure what to suggest in this case except to try the latest version of Sugar and hope or remove the related columns from the search and search based on user first/last name instead of user name.

reply

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.

Saving Comment...

SecuritySuite - SuiteCRM and CE Teams

#306 - Mass Assign - error in where clause

"Couldn't do without it. Highly recommended and I guarantee that you will not find another alternative."

Other Add-Ons of interest

Upsert® Timesheets

Epicor-SugarCRM Integration by TCP

Sugar Integrate for Salesloft

ADD-ONS

SOLUTIONS

RESOURCES

ABOUT