Results 1 to 5 of 5

Thread: Ideas to Improve Relate Fields in SugarCRM

  1. #1
    SugarDev.net is offline Sugar Community Member
    Join Date
    Feb 2008
    Posts
    1,401

    Default 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:

    1. Vardefs: http://www.sugarcrm.com/wiki/index.p...(Vardefs_field)
    2. 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:

    1. 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%'"); 
    2. 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.
    3. 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 '';

    4. 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:
    1. A way to specify whether to include a link in the Relate SugarField. Now it's hard-coded into the SugarField
    2. 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)

    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 2: Remove the field from EditView since its contents are not in Accounts.

    Step 3: Make search work: Remove the buttons from the Lead Source searchform field (possible)

    Step 4: Do a search

    The query will be:

    PHP Code:
    SELECT contacts.id CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as namecontacts.first_name contacts.last_name contacts.salutation accounts.name account_namejtl0.account_id account_idcontacts.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_sourcejtl2.account_id account_idcontacts.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=
    LEFT JOIN users jt1 ON jt1
    .idcontacts.assigned_user_id AND jt1.deleted=0
    AND jt1.deleted=
    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=

    -----> WHERE ((jt2.phone_office '%(434)%')) 

    AND 
    contacts.deleted=
    ORDER BY name ASC 
    Solution

    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:

    PHP Code:
    WHERE ((jt2.phone_office LIKE '%(434)%')) 
    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.

    Advantages
    1. 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
    2. ALL fields from a related bean can be used as input for fields of the current bean
    3. LESS queries (dozens in a ListView), faster executing, less logged output
    4. Better equipped for Studio/MB
    5. Better equipped for object caching
    6. 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.
    Last edited by SugarDev.net; 2009-03-04 at 02:39 PM.
    Developers go here
    Businesses go there (Dutch)

    Modules:
    SugarDev.net Developer Tools | Config | Dutch Language Pack
    "Nothing gets fixed unless there is a bug"

  2. #2
    SanderMarechal is offline Sugar Community Member
    Join Date
    Dec 2007
    Posts
    49

    Default Re: Ideas to Improve Relate Fields in SugarCRM

    It sounds like a great idea. I like it.

    The real problem is of course not making the changes required to implement this, but fixing up all the existing code to use this instead of the module-specific workarounds. If you don't do that then it's just more feature bloat instead of cleaner code.

  3. #3
    eggsurplus's Avatar
    eggsurplus is offline Sugar Community Member
    Join Date
    Dec 2005
    Location
    Minnesota
    Posts
    2,858

    Default Re: Ideas to Improve Relate Fields in SugarCRM

    Yes! Having this included in Sugar would be great! I had to create my own field type that lets you add that field to the search and list views so that you can search and display related data from other modules (data in subpanels or relate field modules). It took some changing of core code to make it work but not too much. The module is in my sig below.
    [SIGPIC][/SIGPIC]

    Using CE and have 2 or more users? You need SecuritySuite - Teams

  4. #4
    genius786's Avatar
    genius786 is offline Sugar Community Member
    Join Date
    Nov 2008
    Location
    Karachi, Pakistan
    Posts
    150

    Smile Re: Ideas to Improve Relate Fields in SugarCRM

    Hi,

    Awesome idea you describe ......


    Best regards,
    SARFARAZ AHMED KHAN
    Karachi, Pakistan
    skype: genius_crystal
    genius_crystal@hotmail.com
    +92-314-2595624

    Drip/Auto Email Marketing Tool: http://www.sugarforge.org/projects/drip/
    Latest Headline News Alert: http://www.sugarforge.org/projects/news-alert/
    Login User History Module: http://www.sugarforge.org/projects/user-history/
    Re Imbursement Module: http://www.sugarforge.org/projects/reimbursement/
    Skype Click to Call Module: http://www.sugarforge.org/projects/skypecall/
    HR & Attendance Module: http://www.sugarforge.org/projects/hr-attendance/

  5. #5
    SugarDev.net is offline Sugar Community Member
    Join Date
    Feb 2008
    Posts
    1,401

    Default Re: Ideas to Improve Relate Fields in SugarCRM

    Quote Originally Posted by SanderMarechal View Post
    It sounds like a great idea. I like it.

    The real problem is of course not making the changes required to implement this, but fixing up all the existing code to use this instead of the module-specific workarounds. If you don't do that then it's just more feature bloat instead of cleaner code.
    Very true. But nevertheless I think it can be done in a relatively small amount of time. Basically there are 2 things to keep in mind:

    1. SugarCRM code
    2. Custom code

    Now, these related fields are filled with a 'dirty' function after the SugarBean fills them with "fill_related_fields", so right now the changes get overwritten. So it *is* already functioning, it just gets overwritten immediately. [1] can be changed fairly quickly, and if we forget anything the existing code will be at least as functional as the one I envision. For [2], functionality will also hold.
    Last edited by SugarDev.net; 2009-06-10 at 10:01 PM.
    Developers go here
    Businesses go there (Dutch)

    Modules:
    SugarDev.net Developer Tools | Config | Dutch Language Pack
    "Nothing gets fixed unless there is a bug"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sugar is slow - some ideas to improve the speed
    By mycrmspacegunnar in forum General Discussion
    Replies: 22
    Last Post: 2012-04-04, 02:44 PM
  2. Relate Fields dropdown
    By salimmohd82 in forum Developer Help
    Replies: 3
    Last Post: 2008-03-14, 11:01 PM
  3. Relate Fields are mandatory
    By salimmohd82 in forum Help
    Replies: 0
    Last Post: 2008-03-13, 07:02 PM
  4. My sugarCRM displays SQL errors, any ideas, please!!?
    By mfeoli in forum General Discussion
    Replies: 8
    Last Post: 2007-07-11, 11:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •