Lone Wolves

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.

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.

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.

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.

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.

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.

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.