Database connector for contacts view

I am trying to find the connector that populates the Contacts list view. The view currently looks like this.

What I want to do is write an API connector that add additional users to the table. I am aware of the different API versions and am able to pull data from SuiteCRM, but I am unable to write to it.

As you are aware there is quite a lot of code for SuiteCRM. I have been able to navigate to the folder Modules/Contacts but cannot see how the table is populated from here. Is what I am trying to do possible and if so where would I replace/update the database control for the Contacts list?

The architecture doesn’t have any clean-cut way of putting in a data connector there.

Normally people integrate first, so that the data is in the database, and then the view can simply show it.

But you can try just grabbing the data in time for the view display. You’ll find some articles about overriding the “display” method of the view class. But usually people just change a field or two there and invoke the parent class’s method. You would have to change the code more heavily.

@GraemeMac
If you wrote about API. You can look:
Old API:
http://domainname/service/v4_1/soap.php
New API:
http://domainname/Api/index.php/V8
If you want to change data on different forms (List, Edit, View) on the screen you should look information about files:

  • modules/<module name>/metedata
  • custom/modules/<module name>/metedata
  • modules/<module name>/views
  • custom/modules/<module name>/views
1 Like

I think there is some confusion here about the use of the SuiteCRM API. If he’s talking about getting data from another system, as I think he is, then he won’t be using the SuiteCRM API at all, he will be using the other system’s API.

@p.konetskiy

I can drill down to modules/Contacts/views/view.list.php. If I remove the line of code require_once(‘modules/Contacts/ContactsListViewSmarty.php’); the contacts list view fails to load. Can I assume that the required database string is found here?

If I open the ContactsListViewSmarty.php file there are a couple of files within this it references. If I take both of them out the Contacts pages loads fine. There are a number of functions in the class on the page reference the ListViewSmarty base class.

I can remove literally all of the code from the class on this page as well as the file that references the base class and the page still loads correctly so I am still confused as to where any reference a connection with the SQL backend lies.

@GraemeMac
May be you should up. :wink:
Look the file: include/SugarEmailAddress/SugarEmailAddress.php and the function: getPrimaryAddress . This function read email address from database.

1 Like

Yes, this is helpful. When I run the corresponding SQL query in phpMyAdmin I’m getting the same email address that are in the Contacts table.

I’m attempting to write a web service that will replace the database call. To test, I have changed the variable $q to one of the email addresses to see if the list would return the result for only that email address. When I reload the page I am still getting all of the email address returned.

Do you know of any reason why this might be? Right now the rewritten method looks like:

        public function getPrimaryAddress($focus, $parent_id = null, $parent_type = null)
    {
        $parent_type = empty($parent_type) ? $focus->module_dir : $parent_type;
        // Bug63174: Email address is not shown in the list view for employees
        $parent_type = $this->getCorrectedModule($parent_type);
        $parent_id = empty($parent_id) ? $focus->id : $parent_id;

        /*$q = "SELECT ea.email_address FROM email_addresses ea
                LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
                WHERE ear.bean_module = '" . $this->db->quote($parent_type) . "'
                AND ear.bean_id = '" . $this->db->quote($parent_id) . "'
                AND ear.deleted = 0
                AND ea.invalid_email = 0
                ORDER BY ear.primary_address DESC";*/

        $q = "sugar.section@example.com";
        $r = $this->db->limitQuery($q, 0, 1);
        $a = $this->db->fetchByAssoc($r);

        if (isset($a['email_address'])) {
            return $a['email_address'];
        }

        ChromePhp::log("Check 1");
        ChromePhp::log("This is q", $q);

        return $q;
    }

Hi, @GraemeMac!
If you want to change data I recomend to use logic hook process_record (https://docs.suitecrm.com/developer/logic-hooks, Example 12.1 & Example 12.2). Chaning the standart function isn’t good way.
If you give more information I can write exmple for you.

1 Like

For logic hooks, I can’t see one that fires after data has been added to a view. The logic hooks listed are for events associated with applications, users, module actions and job queue events.

I want to add further user information to a table from a data set separate from what is being retrieved from the database. The only hook I can see that might work is “process_record”. The hook would have to wait for the last record to be loaded before it calls the API and loads the additional data into the required view.

How would we determine when the last record is loaded in the hook? How would we connect the API to the view. I’m guessing that we can determine the required file based on the path modules/Contacts/views/view.list.php. I can write the API and (gradually) build the required structure for the view but determining how to connect the API to the view/last record that has been loaded are questions that I can’t answer.

@GraemeMac
You can make special field in module Contacts and one of parameters write: ‘function’. For example there is my code for write age of contact.
Field in custom/Extension/modules/Contacts/Ext/Vardefs/age_years.php of Contacts module:

 $dictionary['Lead']['fields']['age_years']  = 
  array (
    'name' => 'age_years',
    'vname' => 'LBL_AGE_YEARS',
    'type' => 'int',
    'len' => '3',
    'comment' => '',
    'importable' => true,
    'function' => array('name' => 'get_age_years', 'returns' => 'html', 'onListView' => 'true', 'include' => 'custom/modules/Contacts/Contacts_fields_functions.php'),
    'studio' => 'false',
    'source' => 'non-db',
  );

Function in custom/modules/Contacts/Contacts_fields_functions.php

<?php
function get_age_years($focus, $field, $value, $view) {
    if ($view == 'ListView') {
        if (isset ($focus['ID']) && !empty($focus['ID'])) {
            $contact = new Contact();
            $contact->retrieve($focus['ID']);
            if (!empty($contact->birthdate)) {
                $age_years=ageYears($contact->birthdate);
            }else{
                $age_years = '-';
            }
            return $age_years;
        } else {
            return "?";
        }
    } elseif ($view == 'DetailView') {
            if (!empty($focus->birthdate)) {
                $age_years=ageYears($focus->birthdate);
            }else{
                $age_years = '-';
            }
            return $age_years;
    }
}
function ageYears ($birthdate){
    global $timedate;
    $birthdate_date = date_create_from_format($timedate->get_date_format(),$birthdate);
    $age_years = date("Y") - $birthdate_date->format("Y");
    if (date("md") < $birthdate_date->format("md")) {
        $age_years = $age_years - 1;
    }
    if ($age_years < 0 || $age_years > 999){
        $age_years = '-';
    }
    return $age_years;
}

And add the field ‘age_years’ to custom/modules/Contacts/metadata/listviewdefs.php

...
  'AGE_YEARS' => 
  array (
    'width' => '5%',
    'label' => 'LBL_AGE_YEARS',
    'default' => true,
    'sortable' => false,
  ),
...
2 Likes

I tried updating the code in the CRM but encountered a few problems.

The files you mentioned do not exist. I tried to build new ones with the code you have written but I am now receiving the following error message on the Contacts page

It looks as if listviewdefs.php is throwing up the error - there is no structure in the file so I pasted in the code raw to the newly created file. I don’t know know if the versions of SuiteCRM we have are the same. I am running Version 7.11.10 Sugar Version 6.5.25 (Build 344). If you can think of any other reason that might be causing the problem let me know.

Thanks

That’s the error that happened to pop up on screen (I always turn off display_errors in php.ini, and this is especially important in XAMPP as it seems to break some screens unless you do that).

But check all the errors in the PHP log. My guess is that you will find some complaint about PHP syntax in the file you altered (a missing comma or bracket), so the variable definition fails, so you get that notice afterwards.

@GraemeMac
I wrote only part code of listviewdefs.php . I wrote symbols: ‘…’ . You should change that for your code. For example full file listviewdefs.php:

<?php
$listViewDefs ['Contacts'] = 
array (
  'NAME' => 
  array (
    'width' => '15%',
    'label' => 'LBL_LIST_NAME',
    'link' => true,
    'contextMenu' => 
    array (
      'objectType' => 'sugarPerson',
      'metaData' => 
      array (
        'contact_id' => '{$ID}',
        'module' => 'Contacts',
        'return_action' => 'ListView',
        'contact_name' => '{$FULL_NAME}',
        'parent_id' => '{$ACCOUNT_ID}',
        'parent_name' => '{$ACCOUNT_NAME}',
        'return_module' => 'Contacts',
        'parent_type' => 'Account',
        'notes_parent_type' => 'Account',
      ),
    ),
    'orderBy' => 'name',
    'default' => true,
    'related_fields' => 
    array (
      0 => 'first_name',
      1 => 'last_name',
      2 => 'salutation',
      3 => 'account_name',
      4 => 'account_id',
    ),
  ),
  'TITLE' => 
  array (
    'width' => '15%',
    'label' => 'LBL_LIST_TITLE',
    'default' => true,
  ),
  'ACCOUNT_NAME' => 
  array (
    'width' => '15%',
    'label' => 'LBL_LIST_ACCOUNT_NAME',
    'module' => 'Accounts',
    'id' => 'ACCOUNT_ID',
    'link' => true,
    'contextMenu' => 
    array (
      'objectType' => 'sugarAccount',
      'metaData' => 
      array (
        'return_module' => 'Contacts',
        'return_action' => 'ListView',
        'module' => 'Accounts',
        'parent_id' => '{$ACCOUNT_ID}',
        'parent_name' => '{$ACCOUNT_NAME}',
        'account_id' => '{$ACCOUNT_ID}',
        'account_name' => '{$ACCOUNT_NAME}',
      ),
    ),
    'default' => true,
    'sortable' => true,
    'ACLTag' => 'ACCOUNT',
    'related_fields' => 
    array (
      0 => 'account_id',
    ),
  ),
  'PHONE_MOBILE' => 
  array (
    'width' => '15%',
    'label' => 'LBL_MOBILE_PHONE',
    'default' => true,
  ),
  'EMAIL1' => 
  array (
    'width' => '15%',
    'label' => 'LBL_LIST_EMAIL_ADDRESS',
    'sortable' => false,
    'link' => true,
    'customCode' => '{$EMAIL1_LINK}{$EMAIL1}</a>',
    'default' => true,
  ),
  'AGE_YEARS' => 
  array (
    'width' => '5%',
    'label' => 'LBL_AGE_YEARS',
    'default' => true,
    'sortable' => false,
  ),
  'ASSIGNED_USER_NAME' => 
  array (
    'width' => '10%',
    'label' => 'LBL_LIST_ASSIGNED_USER',
    'module' => 'Employees',
    'id' => 'ASSIGNED_USER_ID',
    'default' => true,
  ),
);
?>

Add file: custom/Extension/modules/Contacts/Ext/Language/en_us.age_years.php

<?php
$mod_strings['LBL_AGE_YEARS'] = 'Age';

And after that make: Admin-> Repair -> Quick Repair and Rebuild

1 Like

Thanks @p.konetskiy, the code you have written is working. I can follow the structure of the listviewdefs.php file and see the structure of the new table here

I have a couple of questions regarding how the table works before we can wrap up. The first is around the age column itself. The age_years.php file isn’t included anywhere so how does the table know the structure of the column itself i.e. length, source, type etc. I’m receiving an error message when I try to edit the field that reads

There was an error loading the field. Your session may have timed out. Please log in again to fix this

Also, more importantly, how could we load data into the field from an external source? From what I can tell, the get_age_years function is never called. I could add a web service call to this function to retrieve the age from another data source that corresponds to the users ID and populates the age field but the function would have to populate the view.

The solution you have provided also does not solve the issue of appending additional users from an external data source to the table. Do you see a way of encompassing this into the solution?

@GraemeMac

  1. You can’t change field ‘age_years’ because it isn’t data, it’s function.
  2. The function get_age_years call aumaticly for field ‘age_years’ when the form listview creat. If you add standard field birthday into editview form of Contacts module and select date for person, you will see result in listview.
  3. You can make some fields for one module wich use spesial function and this function can read data from external data source.

Example: custom/modules/Contacts/metadata/editviewdefs.php whith field ‘birthdate’:

<?php
$viewdefs ['Contacts'] =
array(
  'EditView' =>
  array(
    'templateMeta' =>
    array(
      'form' =>
      array(
        'hidden' =>
        array(
          0 => '<input type="hidden" name="opportunity_id" value="{$smarty.request.opportunity_id}">',
          1 => '<input type="hidden" name="case_id" value="{$smarty.request.case_id}">',
          2 => '<input type="hidden" name="bug_id" value="{$smarty.request.bug_id}">',
          3 => '<input type="hidden" name="email_id" value="{$smarty.request.email_id}">',
          4 => '<input type="hidden" name="inbound_email_id" value="{$smarty.request.inbound_email_id}">',
        ),
      ),
      'maxColumns' => '2',
      'widths' =>
      array(
        0 =>
        array(
          'label' => '10',
          'field' => '30',
        ),
        1 =>
        array(
          'label' => '10',
          'field' => '30',
        ),
      ),
            'includes' => array (
                0 => 
                array (
                    'file' => 'include/javascript/tiny_mce/tiny_mce.js',
                ),
            ),
      'useTabs' => false,
      'tabDefs' =>
      array(
        'LBL_CONTACT_INFORMATION' =>
        array(
          'newTab' => false,
          'panelDefault' => 'expanded',
        ),
        'LBL_PANEL_CONNECTS' => array(
            'newTab' => false,
            'panelDefault' => 'expanded',                    
        ),
        'LBL_PANEL_ADVANCED' =>
        array(
          'newTab' => false,
          'panelDefault' => 'expanded',
        ),
      ),
    ),
    'panels' =>
    array(
      'lbl_contact_information' =>
      array(
        0 =>
        array(
          0 =>
          array(
            'name' => 'first_name',
            'customCode' => '{html_options name="salutation" id="salutation" options=$fields.salutation.options selected=$fields.salutation.value}&nbsp;<input name="first_name"  id="first_name" size="25" maxlength="25" type="text" value="{$fields.first_name.value}">',
          ),
          1 =>
          array(
            'name' => 'last_name',
          ),
        ),
        1 =>
        array(
          0 =>
          array(
            'name' => 'phone_work',
            'comment' => 'Work phone number of the contact',
            'label' => 'LBL_OFFICE_PHONE',
          ),
          1 =>
          array(
            'name' => 'phone_mobile',
            'comment' => 'Mobile phone number of the contact',
            'label' => 'LBL_MOBILE_PHONE',
          ),
        ),
        2 =>
        array(
          0 =>
          array(
            'name' => 'title',
            'comment' => 'The title of the contact',
            'label' => 'LBL_TITLE',
          ),
          1 => 'department',
        ),
        3 =>
        array(
          0 =>
          array(
            'name' => 'account_name',
            'displayParams' =>
            array(
              'key' => 'billing',
              'copy' => 'primary',
              'billingKey' => 'primary',
              'additionalFields' =>
              array(
                'phone_office' => 'phone_work',
              ),
            ),
          ),
          1 => 
          array (
            'name' => 'birthdate',
            'label' => 'LBL_BIRTHDATE',
          ),
        ),
        4 =>
        array(
          0 =>
          array(
            'name' => 'email1',
            'studio' => 'false',
            'label' => 'LBL_EMAIL_ADDRESS',
          ),
        ),
        5 =>
        array(
          0 =>
          array(
            'name' => 'primary_address_street',
            'hideLabel' => true,
            'type' => 'address',
            'displayParams' =>
            array(
              'key' => 'primary',
              'rows' => 2,
              'cols' => 30,
              'maxlength' => 150,
            ),
          ),
          1 =>
          array(
            'name' => 'alt_address_street',
            'hideLabel' => true,
            'type' => 'address',
            'displayParams' =>
            array(
              'key' => 'alt',
              'copy' => 'primary',
              'rows' => 2,
              'cols' => 30,
              'maxlength' => 150,
            ),
          ),
        ),
        6 =>
        array(
          0 =>
          array(
            'name' => 'description',
            'label' => 'LBL_DESCRIPTION',
          ),
          1 => '',
        ),
        7 =>
        array(
          0 =>
          array(
            'name' => 'assigned_user_name',
            'label' => 'LBL_ASSIGNED_TO_NAME',
          ),
        ),
      ),
            'lbl_panel_connects' => array (
                0 => array (
                    0 => array (
                        'name' => 'bf_connects',
                        'type' => 'collection',
                        'displayParams' => array (
                            'collection_field_list' => array(
                                array(
                                    'name' => 'title',
                                    'displayParams' => array (
                                        'size'=>'20%',
                                    ),
                                ),
                                array(
                                    'name' => 'address_link',
                                    'displayParams' => array (
                                        'size'=>'25%',
                                    ),
                                ),
                                array(
                                    'name' => 'attribute',
                                    'displayParams' => array (
                                        'size'=>'5%',
                                    ),
                                ),
                                array(
                                    'name' => 'date_start',
                                    'displayParams' => array (
                                        'size'=>'10%',
                                    ),
                                ),
                                array(
                                    'name' => 'description',
                                    'displayParams' => array (
                                        'size'=>'30%',
                                    ),
                                ),
                            ),
                        ),
                    ),
                ),
            ),
      'LBL_PANEL_ADVANCED' =>
      array(
        0 =>
        array(
          0 =>
          array(
            'name' => 'lead_source',
            'comment' => 'How did the contact come about',
            'label' => 'LBL_LEAD_SOURCE',
          ),
        ),
        1 =>
        array(
          0 =>
          array(
            'name' => 'report_to_name',
            'label' => 'LBL_REPORTS_TO',
          ),
          1 => 'campaign_name',
        ),
      ),
    ),
  ),
);

P.S.
Change small bug in listview.
Old code:

'customCode' => '{$EMAIL1_LINK}{$EMAIL1}</a>',

New code:

'customCode' => '{$EMAIL1_LINK}',
1 Like