SuiteCRM Number of Contacts field for Accounts, which will keep an accurate count of the number of Contacts related to the Account. Includes SQL to create the field and seed it once deployed.

.SuiteCRM contactCount Example.md

SuiteCRM contactCount Example

1. Run the SQL from fields_meta_data.sql against the database to create the new custom field
2. Place PHP files into project, locations are noted in comment on each file. Definitions in logic_hooks.php will need to be manually merged into the existing file or else move these definitions to better follow the Extension framework.
3. Quick Repair and Rebuild
4. Run the SQL from updateContactCountExistingRecords.sql against the database to populate the new custom field. Note that if custom fields have never been created, then the table accounts_cstm will be empty and this update will fail, you'll need to first populate the table i.e. INSERT INTO accounts_cstm (id_c) SELECT id FROM accounts

_override_sugarfield_contactcount_c.php

<?php
// custom/Extension/modules/Accounts/Ext/Vardefs/_override_sugarfield_contactcount_c.php
$dictionary['Account']['fields']['contactcount_c']['inline_edit']='';
$dictionary['Account']['fields']['contactcount_c']['labelValue']='Number of Contacts';

countContact.php

<?php
// custom/modules/Accounts/countContact.php
class countContact {
    public function decrement($bean, $event, $arguments) {
        $GLOBALS['log']->debug('countContact::decrement invoked');
        if(!empty($arguments['link']) && $arguments['link'] == 'contacts') {
            $bean->contactcount_c--;
            $bean->save();
        }
        $bean->save();
    }
    public function increment($bean, $event, $arguments) {
        $GLOBALS['log']->debug('countContact::increment invoked');
        if(!empty($arguments['link']) && $arguments['link'] == 'contacts') {
            $bean->contactcount_c++;
            $bean->save();
        }
    }
}

en_us.contactCount.php

<?php
// custom/Extension/modules/Accounts/Ext/Language/en_us.contactCount.php
$mod_strings['LBL_CONTACTCOUNT'] = 'Number of Contacts';

fields_meta_data.sql

-- to be run against the database prior to deploying the logic hooks
INSERT INTO `fields_meta_data`
(`id`,`name`,`vname`,`comments`,`help`,`custom_module`,`type`,`len`,`required`,`default_value`,`date_modified`,`deleted`,`audited`,`massupdate`,`duplicate_merge`,`reportable`,`importable`,`ext1`,`ext2`,`ext3`,`ext4`)
VALUES
('Accountscontactcount_c','contactcount_c','LBL_CONTACTCOUNT','','','Accounts','int',10,0,'','2019-08-20 02:39:56',0,0,0,0,1,'true','','','','');

logic_hooks.php

<?php
// custom/modules/Accounts/logic_hooks.php
$hook_array = Array();
$hook_array['after_relationship_add'] = Array();
$hook_array['after_relationship_add'][] = Array(
    100,
    'Increment countContacts',
    'custom/modules/Accounts/countContact.php',
    'countContact',
    'increment'
);
$hook_array['after_relationship_delete'] = Array();
$hook_array['after_relationship_delete'][] = Array(
    100,
    'Decrement countContacts',
    'custom/modules/Accounts/countContact.php',
    'countContact',
    'decrement'
);

updateContactCountExistingRecords.sql

-- to be run after all code is deployed to seed accounts_cstm.contactcount_c with accurate values
UPDATE accounts_cstm
JOIN (
    SELECT
        accounts.id as accountID,
        COUNT(contacts.id) as contactCount
    FROM accounts
    JOIN accounts_contacts
        ON accounts_contacts.account_id = accounts.id
        AND accounts_contacts.deleted = 0
    JOIN contacts
        ON contacts.id = accounts_contacts.contact_id
        AND contacts.deleted = 0
    WHERE accounts.deleted = 0
    GROUP BY accounts.id
    ORDER BY accounts.name desc
) as tempTable ON tempTable.accountID = accounts_cstm.id_c
SET accounts_cstm.contactcount_c = tempTable.contactCount;