issue adding email field to cases module

I am trying to add the email field to the cases module, I tried to follow the Adding the Emails Field to the Record View from the sugar developer guide and everything seemed to work well, the field appears but when the record is saved it throw me the following error:

HTTP: 500 Internal Server Error

An exception occurred while executing 'SELECT jt0_email_addresses_primary.email_address email FROM cases LEFT JOIN email_addr_bean_rel cases_email_addresses_primary ON (cases_email_addresses_primary.deleted = ?) AND (cases_email_addresses_primary.bean_module = ?) AND (cases_email_addresses_primary.primary_address = ?) LEFT JOIN cases jt0_email_addresses_primary ON (jt0_email_addresses_primary.id = cases_email_addresses_primary.bean_id) AND (jt0_email_addresses_primary.deleted = ?) LEFT JOIN cases_cstm cases_cstm ON cases_cstm.id_c = cases.id WHERE (cases.id = ?) AND (cases.deleted = ?) LIMIT 1' with params [0, "Case", 1, "0", "6d64d318-2e86-11eb-bfbb-02df47d122c6", 0]: Unknown column 'jt0_email_addresses_primary.email_address' in 'field list'

I don't know what can be, if anyone have an idea is welcome, thanks for helping

Parents

0 Neeraja P over 3 years ago

Hi Mario,

Based on the query that you provided, looks like the query is trying to search for "email_address" field in the cases table, which is wrong. It should be email_addresses. Something is wrong with the way you've defined vardefs. Please re-check or share it so that we can have a look.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

0 Mario Ramos over 3 years ago in reply to Neeraja P

Hi, Neeraja,

Yeah, I'd thought that but I'm not sure what could be wrong, if you find something wrong please let me know

here is my vardef definition

<?php

$module = 'Case';
$table_name = 'cases';

$dictionary[$module]['fields']['email'] = array(
    'name' => 'email',
    'type' => 'email',
    'query_type' => 'default',
    'source' => 'non-db',
    'operator' => 'subquery',
    'subquery' => 'SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE',
    'db_field' => array(
        'id',
    ),
    'vname' => 'LBL_EMAIL_ADDRESS',
    'studio' => array(
        'visible' => true,
        'searchview' => true,
        'editview' => true,
        'editField' => true,
    ),
    'duplicate_on_record_copy' => 'always',
    'len' => 100,
    'link' => 'email_addresses_primary',
    'rname' => 'email_address',
    'module' => 'EmailAddresses',
    'full_text_search' => array(
        'enabled' => true,
        'searchable' => true,
        'boost' => 1.50,
    ),
    'audited' => true,
    'pii' => true,
);

$dictionary[$module]['fields']['email1'] = array(
    'name' => 'email1',
    'vname' => 'LBL_EMAIL_ADDRESS',
    'type' => 'varchar',
    'function' => array(
        'name' => 'getEmailAddressWidget',
        'returns' => 'html',
    ),
    'source' => 'non-db',
    'link' => 'email_addresses_primary',
    'rname' => 'email_address',
    'group' => 'email1',
    'merge_filter' => 'enabled',
    'module' => 'EmailAddresses',
    'studio' => false,
    'duplicate_on_record_copy' => 'always',
    'importable' => false,
);

$dictionary[$module]['fields']['email2'] = array(
    'name' => 'email2',
    'vname' => 'LBL_OTHER_EMAIL_ADDRESS',
    'type' => 'varchar',
    'function' => array(
        'name' => 'getEmailAddressWidget',
        'returns' => 'html',
    ),
    'source' => 'non-db',
    'group' => 'email2',
    'merge_filter' => 'enabled',
    'studio' => 'false',
    'duplicate_on_record_copy' => 'always',
    'importable' => false,
    'workflow' => false,
);

$dictionary[$module]['fields']['invalid_email'] = array(
    'name' => 'invalid_email',
    'vname' => 'LBL_INVALID_EMAIL',
    'source' => 'non-db',
    'type' => 'bool',
    'link' => 'email_addresses_primary',
    'rname' => 'invalid_email',
    'massupdate' => false,
    'studio' => 'false',
    'duplicate_on_record_copy' => 'always',
);

$dictionary[$module]['fields']['email_opt_out'] = array(
    'name' => 'email_opt_out',
    'vname' => 'LBL_EMAIL_OPT_OUT',
    'source' => 'non-db',
    'type' => 'bool',
    'link' => 'email_addresses_primary',
    'rname' => 'opt_out',
    'massupdate' => false,
    'studio' => 'false',
    'duplicate_on_record_copy' => 'always',
);

$dictionary[$module]['fields']['email_addresses_primary'] = array(
    'name' => 'email_addresses_primary',
    'type' => 'link',
    'relationship' => strtolower($table_name) . '_email_addresses_primary',
    'source' => 'non-db',
    'vname' => 'LBL_EMAIL_ADDRESS_PRIMARY',
    'duplicate_merge' => 'disabled',
    'primary_only' => true,
);

$dictionary[$module]['fields']['email_addresses'] = array(
    'name' => 'email_addresses',
    'type' => 'link',
    'relationship' => strtolower($table_name) . '_email_addresses',
    'source' => 'non-db',
    'vname' => 'LBL_EMAIL_ADDRESSES',
    'reportable' => false,
    'unified_search' => true,
    'rel_fields' => array('primary_address' => array('type' => 'bool')),
);

// Used for non-primary mail import
$dictionary[$module]['fields']['email_addresses_non_primary'] = array(
    'name' => 'email_addresses_non_primary',
    'type' => 'varchar',
    'source' => 'non-db',
    'vname' => 'LBL_EMAIL_NON_PRIMARY',
    'studio' => false,
    'reportable' => false,
    'massupdate' => false,
);

$dictionary[$module]['relationships'][strtolower($table_name) . '_email_addresses'] = array(
    'lhs_module' => $table_name,
    'lhs_table' => strtolower($table_name),
    'lhs_key' => 'id',
    'rhs_module' => 'EmailAddresses',
    'rhs_table' => 'email_addresses',
    'rhs_key' => 'id',
    'relationship_type' => 'many-to-many',
    'join_table' => 'email_addr_bean_rel',
    'join_key_lhs' => 'bean_id',
    'join_key_rhs' => 'email_address_id',
    'relationship_role_column' => 'bean_module',
    'relationship_role_column_value' => $table_name,
);

$dictionary[$module]['relationships'][strtolower($table_name) . '_email_addresses_primary'] = array(
    'lhs_module' => $table_name,
    'lhs_table' => strtolower($table_name),
    'lhs_key' => 'id',
    'rhs_module' => 'EmailAddresses',
    'rhs_table' => 'email_addresses',
    'rhs_key' => 'id',
    'relationship_type' => 'many-to-many',
    'join_table' => 'email_addr_bean_rel',
    'join_key_lhs' => 'bean_id',
    'join_key_rhs' => 'email_address_id',
    'relationship_role_column' => 'bean_module',
    'relationship_role_column_value' => $module,
    'primary_flag_column' => 'primary_address',
);

This link is where I took the example: https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.2/Cookbook/Adding_the_Email_Field_to_a_Bean/

Regards.

Reply