MySQL error 1054: Unknown column 'jt3.name' in 'field list'

[jibinu]

Hi All, Please help me..

In sugarcrm 6.1.0 , I have create a custom modules and create some custom fields and logic-hooks for these modules.

The issue that, when I try to export record in list view of custom module that shown the error :

MySQL error 1054: Unknown column 'jt3.name' in 'field list'

Please help me to solve this problem.

Thanks

[AlexAv]

Maybe Repair will help.
Admin->Repair->Quick Repair and Rebuild

[jibinu]

Hi.......Thanks for your response..........

But its not a solution for this problem..
I have already done its earlier..

I expect more suggestions......................thanks

[ashdwi1]

Hi,
Please add name field in your custom module.
thanks

[jibinu]

Hi............. Thanks for your response.........

one of my custom module already have the name field,
In the other module now I create name filed and perform Quick Repair and Rebuild.

But again the same error in both of the custom modules.

[jibinu]

I expect more suggestions please......................

Thanks

[ashdwi1]

hi,
can you please post sql here.

[jibinu]

Hi........Thanks for taking time out on your busy schedule..........

But from where can we get the sql query of export function.......?
I didn't made any changes in export functionality of sugercrm

[jskov]

I am also getting an export error. Please help. Below is my full sql error and query.

Error exporting Opportunities: 
.SELECT opportunities.*, accounts.name as account_name, users.user_name as assigned_user_name ,opportunities_cstm.account_id_c,opportunities_cstm.agent_type_c,opportunities_cstm.billing_package_c,opportunities_cstm.custom_blog_c,opportunities_cstm.blog_url_c,opportunities_cstm.broker_notes_c,opportunities_cstm.broker_blog_c,opportunities_cstm.cancellation_date_c,opportunities_cstm.train_trainings_id_c,opportunities_cstm.reps_employees_id_c,opportunities_cstm.reps_employees_id1_c,opportunities_cstm.reps_employees_id2_c,opportunities_cstm.reps_employees_id3_c,opportunities_cstm.pay_exempt_c,opportunities_cstm.be_paid_date_c,opportunities_cstm.com_pay_date_c,opportunities_cstm.refund_c,opportunities_cstm.refund_amount_c,opportunities_cstm.charge_back_c,opportunities_cstm.charge_back_amount_c,opportunities_cstm.cancel_notes_c,opportunities_cstm.probability_c,opportunities_cstm.package_price_c,opportunities_cstm.setup_fee_c,opportunities_cstm.initial_billing_c,opportunities_cstm.billing_complete_c,opportunities_cstm.first_rebill_c,opportunities_cstm.monthly_fee_c,opportunities_cstm.billing_method_c,opportunities_cstm.check_num_c,opportunities_cstm.transaction_id_c,opportunities_cstm.welcome_email_c,opportunities_cstm.blog_created_date_c,opportunities_cstm.blog_created_email_c,opportunities_cstm.blog_creation_call_c,opportunities_cstm.review_scheduled_c,opportunities_cstm.review_complete_c,opportunities_cstm.jjwg_maps_lng_c,opportunities_cstm.jjwg_maps_lat_c,opportunities_cstm.jjwg_maps_geocode_status_c,opportunities_cstm.jjwg_maps_address_c,opportunities_cstm.service_fee_c,opportunities_cstm.reps_employees_id4_c,opportunities_cstm.expected_revenue_c,opportunities_cstm.blog_city_c,opportunities_cstm.credit_to_account_c,opportunities_cstm.blog_review_date_c,opportunities_cstm.training_date_c,opportunities_cstm.refund_needed_c,opportunities_cstm.closing_date_c,opportunities_cstm.test_date_time_c,opportunities_cstm.contact_id_c,opportunities_cstm.phone_1_c,opportunities_cstm.phone_2_c,opportunities_cstm.phone_3_c,opportunities_cstm.phone_1_type_c,opportunities_cstm.phone_2_type_c,opportunities_cstm.phone_3_type_c,opportunities_cstm.subscription_id_c, relJoin1.name office_name_c , relJoin2.name training_attended_c , CONCAT(IFNULL(relJoin3.first_name,''),' ',IFNULL(relJoin3.last_name,'')) scheduler_c , CONCAT(IFNULL(relJoin4.first_name,''),' ',IFNULL(relJoin4.last_name,'')) secondary_trainer_c , CONCAT(IFNULL(relJoin5.first_name,''),' ',IFNULL(relJoin5.last_name,'')) deal_manager_c , CONCAT(IFNULL(relJoin6.first_name,''),' ',IFNULL(relJoin6.last_name,'')) primary_trainer_c , CONCAT(IFNULL(relJoin7.first_name,''),' ',IFNULL(relJoin7.last_name,'')) agent_name_c FROM opportunities LEFT JOIN users ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id LEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c LEFT JOIN accounts relJoin1 ON opportunities_cstm. = relJoin1.id AND relJoin1.deleted=0 LEFT JOIN train_trainings relJoin2 ON opportunities_cstm.train_trainings_id_c = relJoin2.id AND relJoin2.deleted=0 LEFT JOIN reps_employees relJoin3 ON opportunities_cstm.reps_employees_id_c = relJoin3.id AND relJoin3.deleted=0 LEFT JOIN reps_employees relJoin4 ON opportunities_cstm.reps_employees_id2_c = relJoin4.id AND relJoin4.deleted=0 LEFT JOIN reps_employees relJoin5 ON opportunities_cstm.reps_employees_id3_c = relJoin5.id AND relJoin5.deleted=0 LEFT JOIN reps_employees relJoin6 ON opportunities_cstm.reps_employees_id4_c = relJoin6.id AND relJoin6.deleted=0 LEFT JOIN contacts relJoin7 ON opportunities_cstm.contact_id_c = relJoin7.id AND relJoin7.deleted=0 where (accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0) AND (accounts.deleted is null OR accounts.deleted=0) AND opportunities.deleted=0 ORDER BY opportunities.name Query Failed:SELECT opportunities.*, accounts.name as account_name, users.user_name as assigned_user_name ,opportunities_cstm.account_id_c,opportunities_cstm.agent_type_c,opportunities_cstm.billing_package_c,opportunities_cstm.custom_blog_c,opportunities_cstm.blog_url_c,opportunities_cstm.broker_notes_c,opportunities_cstm.broker_blog_c,opportunities_cstm.cancellation_date_c,opportunities_cstm.train_trainings_id_c,opportunities_cstm.reps_employees_id_c,opportunities_cstm.reps_employees_id1_c,opportunities_cstm.reps_employees_id2_c,opportunities_cstm.reps_employees_id3_c,opportunities_cstm.pay_exempt_c,opportunities_cstm.be_paid_date_c,opportunities_cstm.com_pay_date_c,opportunities_cstm.refund_c,opportunities_cstm.refund_amount_c,opportunities_cstm.charge_back_c,opportunities_cstm.charge_back_amount_c,opportunities_cstm.cancel_notes_c,opportunities_cstm.probability_c,opportunities_cstm.package_price_c,opportunities_cstm.setup_fee_c,opportunities_cstm.initial_billing_c,opportunities_cstm.billing_complete_c,opportunities_cstm.first_rebill_c,opportunities_cstm.monthly_fee_c,opportunities_cstm.billing_method_c,opportunities_cstm.check_num_c,opportunities_cstm.transaction_id_c,opportunities_cstm.welcome_email_c,opportunities_cstm.blog_created_date_c,opportunities_cstm.blog_created_email_c,opportunities_cstm.blog_creation_call_c,opportunities_cstm.review_scheduled_c,opportunities_cstm.review_complete_c,opportunities_cstm.jjwg_maps_lng_c,opportunities_cstm.jjwg_maps_lat_c,opportunities_cstm.jjwg_maps_geocode_status_c,opportunities_cstm.jjwg_maps_address_c,opportunities_cstm.service_fee_c,opportunities_cstm.reps_employees_id4_c,opportunities_cstm.expected_revenue_c,opportunities_cstm.blog_city_c,opportunities_cstm.credit_to_account_c,opportunities_cstm.blog_review_date_c,opportunities_cstm.training_date_c,opportunities_cstm.refund_needed_c,opportunities_cstm.closing_date_c,opportunities_cstm.test_date_time_c,opportunities_cstm.contact_id_c,opportunities_cstm.phone_1_c,opportunities_cstm.phone_2_c,opportunities_cstm.phone_3_c,opportunities_cstm.phone_1_type_c,opportunities_cstm.phone_2_type_c,opportunities_cstm.phone_3_type_c,opportunities_cstm.subscription_id_c, relJoin1.name office_name_c , relJoin2.name training_attended_c , CONCAT(IFNULL(relJoin3.first_name,''),' ',IFNULL(relJoin3.last_name,'')) scheduler_c , CONCAT(IFNULL(relJoin4.first_name,''),' ',IFNULL(relJoin4.last_name,'')) secondary_trainer_c , CONCAT(IFNULL(relJoin5.first_name,''),' ',IFNULL(relJoin5.last_name,'')) deal_manager_c , CONCAT(IFNULL(relJoin6.first_name,''),' ',IFNULL(relJoin6.last_name,'')) primary_trainer_c , CONCAT(IFNULL(relJoin7.first_name,''),' ',IFNULL(relJoin7.last_name,'')) agent_name_c FROM opportunities LEFT JOIN users ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities ON opportunities.id=accounts_opportunities.opportunity_id LEFT JOIN accounts ON accounts_opportunities.account_id=accounts.id LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c LEFT JOIN accounts relJoin1 ON opportunities_cstm. = relJoin1.id AND relJoin1.deleted=0 LEFT JOIN train_trainings relJoin2 ON opportunities_cstm.train_trainings_id_c = relJoin2.id AND relJoin2.deleted=0 LEFT JOIN reps_employees relJoin3 ON opportunities_cstm.reps_employees_id_c = relJoin3.id AND relJoin3.deleted=0 LEFT JOIN reps_employees relJoin4 ON opportunities_cstm.reps_employees_id2_c = relJoin4.id AND relJoin4.deleted=0 LEFT JOIN reps_employees relJoin5 ON opportunities_cstm.reps_employees_id3_c = relJoin5.id AND relJoin5.deleted=0 LEFT JOIN reps_employees relJoin6 ON opportunities_cstm.reps_employees_id4_c = relJoin6.id AND relJoin6.deleted=0 LEFT JOIN contacts relJoin7 ON opportunities_cstm.contact_id_c = relJoin7.id AND relJoin7.deleted=0 where (accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0) AND (accounts.deleted is null OR accounts.deleted=0) AND opportunities.deleted=0 ORDER BY opportunities.name::MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= relJoin1.id AND relJoin1.deleted=0 LEFT JOIN train_trainings relJoin2 ON oppo' at line 8

[carrabaz]

[QUOTE=jskov;238625]I am also getting an export error. Please help. Below is my full sql error and query.

In you SQL lost name of field 'id_c'
see that:
LEFT JOIN accounts relJoin1 ON opportunities_cstm. = relJoin1.id AND relJoin1.deleted=0
where name of field of table opportunities_cstm?

unfortunatly, now i have that problem with project module.

it occured after modifing one of fields of project module in studio.
php files in cache broken too

repair is not solution for my problem.

what is you think? what i can do for resolving?