Related fields in SugarLogic formulas

bsoremsugar —  November 17, 2011 — 16 Comments

One of the major features in SugarCRM 6.3 is the ability to use relationships in SugarLogic formulas. This means that you will finally be able to use related data in calculations as well as dependent fields.

The functions available are:

Name Description Example
related($link, String $rel_field) Returns the value of the field in the related module. If the link is to many, the first record’s value is returned related($accounts, “name”) = The name field from a related Account record
count($link) Returns a count of the records related via the link to the current record. Works on one or many links count($cases) = The number of cases related the current record
rollupSum($link, String $rel_field) Returns the sum of the values of a field in the records related by a given link. rollupSum($opportunities, “amount”)  = The total amount of all the Opportunities related to the current record.
rollupMin($link, String $rel_field) Returns the lowest value of the number field provided from all of the records related by a given link. rollupMin($opportunities, “amount”)  = The lowest amount of all the Opportunities related to the current record.
rollupMax($link, String $rel_field) Returns the highest value of a given number field from the records related by a given link. rollupMax($opportunities, “amount”)  = The highest amount of all the Opportunities related to the current record.
rollupAve($link, String $rel_field) Returns the average value of a given number field across records related by a given link. rollupAve($opportunities, “amount”)  = The average amount of all the Opportunities related to the current record.

Links:

As you can see, all these functions take a new parameter type called link. In Sugar, related records are accessed through link fields which represent one side of a relationship. For instance, the Contacts-Accounts relationship has two links. The first is the “accounts” field in Contacts that represents the single Account record related to a Contact. In Accounts, there is a “contacts” field that is used to access the list of Contact record related to an Account by that relationship. So to get a count of the Contacts related to an Account, you would use the formula count($contacts) from the Accounts module.

For most of the out of box relationships, the naming convention for links is just the lowercase name of the related module. However, this is not the case for all relationships (Member accounts is an example) and is definitely not the case for custom relationships created from studio and module builder. For these cases, the easiest way to find the name of the link fields is to open the vardef file for the module you are working on (cache/modules/{Module_Name}/{module}vardefs.php) and find the link field that matches the relationship your looking for.

Examples

So lets look at an example of a dependent field that uses related data. If we want to have a field in Contacts (partner role) that shows when a Contact is related to an Account of type “Partner”, first we would need to create the field in Contacts through studio. A simple text field will do for now. We would then check the Dependent option and enter the following formula:

equal(related($accounts, “account_type”), “Partner”)


A rollup is just as easy as the related field example above. In order to get the total of all the Opportunities related to an Account we would just need to add a new currency field to Accounts with the formula :
related($opportunities, “amount”) .
If you have an Account with opportunities in multiple currencies, you might want to use “amount_usdollar”  to avoid currency conversion issues. This works great as long as you are interested in seeing the value of all the opportunites, won or lost. If we wanted to say get the sum of only won opportunities, we can do this by creating a new custom calculated field in opportunities called “won_value_c” with a formula like:
ifElse(equal($sales_stage, “Closed Won”), $amount, 0).
At that point, we can just do related($opportunities, “won_value_c”)  and get just the won opportunity values on the Account.

16 responses to Related fields in SugarLogic formulas

  1. 

    Is there the ability to use data from a related modules related module? perhaps in a nested format?
    ie: related($contacts,related($custom1,$customfield))

  2. 

    Hi I have tried something similar but get an error with cannot use calculated field. Any idea why that is and what I can do to mitigate this?

    • 

       Which version of Sugar are you using?

      • 

        Pro version 6.4.1

        • 

           Hmm, this is an odd one. Could you file a bug for this at http://bugs.sugarcrm.com?

          • 

            Sure, do you think it would help if I created a new field? The only reason I am asking is because this is something my business owners are itching to have

            • 

               It couldn’t hurt. Let us know how that goes.

              • 

                Nope that did not work, I will place my hope in a bug fix :)

                • 

                  Did you figure out what was wrong? I have the same problem, thank you.

                  • 

                    Somehow I figure out a pass by. It depends what field you are using by for my case, I just used an original Field and change it to the type I wanted with formula in. After in another module, I called back the Original Field back and I add the total of the Field and it worked out fine. Even I put the same criteria and forumla on the custom field, it didn’t work out. I made sure the Data Type match and all, it won’t calculate. I hope it does help for people that will come across that situation.

                    • 
                      Dmytro Voloshyn March 16, 2013 at 4:59 pm

                      can you explain a little bit more about how did you solved it?
                      thanks in advance

                    • 

                      I had to change a total calculation inside Quote module. So I made Total2, I added some rules to make it what I wanted

                      For my case that what I wrote:

                      ifElse(and(equal($quote_stage,”Ready”),equal($payment_type_dv_c,”Sample”)),$total,”0″)

                      Afterwards, I could use other Module to call back the field of Quote module to get back what I wanted.

                      I hope it helped.

  3. 

    Hi David, John,

    The last option is great, but in 6.5. gives me: 
    related: Cannot use calculated field won_value_c 

    Adjusting the formula to: 
    rollupSum($opportunities, “won_value_c”)

    … gives the same error:
    rollupSum: Cannot use calculated field won_value_c 

    Any idea?

    Kind regards, Arjen

    • 
      Dmytro Voloshyn March 16, 2013 at 4:58 pm

      the same problem “Cannot use calculated field “. Looks like sugar doesnot enable to do calculations over calculated fields

  4. 

    Added a custom field at the user level to enter a ‘maximum discount allowed’ decimal number. When I create a field in another module and try to tie back to the assigned user when a new record is being created, the value comes back empty.

  5. 

    i am setting this exact scenario up.

    I have:

    total_won_c field on accounts

    won_amount_c field on opportunities

    The formula for the won_amount_c is:
    ifElse(
    equal($sales_stage,”Closed Won”),
    $amount,
    0
    )

    The formula for total_won_c is:

    rollupSum($opportunities,”won_amount_c”)

    You HAVE to:
    setup won_amount_c in opportunities and save WITHOUT a formula
    Setup total_won_c in accounts with the formula
    go BACK to won_amount_c and enter the formula

    this gets around the stupid “can’t run a conditional field on a conditional field”

    Now you must edit the opportunity to run that calculation, then edit the account to run that calculation. This sucks but does work.

    We need a better solution where calculated fields run automatically.

    All I want in the end is to be able to generate a contact list of every account that is a customer, WITHOUT having to manually make accounts of type Customer in the dropdown.

    I should mention that this is a LOT easier in Salesforce.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s