Hi there.
I am making a custom sub panel to display accounts that are related to an opportunity but are't the paying account
There is a custom 1 to many relationship from Accounts to Opportunities
In opportunities there is a custom field which is a related link to the opportunities and shows up in opportunities_cstm as account_id1_c
What I want is for an Account give me the opportunities that have opportunity_cstm.account_id1_c = the account id where opportunity.id = opportunity_cstm.idc
The logic I can see in the relationships is
This relationship is called from Accounts so base id is the account I am looking at
Its looking at opportunities as thats the end result I want
The link is through opportunities_cstm which has the account id link field I need account_id1_c
JoinTable keeps adding an extra condition that the joined table.id = the master module.bean id i.e. the account id. I don't know why or how to get rid of it
Perhaps this is no longer the way to do custom subpanels?
All help gratefully appreciated. Details below
I have made a custom relationship
custom/Extension/modules/Accounts/Ext/Vardefs/accounts_opportunities_1_Accounts.php
<?php$dictionary["Account"]["fields"]["accounts_opportunities_1"] = array ('name' => 'accounts_opportunities_1','type' => 'link','relationship' => '','source' => 'non-db','module' => 'Opportunities','bean_name' => 'Opportunity','vname' => 'LBL_OPPORTUNITIES_FOR_ACCOUNT_SUBPANEL_TITLE','id_name' => 'accounts_opportunities_1accounts_ida','link-type' => 'many','side' => 'left','link_file' => "custom/modules/Accounts/OpportunitiesForAccountLink.php",'link_class' => "OpportunitiesForAccountLink",);
Modified the label
custom/Extension/modules/Accounts/Ext/Language/en_us.opportunities_for_cases_subpanel.php
<?php
$mod_strings['LBL_OPPORTUNITIES_FOR_ACCOUNT_SUBPANEL_TITLE'] = 'All related Opportunities that are not Paying';
Edited the subpanel defintitions
custom/Extension/modules/Accounts/Ext/clients/base/layouts/subpanels/accounts_opportunities_1_Accounts.php<?php// created: 2018-03-07 14:38:22$viewdefs['Accounts']['base']['layout']['subpanels']['components'][] = array ('layout' => 'subpanel','label' => 'LBL_OPPORTUNITIES_FOR_ACCOUNT_SUBPANEL_TITLE','context' =>array ('link' => 'accounts_opportunities_1',),);
And I get to the relationship and its now all based on using joinTable instead of joinRaw and I am struggling with getting the syntax right
public function buildJoinSugarQuery($sugar_query, $options = array())
{
$joinParams = array('joinType' => isset($options['joinType']) ? $options['joinType'] : 'INNER');
$jta = 'accounts_opportunities_1';
if (!empty($options['joinTableAlias'])) {
$jta = $joinParams['alias'] = $options['joinTableAlias'];
}
// $sugar_query->joinTable($this->getCustomJoin($options), $joinParams);$sugar_query->joinTable('opportunities_cstm', $joinParams)->on()->equalsField('id',$jta.'.id_c');
$sugar_query->where()->equals($jta.'.account_id1_c', $this->focus->id);
$preparedStmt = $sugar_query->compile();
$GLOBALS['log']->debug($preparedStmt->getSql());
return $GLOBALS['log']->debug($preparedStmt->getParameters());}
What happens is that the join is almost there but it adds a condition for the join table link.id to equal the module id which isn't correct.
An exception occurred while executing
'SELECT opportunities.id, opportunities.date_modified, opportunities.assigned_user_id, opportunities.created_by
FROM opportunities
INNER JOIN opportunities_cstm jt0_accounts_opportunities_1 ON (opportunities.id = jt0_accounts_opportunities_1.id_c) AND (jt0_accounts_opportunities_1.id = ?)
LEFT JOIN opportunities_cstm opportunities_cstm ON opportunities_cstm.id_c = opportunities.id
WHERE (jt0_accounts_opportunities_1.account_id1_c = ?) AND (opportunities.deleted = ?)
ORDER BY opportunities.date_modified DESC, opportunities.id DESC LIMIT 11'
with params ["294060bc-252d-11e9-8c21-022504f1067d", 0]:
Unknown column 'jt0_accounts_opportunities_1.id' in 'on clause'\
The additional problem statement is bolded. Everything works without that statement. There is no id field in the opportunities_cstm, but even if there was it has nothing to link to and the test expects it to match the accounts module id.
How do I arrange this so I can get the join right - is it even possible now?
Thanks again