Error on screen when saving Opportunity record with email field

Question

Hey All!

I have attempted to add an email field to the Opportunities modules following this Cookbook Article in the Developer Guide, and I am getting a "HTTP: 500 Internal Server Error" loaded to the screen. The error seems to complain that there is not an "email_address" field in the Opportunity table, so it would seem the query is being built incorrectly as it shouldn't be looking for an email address in the Opportunity table. That being said, the record seems to save fine, and the email addresses will actually save just fine to the database. I've taken a look at the db and confirmed they are saved in the email_addr_bean_rel table with the bean module set to Opportunities. Also, on page refresh, the record view loads just fine and it displays the email addresses without issue. If I try to edit and save the record again though, the same error appears though.

Here is the full output of the error I receive on record view when saving the record:

HTTP: 500 Internal Server Error

An exception occurred while executing 'SELECT jt0_email_addresses_primary.email_address email FROM opportunities LEFT JOIN email_addr_bean_rel opportunities_email_addresses_primary ON (opportunities_email_addresses_primary.deleted = ?) AND (opportunities_email_addresses_primary.bean_module = ?) AND (opportunities_email_addresses_primary.primary_address = ?) LEFT JOIN opportunities jt0_email_addresses_primary ON (jt0_email_addresses_primary.id = opportunities_email_addresses_primary.bean_id) AND (jt0_email_addresses_primary.deleted = ?) WHERE (opportunities.id = ?) AND (opportunities.deleted = ?) LIMIT 1' with params [0, "Opportunity", 1, "0", "ee9b7198-8177-11e9-b673-0800270fcb6c", 0]: Unknown column 'jt0_email_addresses_primary.email_address' in 'field list'

I do also receive a seemingly similar error on list view any time it is loaded, which means I cannot load any records on the list view at all.

HTTP: 500 Internal Server Error

An exception occurred while executing 'SELECT opportunities.sales_stage, opportunities.commit_stage, opportunities.base_rate, opportunities.currency_id, opportunities.amount, opportunities.best_case, opportunities.worst_case, opportunities.date_closed, opportunities.probability, case when jt1_following_link.id IS NOT NULL then 1 else 0 end following, case when jt8_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite, opportunities.name, opportunities.total_revenue_line_items, opportunities.closed_revenue_line_items, opportunities.included_revenue_line_items, jt2_accounts.id account_id, jt2_accounts.assigned_user_id account_id_owner, jt2_accounts.name account_name, jt2_accounts.assigned_user_id account_name_owner, opportunities.opportunity_type, opportunities.lead_source, opportunities.next_step, jt3_created_by_link.first_name rel_created_by_name_first_name, jt3_created_by_link.last_name rel_created_by_name_last_name, jt3_created_by_link.created_by created_by_name_owner, jt4_assigned_user_link.first_name rel_assigned_user_name_first_name, jt4_assigned_user_link.last_name rel_assigned_user_name_last_name, jt4_assigned_user_link.created_by assigned_user_name_owner, jt5_modified_user_link.first_name rel_modified_by_name_first_name, jt5_modified_user_link.last_name rel_modified_by_name_last_name, jt5_modified_user_link.created_by modified_by_name_owner, opportunities.date_entered, jt6_email_addresses_primary.email_address email, opportunities.acl_team_set_id, opportunities.team_set_id, LTRIM(RTRIM(CONCAT(IFNULL(jt7_team_link.name,''),' ',IFNULL(jt7_team_link.name_2,'')))) team_name, jt7_team_link.created_by team_name_owner, opportunities.created_by, opportunities.assigned_user_id, opportunities.modified_user_id, opportunities.team_id, opportunities.id, opportunities.date_modified FROM opportunities LEFT JOIN sugarfavorites sf_opportunities ON (sf_opportunities.module = ?) AND (sf_opportunities.record_id = opportunities.id) AND (sf_opportunities.assigned_user_id = ?) AND (sf_opportunities.deleted = ?) LEFT JOIN subscriptions opportunities_following ON (opportunities.id = opportunities_following.parent_id) AND (opportunities_following.deleted = ?) AND (opportunities_following.parent_type = ?) AND (opportunities_following.created_by = ?) LEFT JOIN users jt1_following_link ON (jt1_following_link.id = opportunities_following.created_by) AND (jt1_following_link.deleted = ?) LEFT JOIN accounts_opportunities accounts_opportunities ON (opportunities.id = accounts_opportunities.opportunity_id) AND (accounts_opportunities.deleted = ?) LEFT JOIN accounts jt2_accounts ON (jt2_accounts.id = accounts_opportunities.account_id) AND (jt2_accounts.deleted = ?) LEFT JOIN users jt3_created_by_link ON (opportunities.created_by = jt3_created_by_link.id) AND (jt3_created_by_link.deleted = ?) LEFT JOIN users jt4_assigned_user_link ON (opportunities.assigned_user_id = jt4_assigned_user_link.id) AND (jt4_assigned_user_link.deleted = ?) LEFT JOIN users jt5_modified_user_link ON (opportunities.modified_user_id = jt5_modified_user_link.id) AND (jt5_modified_user_link.deleted = ?) LEFT JOIN email_addr_bean_rel opportunities_email_addresses_primary ON (opportunities_email_addresses_primary.deleted = ?) AND (opportunities_email_addresses_primary.bean_module = ?) AND (opportunities_email_addresses_primary.primary_address = ?) LEFT JOIN opportunities jt6_email_addresses_primary ON (jt6_email_addresses_primary.id = opportunities_email_addresses_primary.bean_id) AND (jt6_email_addresses_primary.deleted = ?) LEFT JOIN teams jt7_team_link ON (opportunities.team_id = jt7_team_link.id) AND (jt7_team_link.deleted = ?) LEFT JOIN sugarfavorites opportunities_favorite ON (opportunities.id = opportunities_favorite.record_id) AND (opportunities_favorite.deleted = ?) AND (opportunities_favorite.module = ?) AND (opportunities_favorite.created_by = ?) LEFT JOIN users jt8_favorite_link ON (jt8_favorite_link.id = opportunities_favorite.modified_user_id) AND (jt8_favorite_link.deleted = ?) WHERE (opportunities.id IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND (opportunities.deleted = ?) ORDER BY opportunities.date_modified DESC, opportunities.id DESC' with params ["Opportunities", "1", 0, "0", "Opportunities", "1", 0, 0, 0, 0, 0, 0, 0, "Opportunity", 1, "0", 0, "0", "Opportunities", "1", 0, "ee9b7198-8177-11e9-b673-0800270fcb6c", "eec63a4a-8177-11e9-98ec-0800270fcb6c", "eec8e81c-8177-11e9-b2ac-0800270fcb6c", "eebb53be-8177-11e9-97c1-0800270fcb6c", "eeb83ff8-8177-11e9-974a-0800270fcb6c", "eeb0a608-8177-11e9-b2b4-0800270fcb6c", "eeac5fbc-8177-11e9-a3df-0800270fcb6c", "eea796da-8177-11e9-9ced-0800270fcb6c", "eea410dc-8177-11e9-a538-0800270fcb6c", "ee98f1a2-8177-11e9-9406-0800270fcb6c", "ee954e80-8177-11e9-98e8-0800270fcb6c", "ee94118c-8177-11e9-b084-0800270fcb6c", "ee930666-8177-11e9-b425-0800270fcb6c", "ee91c652-8177-11e9-a253-0800270fcb6c", "ee8f2500-8177-11e9-b92c-0800270fcb6c", "ee8dd402-8177-11e9-979c-0800270fcb6c", "ee7fca56-8177-11e9-a406-0800270fcb6c", "ee7d8c3c-8177-11e9-b149-0800270fcb6c", "ee7af94a-8177-11e9-b96b-0800270fcb6c", "ee76fa20-8177-11e9-b1d8-0800270fcb6c", "ee690ab4-8177-11e9-9b57-0800270fcb6c", 0]: Unknown column 'jt6_email_addresses_primary.email_address' in 'field list'

Any help would be much appreciated!

Thank you!

Chris

Christopher Heyman · Accepted Answer

Hi Christopher Heyman , Please follow the below thread to create an email field using VardefManager. Email Addresses on Custom Module, add programmatically We could make use of ready-made vardefs templates available inside include/SugarObjects/implements. We need to add this entry under 'uses' array in module vardefs. For eg: $dictionary['']['uses'] = array('email_address'); //create email fields VardefManager::createVardef("", "", array('email_address')); For reference, you can check out Accounts or Contacts module vardefs in cache/modules/Accounts/Accountvardefs.php Let us know if this helps. Regards.