AnsweredAssumed Answered

How can we insert records belonging to multiple modules in to custom subpanel in sugarcrm 7.6

Question asked by Anand Chitikela on Dec 3, 2015

Hi

 

     I have created a subpanel by following

How to create a subpanel in sugarcrm 7.6 in Accounts module.

 

 

     I have created an custom field-relatedaccount_c(related to accounts module)field in the Calls/Meetings/Tasks modules.This field appears only when the Parent Type="Projects" for the calls/Meetings/Tasks.So in the accounts module i want a Project Activities subpanel in which we can see all the list of calls/meetings/tasks whose parent type is Projects and  have relatedaccount_c.This is something similar to the history subpanel in sugarcrm 6.Gustav LindströmShijin KrishnaAjay Kumar Prashant Patel

 

    I want to insert a query like

SELECT calls_result.id FROM calls as calls_result

LEFT JOIN calls_cstm cc ON calls_result.id=cc.id_c AND calls_result.deleted=0

LEFT JOIN accounts a ON a.id=cc.account_id_c AND a.deleted='0' WHERE calls_result.parent_type='P_Projects' AND a.id='e716f666-4035-afde-b44f-55fc77b09254'

UNION ALL

SELECT meetings_result .id FROM meetings as meetings_result

LEFT JOIN meetings_cstm mm ON meetings_result .id=mm.id_c AND meetings_result .deleted=0

LEFT JOIN accounts ac ON ac.id=mm.account_id_c AND ac.deleted='0' WHERE meetings_result .parent_type='P_Projects' AND ac.id='e716f666-4035-afde-b44f-55fc77b09254'

UNION ALL

SELECT tasks_result .id FROM tasks as tasks_result

LEFT JOIN tasks_cstm tt ON tasks_result .id=tt.id_c AND tasks_result .deleted=0

LEFT JOIN accounts acc ON acc.id=tt.account_id_c AND acc.deleted='0' WHERE tasks_result .parent_type='P_Projects' AND acc.id='e716f666-4035-afde-b44f-55fc77b09254'

 

Reference:Shane Dowling – SugarCRM 7 - Custom subpanels

to retreive the ids of the meetings,tasks,calls so that they can be inserted in to my subpanel at the funtion

protected function getCustomJoin($params = array())

   {

   $bean_id = $this->db->quoted($this->focus->id);

   $sql = " INNER JOIN(";

   $sql .= "SELECT id FROM accounts WHERE id={$bean_id}"; // This is essentially a select statement that will return a set of ids that you can match with the existing sugar_query
   $sql .= ") accounts_result ON accounts_result.id = sugar_query_table.id";

   return $sql;

   }

Outcomes