Add grandparent fields to your SugarCRM modules

by Sander Marechal

When you build custom modules in SugarCRM and you create one-to-many relations between them, you can put a link to the parent module the various views. But what if that parent module has a one-to-many relation to yet another module? By default SugarCRM does not support adding links to grandparent modules on your views. For example, suppose you have a custom Invoices module which relates to Accounts and you have an InvoiceLines module that relates to Invoices. When you look at an invoice it will contain a link to the account. When you look at an invoice line then you will see a link to the invoice. But you cannot add a link directly from the invoice line to the account.

In this article I will show you how you can add links and fields from grandparent modules to your own custom modules. I will build on the invoicing package I created in my article “SugarCRM many-to-one relations to standard modules”. That packages has an Invoices module that relates to Accounts, and an InvoiceLines module that relates to Invoices. I will add fields to the InvoiceLines module that directly links to the account.

You can download the base invoicing package that I built in my previous article as a starting point, or you can download the finished invoicing package with the grandparent fields already added to the InvoiceLines module.

0: Table of contents

  1. 1: How SugarCRM loads related modules
  2. 2: Making SugarCRM load grandparent relationships
    1. 2.1: Adding the grandparent fields to the vardefs
    2. 2.2: Loading related fields with a bean filter
    3. 2.3: Add the grandparent fields to the viewdefs
  3. 3: Pro example and source code

1: How SugarCRM loads related modules

Before we can start adding grandparent fields you first need to understand how SugarCRM loads related modules. As you can see from my article about many-to-one relations in SugarCRM, a relationship is defined in the vardefs using three separate fields. First there is the field that holds the value of the related module. In the previous article we added the account_name field to the Invoice module vardefs as shown below.

  1. $dictionary["inv_Invoices"]["fields"]["account_name"] = array (
  2.   'name' => 'account_name',
  3.   'type' => 'relate',
  4.   'id_name' => 'account_id',
  5.   'link' => 'accounts_inv_invoices',
  6.   'module' => 'Accounts',
  7.   'rname' => 'name',
  8.   ...
  9. );

In the account_name field shown above, the id_name attribute points to a second field that we added to the vardefs called account_id. This account_id field is of type “link” and it will hold the ID of the account that we are relating to. The link attribute points to a third field that we added to the vardefs, the accounts_inv_invoices field. This field will hold the actual relationship that links the two modules. The module attribute tells us that the related module is an account. The rname attribute tells SugarCRM that we want account_name to hold the “name” field of the related account.

When SugarCRM loads an invoice, it will see that account_name is a related field and that the ID is stored in account_id. It then calls the fill_in_link_field() method on the SugarBean to load the ID of the related module in the account_id field. This function first loads the relationship into account_id and then calls the get() method on it, which returns the correct ID value. Here is a closer look at the fill_in_link_field() method.

  1. /*
  2.  * Fill in a link field
  3.  */
  4. function fill_in_link_field($linkFieldName)
  5. {
  6.         if ($this->load_relationship($linkFieldName)) {
  7.                 $list = $this->$linkFieldName->get();
  8.                 $this->$linkFieldName = ''; // match up with null value in $this->populateFromRow()
  9.                 if (!empty($list))
  10.                         $this->$linkFieldName = $list[0];
  11.         }
  12. }

The magic happens in the get() function of the Link object. This function calls the getQuery() function and executes it. The getQuery() function by default uses the value of the id field of the current module to find the related module, but it can use a different value by specifying the $bean_filter parameter on the getQuery() function. So, when the account_id field is loaded then getQuery uses the invoice ID and finds the related account ID from the relationship table.

2: Making SugarCRM load grandparent relationships

The reason why SugarCRM cannot load grandparent relations is the implementation of the get() function on the Link object. It calls getQuery without any parameters so it will always use the id of the current bean. If we want to display the account name on an invoice line then we would need to load the account_inv_invoices relation. But getQuery() will be looking for the id of the current bean—an InvoiceLine record, not an Invoice record because we are working on an InvoiceLine now. So, get() will not find the account we are looking for.

To solve this problem we will override the fill_in_link_field() function of the InvoiceLines bean. Our implementation will not call the get() function on the relationship but instead call getQuery() directly so that we can add the $bean_filter parameter. This way we can tell SugarCRM that when it tries to find the account we need, it should search for it using the ID of the invoice instead of the ID of this module (the invoice line). We will tell SugarCRM which field it should use by looking in the vardefs for the bean_filter attribute, which we will add.

2.1: Adding the grandparent fields to the vardefs

We are going to add the account name to the InvoiceLines module. The changes to the vardefs file of the InvoiceLines module are straightforward. As you can see below it is almost identical to the changes we make to the Invoices vardefs when we added the account_name to it. There are just three changes:

  1. The two “link” fields now have a bean_filter parameter. This parameter specifies the field in which the ID is stored that we want to search against. The field that contains the invoice ID is inv_invoicev_invoices_ida.
  2. The save attribute on account_name has been set to false.
  3. The join_name parameter has been added to the account_name field. The value is set to the name of the database table that holds the accounts. SugarCRM needs this in some complex join operations or it will generate faulty SQL queries.
  1. $dictionary["inv_InvoiceLines"]["fields"]["account_name"] = array (
  2.   'name' => 'account_name',
  3.   'type' => 'relate',
  4.   'source' => 'non-db',
  5.   'vname' => 'LBL_ACCOUNT',
  6.   'save' => false,
  7.   'id_name' => 'account_id',
  8.   'link' => 'accounts_inv_invoices',
  9.   'table' => 'accounts',
  10.   'module' => 'Accounts',
  11.   'rname' => 'name',
  12.   'join_name' => 'accounts',
  13. );
  14.  
  15. $dictionary["inv_InvoiceLines"]["fields"]["account_id"] = array (
  16.   'name' => 'account_id',
  17.   'type' => 'link',
  18.   'relationship' => 'accounts_inv_invoices',
  19.   'source' => 'non-db',
  20.   'bean_filter' => 'inv_invoicev_invoices_ida',
  21. );
  22.  
  23. $dictionary["inv_InvoiceLines"]["fields"]["accounts_inv_invoices"] = array (
  24.   'name' => 'accounts_inv_invoices',
  25.   'type' => 'link',
  26.   'relationship' => 'accounts_inv_invoices',
  27.   'source' => 'non-db',
  28.   'bean_filter' => 'inv_invoices_inv_invoicelines',
  29. );

We also need to add a similar join_name parameter to the field that holds the invoice name, or the complex join operations SugarCRM uses when searching will fail.

  1. $dictionary["inv_InvoiceLines"]["fields"]["inv_invoices_inv_invoicelines_name"] = array (
  2.   'name' => 'inv_invoices_inv_invoicelines_name',
  3.   ...
  4.   'join_name' => 'inv_invoices',
  5. );
2.2: Loading related fields with a bean filter

Now SugarCRM needs to use the bean_filter attribute that we added to the link fields above. The upgrade-safe way to do this is by overriding fill_in_link_field() in the InvoiceLines bean class. Below is the function you should add to the bean.

  1. /**
  2.  * Override fill_in_link_field to support a bean_filter attribute on link variables.
  3.  * This can be used to implement grandparent fields
  4.  */
  5. function fill_in_link_field($field)
  6. {
  7.         if ($this->load_relationship($field))
  8.         {
  9.                 $bean_filter = '';
  10.                 $bean_filter_field = $this->field_defs[$field]['bean_filter'];
  11.                 if ($bean_filter_field)
  12.                 {
  13.                         // If the bean_filter is another link, make sure it's loaded
  14.                         if (empty($this->$bean_filter_field) && $this->field_defs[$bean_filter_field]['type'] == 'link')
  15.                                 $this->fill_in_link_field($bean_filter_field);
  16.  
  17.                         // If the bean_filter link cannot be loaded or is empty then this linked field is empty as well
  18.                         if (empty($this->$bean_filter_field)) {
  19.                                 $this->$field = '';
  20.                                 return;
  21.                         }
  22.  
  23.                         $bean_filter = "= '" . $this->$bean_filter_field . "'";
  24.                 }
  25.  
  26.                 $query = $this->$field->getQuery(false, array(), 0, '', false, $bean_filter);
  27.                 $result = $this->$field->_db->query($query, true);
  28.                 $row = $this->$field->_db->fetchByAssoc($result);
  29.                 $this->$field = '' ; // match up with null value in $this->populateFromRow()
  30.                        
  31.                 if (!empty($row))
  32.                         $this->$field = $row['id'];
  33.         }
  34. }

This function first loads the relationship into the field. If the link field has a bean_filter attribute then it loads the value from the field that bean_filter points to. If that field is empty and is of type “link” as well, then it will recursively load that link first. This is exactly what happens with grandparent fields. To get the account name SugarCRM needs to find the account ID. To find the account ID it needs the invoice ID, so it recursively loads the invoice ID first (which is done with a search against the invoice line ID). When the invoice ID has been found it loads the account ID and then the account name.

We need to make one more change to the InvoiceLines bean. The account_name field will not be saved according to the vardefs, but SugarCRM will still try to save the account ID field. When Sugar is saving relationships we need to exclude the account_id field. You can do this by overriding the save_relationship_changes() function.

  1. /**
  2.  * Do not save the grandparent fields
  3.  */
  4. function save_relationship_changes($is_update, $exclude = array())
  5. {
  6.         $exclude = array_merge($exclude, array('account_id'));
  7.         parent::save_relationship_changes($is_update, $exclude);
  8. }
2.3: Add the grandparent fields to the viewdefs

All we need to do now is displaying the newly added fields. This is easy as it works exactly the same was as adding regular related fields to the various viewdefs. See “Fixing one-to-many relationships in SugarCRM 5.1“ and “SugarCRM many-to-one relations to standard modules” for more details. Don’t forget to add the LBL_ACCOUNT label to the InvoiceLines language file as well! Then you can build and install the package. You should now be able to see and use the grandparent fields as in the screenshot below.

As you can see in the screenshot below, you can even add the grandparent fields to the search panels.

3: Pro example and source code

The nice thing about the recursive way we implemented the fill_in_link_field() function is that it doesn’t only support grandparent fields but also great-grandparent fields and higher, as long as each module is connected to the next by a one-to-many relationship. The screenshot below is from a rather more complicated invoicing package that I wrote for my former employer in SugarCRM Pro. It shows a lot of different custom modules. Each account can have one or more websites (this is not the “website” field in the accounts module, but a custom “Websites” module I built). Each website can have one or more support packages. Every package has one or more standard invoice lines (called payment terms). Every month the system creates new invoice lines based on these payment terms. Then an invoice is created for every account and all the invoice lines are added to the invoices, based on an invoice line's great-great grandparent “account” field.

You can download download the finished invoicing package with the grandparent fields already added to the InvoiceLines module. This package has the grandparent fields implemented on the detail view, list view, search panels, dashlets and subpanels. It has been tested with SugarCRM Community Edition 5.1.0b.

Creative Commons Attribution-ShareAlike

Comments

#1 Igor Vitorac

Hi Sander,
I was wondering is it possible to apply similar approach with classic (old) relate fields?
In classic (old) relate fields, there are no relationship file except in vardefs for the bean.
Problem I was facing is that I had a lot of new modules (13-15 new modules) and I had to do complex reporting (ZuckerReports). On many places I have put old relate fields to reduce number of tables in database. I did this only with relationships where I didn't need subpanel. Now I am thinking about introducing this fantastic solution for the grandparents fields, for classic (old) relate fields.

Thanks,
Igor

#2 Sander Marechal (http://www.jejik.com)

I do not think that the above code will work for classic relate fields. The fill_in_link_field() function is never called for classic relate fields.

That said, it should be possible to implement grandparent fields in some other way. You could add another "relate" field.

Short example: A classic relate field is actually two fields in the database. So the InvoiceLines module would get the fields "invoice" (type: "relate") and "invoice_id" (type: "id"). The "id_name" parameter of the "invoice" field points to "inoice_id".

You could add another field that is called "account_id". Make it type "relate" and set "id_name" to "invoice_id". Then, add the "rname" parameter to it and give it a value of "account_id".

The "rname" parameter in a relate relate tells Sugar which field from the parent module it should look up. The "invoice" field above does not have an "rname" parameter so it default to "name". That is, it goes to the Invoice module, looks at the invoice with the ID equal to the field "invoice_id" and takes the "name" field.

You can guess what (should) happen with the account_id field. Sugar goes to the Invoices module, looks at the invoice with ID of "invoice_id" and copies the "account_id" field.

Now you have the account_id! So, add another relate field to the vardefs with the name "account_name" and set its "id_name" to "account_id". You should get the account name.

Of course, there are tons of bugs in SugarCRM so I have no idea if this actually works, but in theory it *should* work. The main question is if SugarCRM will load the fields in the correct order. It should find try to load the account_id first and the account_name second. If it does it the other way around it will not work and you will need to change (or override) core code.

#3 Anonymous Coward

Tried to implement this as you described in Sugar 5.1, but noticed that the function fill_in_link_field($field) is never invoked... Any idea why?

#4 Sander Marechal (http://www.jejik.com)

@Anonymous: How are your relations implemented? Did you build them with Relate fields or with the Relationships editor in the Module Builder? The fill_in_link_field is only used for the latter. When you use Relate fields instead, the field will already contain the correct ID. See comment #2 for how it could work with Relate fields instead of database/table relationships

If you are sure that you are using database/table relationships then you can always use a debugger to find out where it goes wrong. Step through the code until it tries to fill the account_name field. That part should invoke fill_in_link_field() to fill the account_id field before it fills the account_name field.

I highly recommend that you install the PHP xdebug module on your server to help you debugging.

#5 KingoftheRoad

Hi Sander,

Would you mind elaborating more on how to apply this approach for great-grandparent fields and higher? A more detailed guideline will be highly desirable.

Many thanks.

KingoftheRoad.

#6 Sander Marechal (http://www.jejik.com)

Great-grandparent fields work exactly the same. First add the grandparent fields like explained below. Notice that the 'bean_filter' attribute of the grandparent fields point to the parent's ID field. Then simply repeat the above steps for the great-grandparent fields. To make those work correctly, you need to set the 'bean_filter' attribute of the great-grandparent fields to the ID field of the grandparents.

#7 Michiel Boertje (http://distingo.nl)

Hi Sander,

Long time no see. :-)
I started to use Sugar for my new little company (IT Interim Management).
How difficult would it be to implement a simple time recording mechanism to clients/assignments? And could we also track chargeable km's to it? Invoicing itself is not needed; I don't have many invoice to send anyway, but do need to track my time.

#8 Sander Marechal (http://www.jejik.com)

Hi Michiel,

In theory, time tracking doesn't have to be very difficult but I see lots of potential little problems with aggregation and hooks that can cause you headaches when you try to implement it. If you read the footnotes on my various SugarCRM articles then you can see that there are lots of bugs that can wreck havoc on your implementation. I have come across plenty of such problems myself when trying to design the Pro Invoicing module for my former employer.

I suggest you look for an existing module instead. There are a few listed in the Task Management section on SugarForge. SuperTimesheet apparently works with Sugar 5.2 so I suggest you try that one out. Do test on a separate testing instance of Sugar of course and not on your live system.

I haven't found anything that allows you to track travel expenses or kilometers, so that one you'd need to build for yourself. The same warning as with time tracking applies. The basics are quite doable but prepare for gotchas.

If you need any help, let me know. Like you , I also started my own company. Well, started... The Lone Wolves Foundation has been going for over five years now but since this this January I am doing it full time. One of the services that I can offer you is support with SugarCRM customisations.

#9 Sander Marechal (http://www.jejik.com)

PS: An alternative way would be to use an external time and travel tracking application. Something simple, like BaseCamp with a few extra features. Then all you need to do is simply to write a connector to keep the projects (and optionally project tasks) in sync between the two applications.

#10 Michiel Boertje (http://distingo.nl)

Thanks Sander. I must admit that I am very new with Sugar. However, I am using it extensively: all actions and mail go through the system. I am already getting the benefits.

I installed "JustCRMs - Time and Invoicing" on my test environment and I will deploy it in live tomorrow.

Good to know you expanded your own company. Certainly I will call in for help when needed.

#11 sugardev.net (http://sugardev.net)

Please be informed that Sugar has changed the arguments of getQuery() in 5.2.0c.

http://lvangool.homeip.net/trac/repos/changeset/56/latest/data/Link.php

#12 Sander Marechal (http://www.jejik.com)

Thanks. Looking at the code they only added a single parameter to the end of the function call with a default value, so my code above should still work.

PS: Your link gives me a 403 forbidden (view privileges needed), but I was able to view the 5.1 and 5.2c-patch source trees so I compared from there.

#13 sugardev.net (http://sugardev.net)

Excuse me for that, this is the right one:

http://lvangool.homeip.net/trac/repos/browser/latest/data/Link.php

#14 Will Boyce

Hi,

I have run into issues with including grandparent fields in ListViews.

Changing the order of the columns in the list view changes the order of the JOINs in the query, which can result in a broken query (unfortunately this is the case the column order I desire)..

Is this an issue you've come across before with grandparent fields?

#15 Sander Marechal (http://www.jejik.com)

Hi Will. I haven't run into that issue myself. I pretty much always order my columns the way you see in the screenshots above. From left-to-right: name - parent - grandparent - great-grandparent and so on.

Could you perhaps post your SQL queries here? One good query with a regular order and one broken query with the same columns but in a different order?

PS: You can use <pre></pre> tags here to wrap your SQL query in.

#16 Will Boyce

Hi Sander,

I am trying to display the fields in the order: ID, Grandparent, Parent

The broken query looks like:
SELECT  iml_orderitems.id , iml_orderitems.name  , iml_publications.name publication_name, jtl0.publication_id publication_id , iml_issues.name issue_name, jtl1.issue_id issue_id, iml_orderitems.assigned_user_id  FROM iml_orderitems   LEFT JOIN  iml_publications_iml_issues jtl0 ON iml_issues.id=jtl0.issue_id  AND jtl0.deleted=0
 LEFT JOIN  iml_publications iml_publications ON iml_publications.id=jtl0.publication_id AND iml_publications.deleted=0
 AND iml_publications.deleted=0  LEFT JOIN  iml_issues_iml_orderitems jtl1 ON iml_orderitems.id=jtl1.orderitem_id  AND jtl1.deleted=0
 LEFT JOIN  iml_issues iml_issues ON iml_issues.id=jtl1.issue_id AND iml_issues.deleted=0
 AND iml_issues.deleted=0 where iml_orderitems.deleted=0 LIMIT 0,21


The working one:
SELECT  iml_orderitems.id , iml_orderitems.name  , iml_issues.name issue_name, jtl0.issue_id issue_id , iml_publications.name publication_name, jtl1.publication_id publication_id, iml_orderitems.assigned_user_id  FROM iml_orderitems   LEFT JOIN  iml_issues_iml_orderitems jtl0 ON iml_orderitems.id=jtl0.orderitem_id  AND jtl0.deleted=0
 LEFT JOIN  iml_issues iml_issues ON iml_issues.id=jtl0.issue_id AND iml_issues.deleted=0
 AND iml_issues.deleted=0  LEFT JOIN  iml_publications_iml_issues jtl1 ON iml_issues.id=jtl1.issue_id  AND jtl1.deleted=0
 LEFT JOIN  iml_publications iml_publications ON iml_publications.id=jtl1.publication_id AND iml_publications.deleted=0
 AND iml_publications.deleted=0 where iml_orderitems.deleted=0 LIMIT 0,21


The error raised by the broken query is simply "MySQL error 1054: Unknown column 'iml_issues.id' in 'on clause'"

I've left the queries "as-is", as they were generated by Sugar.

#17 Sander Marechal (http://www.jejik.com)

Hi Will, I think I found the problem and a reasonably simple workaround. The problem is a couple of new optimisations in the Sugar code. It only queries for fields that you want on the listview or subpanel. The fields are taken from the listviewdefs and processed in the order they appear in, thus causing the wrong order of the select queries.

The workaround is easy. You need to re-order the columns when you generate the query. First, make sure that in your vardefs the fields are in the right order top-to-bottom. Parent field first, then grandparent field, great-grandparent field, and so on. They probably already are in that order.

Next you need to override the create_new_list_query function in the bean file of your custom module. All this function needs to do is re-order the $filter parameter and call the original function. Order it in the same way as the vardefs are ordered. The function should look something like this. Note: I haven't tested this. I'm writing this off the top off my head.

/**
 * Re-order the filter and pass to the parent function
 */
function create_new_list_query($order_by, $where,$filter=array(),$params=array(), $show_deleted = 0,$join_type='', $return_array = false,$parentbean, $singleSelect = false)
{
    if (!empty($filter)) {
        $filter_new = array();
        $filter_unique = array_unique($filter);

        // Only re-order when the filter was created from listview columns, which
        // are in the form of $filter[$columnname] = true;
        if (sizeof($filter_unique == 1) && array_shift($filter_unique) == true) {
            $filter_keys = array_keys($filter);

            foreach ($this->field_defs as $name => $value) {
                if (in_array(strtolower($name), $filter_keys)) {
                    $filter_new[strtolower($name)] = true;
                }
            }
        }
    }    

    return parent::create_new_list_query($order_by, $where, $filter_new, $params, $show_deleted, $join_type, $return_array, $parentbean, $singleSelect);
}

#18 Will Boyce

Sander!

That works a charm! Thank you very much!

#19 Sander Marechal (http://www.jejik.com)

You're welcome! Did the code above work without any modifications or was there a typo somewhere? I want to add this to the main article.

#20 Chris

Hi Sander,

I have noticed on your websites invoice module you have implemented ‘total price’ and ‘margin’ fields which sum the prices of the related invoice lines in the detail view. I am seeing this as almost the reverse of the grandparent article that you have written. This is a great piece of functionality for your invoice module and is exactly what I would like to achieve. Would it be possible for you to explain how you have gone about achieving this and guide me in the right direction.

Thanks

Chris

#21 Sander Marechal (http://www.jejik.com)

Hi Chris,

I implemented the "total price" and "margin" fields the hard way. They are the sum of the "price" and "margin" fields of all the invoice lines belonging to the invoice.

I initially tried to use calculated fields (like in the custom aging field tutorial) but that didn't work reliably. Then I tried using before/after save methods but I found out than many times these do not get called when they should. As an added problem there are lots of formatting issues with currency fields forcing you to do manual formatting.

I added a "price_total" and "margin" field to the Invoice in the database as regular fields. I override InvoiceLines::save() to find the parent Invoices. Note that you need to find both parents in case you move an InvoiceLine from one Invoice to another. Then I call the update_invoice_totals() method on those Invoices, after saving the InvoiceLine itself.

Because of all kinds of caching issues you can't really trust SugarCRM's relationships and Link fields at this point, so Invoice::update_invoice_totals() queries the database directly to find all the InvoiceLines belonging to it and update the price_total and margin fields.

Since the price_total and margin fields are real database fields instead of calculated fields it's easy to use them in list views, searches and subpanels.

I hope this helps!

#22 Will Boyce (http://willboyce.com)

Sander,

Sorry it took me so long to get back to you! As I recall the code you provided worked verbatim.

#23 Henrik

I have the following setup:

- An Invoice can have multiple Cases (one-to-many)
- An Invoice can have multiple TrackingNumbers (one-to-many)
- A Case can have multiple TrackingNumbers, and a TrackingNumber can have multiple Cases (many-to-many)
- When I create a Case from an Invoice, it is automatically related to the Invoice.

I want to do this:

- As I create the Case, I also want to create relations to one or more of the TrackingNumbers that already are related to the Invoice.

I reckon this could be done with a new kind of multiple select field (or checkboxes), which is populated from the Invoice's TrackingNumbers. The problem is, I'm not able to figure out how to do this. I haven't been able to find a suitable example here or in the wiki.

Can I solve this by using grandparent fields somehow? All help is appreciated :)

#24 Sander Marechal (http://www.jejik.com)

Henkrik, you should have a look at my follow-up article "Enhance you SugarCRM edit views with filters".

It shows a way to limit what you will see in a search popup that you get from the "Select" button on the EditView. You can try to adapt that so that it works on the "Select" button on a subpanel instead.

Create the many-to-many relation between Cases and TrackingNumbers. When you are on the DetailView of a Case then you should see a Subpanel for all related TrackingNumbers. Modify my filter code so that when you click "Select" on the subpanel, you only see the TrackingNumbers that are related to the Invoice that this Case is related to. That is: On a Case, look at the invoice_id field. On the Subpanel "Select", search for all TrackingNumbers which have the same invoice_id field.

I hope this makes sense to you!

#25 Henrik

Hi Sander, thanks for your feedback.

I'm at loss, which file should I edit to override the Select button in a subpanel?

When I grep for the subpanel name I get hits in custom/modules/*/Ext/Layoutdefs/layoutdefs.ext.php, custom/modules/*/Ext/Vardefs/vardefs.ext.php, custom/working/modules/*/relationships.php, custom/metadata/*MetaData.php, custom/Extension/modules/relationships/relationships/*MetaData.php, custom/Extension/modules/relationships/vardefs/*.php, custom/Extension/modules/relationships/layoutdefs/*.php, custom/Extension/modules/*/Ext/Layoutdefs/*.php and more.

I hope you pardon my ignorance, I'm new to Sugar.

#26 Sander Marechal (http://www.jejik.com)

That would be include/generic/SugarWidgets/SugarWidgetSubPanelTopSelectButton.php.

You could either copy and edit it, making a new custom top button for your subpanel, or you could implement filters in a more gereic way such as I did in my article about filters for editviews.

The bottom line is that the search popup that opens should have a couple of extra parameters in the URL that specifies the contents of the fields that you want to search for in the popup.

#27 Henrik

Thanks again. I'm trying to create the filters as you did in your article, but I'm not very successful. I've added more comments about that in that article.

#28 mike

Is there a way to use the grandparent fields for subpanels?

Using your modules as an example I need a subpanel from both invoicing and invoicing lines to accounts

at present with my custom modules I have to have a relationship to accounts from each of my custom module and if the account on the parent module changes I then have to modify the account on the child

#29 Sander Marechal (http://www.jejik.com)

Unfortunately I don't see an easy way of doing that Mike. Sugar SubPanels are pretty much hardcoded to look in the current module and use a relationship to figure out what data to load. The only two ways I see around that are:

1) Create a fully custom SubPanel. This is doable. Several Sugar built-in modules have custom subpanels so you should be able to learn from that.

2) Create a "fake" relationship between Accounts and InvoiceLines so that the standard subpanel can load the correct data. I have no idea if this is even possible without touching core code.

#30 edg

FYI -- The bean_filter parameter is not loaded into the Cache from the vardefs.ext.php file (using 5.2.0f).

Also, no matter what order I load the fields into a subpanel - and even after revising the filter with your script - I continue to get MySQL errors with List Queries. I believe this is because of the order relationship fields are loading even after correcting for optimization.

I've overcome this situation by removing the link parameter in the relate field
adding a logic_hook for process_record that re-calls
$bean->fill_in_relationship_fields().

I'm using a logic_hook rather than fill_in_additional_fields() because ListView.php does not call fill_in_additional_fields unless there are additional fields defined.

#31 Sander Marechal (http://www.jejik.com)

Hi edg. Thanks for your updates and fixes. I already say your post as well on the SugarCRM forums. I wrote the grandparent fields when 5.1 was the latest version of SugarCRM. I could have giessed that something would break in 5.2.

What I really hope is that they roll this functionality into core SugarCRM for the next version (5.5 if I recall correctly).

#32 Sander Marechal (http://www.jejik.com)

From David Morton via e-mail:

Can a grand parent field be made sortable in a ListView?


I don't believe so, although I never tried. If I recall correctly, grandparent fields are added to the results after the main query so they are not taken into consideration when ordering the result set (which uses a MySQL ORDER BY).

#33 Daniel (http://www.dundas.com)

Sander,

This is not directly related to grandparent fields, but I am curious if you ran into this issue and maybe have a workaround.

Lets say I have an Order bean. Order has two relationships to accounts, one billing and one shipping accounts. Now whenever you try to display account names in a listview, Sugar displays both columns using the name of the first account field that appears in the row. For example.

order # billing acc shipping acc
123 aaa aaa
344 bbb bbb
456 ccc ccc

The problem seems to be the retarded way Sugar builds this query, instead of doing two joins, one for first account, second for the other it does only one.

Do you see any possible workarounds (other than rewriting query building code)?

The only solution I have so far is to make one of those fields a relate to field, instead of a proper relationship. But then you are loosing a subpanel on the other end of the relationship.

#34 Sander Marechal (http://www.jejik.com)

First off, you can use relate-to fields. There are plenty of tutorials and howto's in the Sugar forums that explain how you can get your subpanels back when you use straight relate-to fields.

You should also look at the code that you module builder is generating for your relationships. Multiple relations should be no problem (in theory). I think the problem is the retarded way that the module builder generates variable names for the various parts of the relationships. You may get collisions there because both relationships relate to the same module.

#35 Sander Marechal (http://www.jejik.com)

I got an e-mail from Alex Flynn. Unfortunately, when I reply to his e-mail his mailserver refuses to deliver the message. I hope that he reads my reply here. Alex, please contact your e-mail provider. The bounce message says that "550: Inavlid recipient".

i have followed your tutorial to add a grandparent field for a custom
module called dev request, which is a child of cases, i need to be
able to pull the account name from cases, when i add the line
'join_name' => 'ad21_Dev_Request', to cases_ad21_dev_request_name
relate definition in vardefs the detail view on dev request is now
blank screen, anything you could do to point me in the right
direction would be greatly appreciated.


A blank screen is usually a good indicator that your SQL query is generating an error. Turn on debugging and see what SQL query is generating an error in your sugarcrm.log.

If I had to guess, I'd say that the join_name needs to be the same as the table name. In your case that's probably "ad21_dev_request" instead of "ad21_Dev_Request" (i.e. lower case).

#36 Anonymous Coward

Sander:

Thank you for this excellent write-up. I'm new to Sugar, and your tutorials have helped me trememdously.

I ran into a problem when I attempted to add a grandparent field to my own custom "invoices" and "invoice_lines" modules using the instructions in this guide with version 6.02. I added an account_name field to my invoice_lines module, and, although the field displayed in the listview, I was unable to search for invoice_lines by account_name.

A check of the mysql log revealed that the WHERE clause of the search query contained the following condition:

((accounts.name like 'Aim Capital Inc%' ) AND ( slsan_invoice_lines.account_id like '32bddf4c-0bea-1df7-0765-4ca244bd2d04%'))

Since slsan_invoice_lines.account_id is not a real database field, no records were ever returned. Through trial and error I discovered that the solution was to add the following entry to the searchFields array in SearchFields.php:

'account_id'=> array('query_type'=>'default','db_field'=>array('accounts.id')),

This may be the type of thing that experienced Sugar developers take for granted, but I was unable to find any references to this solution on the web. I thought I'd share what I learned for the benefit of anyone who runs into this problem in the future.

#37 Sander Marechal (http://www.jejik.com)

Thanks for your comment Anonymous. Good to know this works with 6.02. Unfortunately you are right about one thing. The SugarCRM core is woefully underdocumented. It's a real PITA.

#38 Donovan

There appears to be a small bug in the file : SugarModules/relationships/vardefs/Accounts.php

It should be $dictionary["Account"] (singular), not $dictionary["Accounts"] (plural). This bug prevents the Invoices subpanel from displaying on the Accounts module.

At least this is the case in Sugar 6.

#39 Biju Paul

Hello Sander,

I'm picking up an old thread because I have a question related this (Invoice and Line items). I have recently installed ver 6.4.2 (my first shot at Sugarcrm). I'm building an Applicant Tracking System, which is similar to your example of Customer-Invoice-Line item. I have Customer-Jobs-candidates (one customer has many jobs, and one job has many applicants).

In your example, I'm assuming you have an Item or Product master file where you have all products listed. Now, my question is, if I click on any one product, I want to see a list of customers who bought the product in a subpanel. Is this possible?

(in my real case, if I view an applicant, I want to see all jobs and all customers the applicant has applied for).

#40 Sander Marechal (http://www.jejik.com)

That's tricky to answer, for two reasons:

1) In your case, jobs and applicants do not have a one-to-many relationship but a many-to-many relationship. I suppose it could be done, but it would require more work that my example. But it shouldn't be too different since the one-to-many relationships created by the module builder are basically many-to-many relationships with an added restriction. They are not true one-to-many relationships.

2) You're using SugarCRM 6.4. These examples were built for 5.1. I have no idea how much changed in between but I think quite a bit (at least I hope so, because the 5.1 internals had quite a few shortcomings). I haven't worked with SugarCRM since I left the job that I created the invoice module for. That is quite a while ago when 5.1 was stable and 5.5 just around the corner.

#41 sugaroutfitters (https://www.sugaroutfitters.com)

I wish I would have found this post awhile ago when I needed to do something similar. Very well done!

With the changes since 6.3 to the Ext framework it should be even easier to do this now in an upgrade-safe manner. I'd love to see someone take a crack at an updated version of this.

#42 Michael Joyner

Hey Jason,

I was thinking about doing it for 6.6. I will need to see how much applies from 5.1 version for the BEAN.

SANDER : YOU ROCK DUDE!!!!!

#43 George

GREAT POST, GREAT TUTORIAL... unique on the web. nothing like this found after lots of searching... Thank you Sander. An update for CE v 6.5+ would be amazing and real value add for custom builders...

Thank you in advance!

Comments have been retired for this article.