I am trying to add a one-to-many relationship between ProductTemplates and RevenueLineItems without using studio so that I do not get a middle table. This is using a technique described here - Programming and web resources » How to create an one-to-many relationship without an intermediate table in SugarCRM
Here is my new file (custom/Extension/modules/RevenueLineItems/Ext/Vardefs/parent_product_template.php):
<?php $dictionary["RevenueLineItem"]["relationships"]["revenuelineitem_parent_product_template"] = array( 'lhs_module' => 'ProductTemplates', 'lhs_table' => 'product_templates', 'lhs_key' => 'id', 'rhs_module' => 'RevenueLineItems', 'rhs_table' => 'revenue_line_items', 'rhs_key' => 'parent_product_id_c', 'relationship_type' => 'one-to-many' ); $dictionary["RevenueLineItem"]["fields"]["revenuelineitem_parent_product_template_link"] = array( 'name' => 'revenuelineitem_parent_product_template_link', 'type' => 'link', 'relationship' => 'revenuelineitem_parent_product_template', 'module' => 'ProductTemplates', 'bean_name' => 'ProductTemplate', 'source' => 'non-db', 'vname' => 'LBL_PARENT_PRODUCT', ); $dictionary['RevenueLineItem']['fields']['parent_product_name'] = array( 'name' => 'parent_product_name', 'rname' => 'name', 'id_name' => 'parent_product_id_c', 'vname' => 'LBL_PARENT_PRODUCT', 'type' => 'relate', 'link' => 'revenuelineitem_parent_product_template_link', 'table' => 'product_templates', 'isnull' => 'true', 'module' => 'ProductTemplates', 'source' => 'non-db', 'required' => false, ); $dictionary['RevenueLineItem']['fields']['parent_product_id_c'] = array( 'name' => 'parent_product_id_c', 'rname' => 'id', 'vname' => 'LBL_PARENT_PRODUCT', 'type' => 'id', 'table' => 'product_templates', 'isnull' => 'true', 'module' => 'ProductTemplates', 'dbType' => 'id', 'reportable' => false, 'audited' => true, 'duplicate_merge' => 'disabled', 'source' => 'custom_fields', 'required' => false );
This does not seem to work, I think because the parent_product_id_c ID field is created on the custom table - product_templates_cstm. I have tried changing the rhs_table field in the revenuelineitem_parent_product_template relationship to product_templates_cstm but this also does not appear to work.
Has anyone been able to achieve anything similar?