Forums - Thread Details
  • State Not Answered
  • Replies 9 replies
  • Subscribers 322 subscribers
  • Views 1455 views
  • Users 0 members are here
Forum Actions
Related

issue adding email field to cases module

Mario Ramos

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

  • 0

    Looks like the field was not created in the cases database table.

    Try to run a quick repair and rebuild and make sure that the proposed SQL command will be executed correctly. Sometime you have to delete the comments before the ALTER TABLE statement.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • 0

    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.

  • 0 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',
);
     
    Regards.
  • 0 in reply to Harald Kuske

    Hi Harald,

    thanks for answer

    I already did that, but it's not working

    Regards.

  • 0 in reply to Mario Ramos

    So you created the email fields as non-db in your vardef file but the code supposes them to be db fields.

    Did you create and install the custom Bean as written in the blog post?

    Did you notice that the object name of Cases is aCase ? That means that \custom\modules\Cases\CustomCase.php starts with

    <?php

class CustomCase extends aCase
{
    /**
     * Constructor
     */
    public function __construct()
    {
        parent::__construct();
        $this->emailAddress = BeanFactory::newBean('EmailAddresses');
    }

    custom\Extension\application\Ext\Include\customCases.php must contain (not aCase but Case in $objectList):

    <?php

$objectList['Cases'] = 'Case';
$beanList['Cases'] = 'CustomCase';
$beanFiles['CustomCase'] = 'custom/modules/Cases/CustomCase.php';

    Case is a keyword within php, so it cannot be the name of class.

    I tested it locally and it worked fine:

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • 0 in reply to Harald Kuske

    Yeah I noticed that, actually it did't let me upload the package if a put it like Case, so I checked the Case.php file and I found out that, but even this way, it doesn't working

    I'm gonna try into other instance and look if its still not working

  • 0 in reply to Mario Ramos

    Hi Mario, the def looks good - but something else might be wrong. Which version of Sugar are you using? If you're creating a package, can you share it so that we can check? Similar to Harald, I've tried the exact same steps as mentioned in the doc and it worked fine for me.

  • 0

    I put the files of the post, modified for Cases, into an installation package and installed it in a vanilla demo instance.

    The result seems to work.

    The installable:

    Cases_Email.zip

    Perhaps you should check your custom/Extension/application/Ext/Include/ and custom/Extension/modules/Cases/Ext/Vardefs/ directories for any remaining bodies of former tries. Each of the files in the extension directories will be included, so old or unwanted files like editor backups are very often an issue.

    Harald Kuske
    Principal Solution Architect – Professional Services, EMEA
    hkuske@sugarcrm.com
    SugarCRM Deutschland GmbH

  • 0

    In custom/Extension/modules/Cases/Ext/Vardefs/custom_email_field.php use this code

    $module = 'Case';
    $table_name = 'Cases'; // instead of 'cases'

    Thanks,