LinkBack URL
About LinkBacks
Ideas to Improve Relate Fields in SugarCRM
For my ongoing documentation work I've investigated the workings of the Relate field type, both the Vardefs and the SugarField. To see the Wiki pages on this see:
- Vardefs: http://www.sugarcrm.com/wiki/index.p...(Vardefs_field)
- SugarField: http://www.sugarcrm.com/wiki/index.php?title=Relate
The Relate field type can be a very powerful way to display fields from other beans, but at this moment it's limited by Sugar. Relate fields are used by one-to-one or one-to-many (on the 'many' side) relationships, and thus hold the contents of one related bean. That way it's possible to display the Account Name in Contacts, for instance.
Back in the day the code needed to accomplish this was seen mainly in fill_additional_*_fields() in Sugar's core files. These functions would then create their own queries to fill the field. That method was, of course, very strict on customization. Now, with logic hooks we have a upgrade-safe method of adding these values to the bean in almost all cases.
Logic hooks are an improvement, but not a final solution. Here is a list of disadvantages of the current solution:
- You cannot search for values filled in a logic hook. We'll see in a moment that all Sugar's related field searches (like searching for Account Name in Contacts), actually require manual coding in the Sugar Bean, as seen in Contact::build_generic_where_clause(). But, that is not customizable:
PHP Code:Contact::build_generic_where_clause()
array_push($where_clauses, "contacts.last_name like '$the_query_string%'");
array_push($where_clauses, "contacts.first_name like '$the_query_string%'");
array_push($where_clauses, "accounts.name like '$the_query_string%'");
array_push($where_clauses, "contacts.assistant like '$the_query_string%'");
array_push($where_clauses, "ea.email_address like '$the_query_string%'");
- Sugar doesn't 'get' your metadata. Since SugarCRM really doesn't 'understand' your data structure once you use a logic hook, it has no clue on how to implement this on (very) different SQL servers, or how to audit the values, etc.
- You need extra code in Bean files that can already be handled perfectly by SugarBean. It cannot be customized in core beans, and it needs to be re-done in custom beans, leading to even more code. It's just old stuff waiting to be removed:
PHP Code:Contact::fill_in_additional_detail_fields()
// retrieve the account information and the information about the person the contact reports to.
$query = "SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name
from contacts
left join accounts_contacts a_c on a_c.contact_id = '".$this->id."' and a_c.deleted=0
left join accounts acc on a_c.account_id = acc.id and acc.deleted=0
left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id
where contacts.id = '".$this->id."'";
$result = $this->db->query($query,true," Error filling in additional detail fields: ");
// Get the id and the name.
$row = $this->db->fetchByAssoc($result);
if($row != null)
{
$this->account_name = $row['name'];
$this->account_id = $row['id'];
$this->report_to_name = $row['first_name'].' '.$row['last_name'];
}
else
{
$this->account_name = '';
$this->account_id = '';
$this->report_to_name = '';
}
- Logic hooks are bloated and difficult to implement. They need their own class, they need to be called at least twice (after_retrieve and process_record to catch all views), files must be writable, etc. Vardefs definitions are much more clean and keep all of the data structure information (metadata) in one place.
Meanwhile, Vardefs and SugarBean can handle *any* related field, not just the Account Name or Member Of...This can be a very powerful functionality, with a few modifications to Sugar:
We need:
- A way to specify whether to include a link in the Relate SugarField. Now it's hard-coded into the SugarField
- A small change to the search query builder. It now uses a "=" instead of a LIKE to match
Practical Example
Imagine, for instance, that I want to alter Contacts to use the Account's office phone number instead.
Step 1: Remove the field from Contacts database* and add a relate field:
(using lead_source field in this example but could be any field)
Step 2: Remove the field from EditView since its contents are not in Accounts.PHP Code:'lead_source' => array (
'name' => 'lead_source',
'rname' => 'phone_office',
'id_name' => 'account_id',
'vname' => 'LBL_LEAD_SOURCE',
'required' => false,
'type' => 'relate',
'table' => 'accounts',
'isnull' => 'true',
'module' => 'Accounts',
'massupdate' => false,
'source'=>'non-db',
'len' => '255',
'link'=>'accounts',
'unified_search' => true,
'importable' => 'false',
),
Step 3: Make search work: Remove the buttons from the Lead Source searchform field (possible)
Step 4: Do a search
The query will be:
SolutionPHP Code:SELECT contacts.id , CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation , accounts.name account_name, jtl0.account_id account_id, contacts.title , contacts.phone_work , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod , jt2.phone_office lead_source, jtl2.account_id account_id, contacts.assigned_user_id
FROM contacts
LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
AND accounts.deleted=0
LEFT JOIN users jt1 ON jt1.id= contacts.assigned_user_id AND jt1.deleted=0
AND jt1.deleted=0
LEFT JOIN accounts_contacts jtl2 ON contacts.id=jtl2.contact_id AND jtl2.deleted=0
LEFT JOIN accounts jt2 ON jt2.id=jtl2.account_id
AND jt2.deleted=0
AND jt2.deleted=0
-----> WHERE ((jt2.phone_office = '%(434)%'))
AND contacts.deleted=0
ORDER BY name ASC
Notice the "where ((jt2.phone_office = '(434)'))". This now only works if one uses the SELECT button to get the exact value. Suppose you want to search every Contact working at an Account in a town's phone region. Then the query should be:
Now this is power! We can also search for parts of values too! Of course this is already possible with default fields, as seen in Contact::build_generic_where_clause(). But, that method has some disadvantages as mentioned earlier.PHP Code:WHERE ((jt2.phone_office LIKE '%(434)%'))
Advantages
- Real low-level implementation! SEARCHING, no hacks, no code fields, no logic hook classes, no core hacks, no overriding of SugarBean methods, no upgrade troubles, less code to maintain, less dependencies, less duplicated logic
- ALL fields from a related bean can be used as input for fields of the current bean
- LESS queries (dozens in a ListView), faster executing, less logged output
- Better equipped for Studio/MB
- Better equipped for object caching
- Possibly better equipped for related fields in Reports
Performance
Of course, searching with LIKE is slower then with =. But, as the code examples show, many (if not all) of the related search queries use LIKE. So this doesn't seem like an issue.
Conclusion
If SugarCRM wishes to label Sugar as a PaaS solution, the functionality should be the same for customized or standard fields. Changes should be made to SearchForm2.php to make sure a LIKE is used when searching for related fields, not just in all Sugar's default fields!
Posted as a feature request at the bug tracker. I'm posting it here to hear your thoughts and because reading with syntax highlighting is much easier.
* One bug that crops up here: If you change a database field into a relate field with source => non-db, if the field still exists in the database it will get read anyway, despite the non-db value on the source attribute.
Reply With Quote
Re: Ideas to Improve Relate Fields in SugarCRM
Originally Posted by SanderMarechal 
Bookmarks