Calculated field to concatenate Email fields

Hi Freddy Jreisat,

So the opportunity has one contact and you want all the email addresses for that contact to be listed on the Opportunity as a semi-colon separated list.

Do you want this to be a snapshot in time when the Opportunity is created or do you want that list to be updated every time you update either the Contact's email addresses or the Opportunity?

What is the ultimate business purpose for this field?

I'm asking so I might understand how to best achieve your goal without having to store such a list which may be error prone, I used to do that in a custom module back when I first started working with Sugar in v6 but ran into so many issues that I totally scrapped it and started over with a total redesign to meet our needs.

FrancescaS

Francesca Shiekh said:

So the opportunity has one contact and you want all the email addresses for that contact to be listed on the Opportunity as a semi-colon separated list

Precisely.

Francesca Shiekh said:

Do you want this to be a snapshot in time when the Opportunity is created or do you want that list to be updated every time you update either the Contact's email addresses or the Opportunity?

The latter. We would like it to be updated along with the Contact and kept in sync with it.

Francesca Shiekh said:

What is the ultimate business purpose for this field?

To allow sales reps to both easily see as well as copy/paste all of the customer's available email addresses instead of just the primary.

Francesca Shiekh said:

I'm asking so I might understand how to best achieve your goal without having to store such a list which may be error prone, I used to do that in a custom module back when I first started working with Sugar in v6 but ran into so many issues that I totally scrapped it and started over with a total redesign to meet our needs.

Of course. :) Thank you so much.

email1 and email2 were deprecated some time ago, but they still exist in v11.0.3 so I think the issue you are seeing may be something else, perhaps their use is no longer allowed in formulas though they are there.

If you try to edit and save the formula as is, does the system give you an error?

On mine it complains about the "$contacts_opportunities" not being valid and when I try from scratch, it populates the relationship name as "$contacts" and not "$contacts_opportunities"

So it won't accept your version but it will accept:

concat(related($contacts,"email1"),";",related($contacts,"email2"))

But mine has so many customizations I don't even know what's out of the box anymore...

All other options I can think of would require code...

One option would be a before save logic hook on the Opportunity that finds the related Contacts and their email addresses and concatenates the addresses into a text field. However it can get complicated pretty quickly because you would want to trigger the change in multiple cases, at a minimum:

- when a Contact is added/updated on the Opportunity

- when an email address on the contact is added or removed or marked invalid or opted out you would need to find all the Opportunities (or at least the open ones) related to that contact and update them to keep the sales rep from using old information...

it sounds like a bit of a challenge not only to implement but also to maintain.

I have had some "custom compose" actions on the Emails subpanel to compose emails with a combination of addresses from related contacts/users on the Cases module and I am living to regret it to be honest... the big lesson I learned in the past 12 years of working with Sugar is: "just because you can, doesn't mean you should" and trust me you can do a whole lot if you set your mind to it.... Pick your customizations wisely. 

I hope you find a no-code solution for your problem.

Sorry I could not be more help.

email1 and email2 were deprecated some time ago, but they still exist in v11.0.3 so I think the issue you are seeing may be something else, perhaps their use is no longer allowed in formulas though they are there.

If you try to edit and save the formula as is, does the system give you an error?

On mine it complains about the "$contacts_opportunities" not being valid and when I try from scratch, it populates the relationship name as "$contacts" and not "$contacts_opportunities"

So it won't accept your version but it will accept:

concat(related($contacts,"email1"),";",related($contacts,"email2"))

But mine has so many customizations I don't even know what's out of the box anymore...

All other options I can think of would require code...

One option would be a before save logic hook on the Opportunity that finds the related Contacts and their email addresses and concatenates the addresses into a text field. However it can get complicated pretty quickly because you would want to trigger the change in multiple cases, at a minimum:

- when a Contact is added/updated on the Opportunity

- when an email address on the contact is added or removed or marked invalid or opted out you would need to find all the Opportunities (or at least the open ones) related to that contact and update them to keep the sales rep from using old information...

it sounds like a bit of a challenge not only to implement but also to maintain.

I have had some "custom compose" actions on the Emails subpanel to compose emails with a combination of addresses from related contacts/users on the Cases module and I am living to regret it to be honest... the big lesson I learned in the past 12 years of working with Sugar is: "just because you can, doesn't mean you should" and trust me you can do a whole lot if you set your mind to it.... Pick your customizations wisely. 

I hope you find a no-code solution for your problem.

Sorry I could not be more help.

PS. When I look at the vardefs in my cache:

cache/modules/Opportunities/Opportunityvardefs.php

I can see that although the relationship is 'opportunities_contacts' the link name is 'contacts'

    'contacts' =>

    array (

      'name' => 'contacts',

      'type' => 'link',

      'relationship' => 'opportunities_contacts',

      'source' => 'non-db',

      'module' => 'Contacts',

      'bean_name' => 'Contact',

      'rel_fields' =>

      array (

        'contact_role' =>

        array (

          'type' => 'enum',

          'options' => 'opportunity_relationship_type_dom',

        ),

      ),

      'vname' => 'LBL_CONTACTS',

      'populate_list' =>

      array (

        'account_id' => 'account_id',

        'account_name' => 'account_name',

      ),

    ),

I replied to this yesterday but once again my post was sent to auto-moderation and still hasn't been approved. :( Going to type my response again..

If you try to edit and save the formula as is, does the system give you an error?

On mine it complains about the "$contacts_opportunities" not being valid and when I try from scratch, it populates the relationship name as "$contacts" and not "$contacts_opportunities"

No, it accepts it for me. I think this is because you do not have contacts_opportunities custom relationsihp in your instance like we do?    

So it won't accept your version but it will accept:

concat(related($contacts,"email1"),";",related($contacts,"email2"))

I think you are right when you speculate that email2 no longer works in Sugar formulas. Even using your version above (referencing the stock relationship), it does not pull in email2.

Even if I do this as a calculated field, it does not work:

related($contacts_opportunities,"email2")

It returns a blank value on save.

One option would be a before save logic hook on the Opportunity that finds the related Contacts and their email addresses and concatenates the addresses into a text field.

This is what we really need. Or probably an after_save. Do you have an example of a logic hook that would accomplish this?

However it can get complicated pretty quickly because you would want to trigger the change in multiple cases, at a minimum:

- when a Contact is added/updated on the Opportunity

- when an email address on the contact is added or removed or marked invalid or opted out you would need to find  all  the Opportunities (or at least the open ones) related to that contact and update them to keep the sales rep from using old information...

These would not be concerns for us in our instance with our business flow. I can see how it would be problematic for other environments, though. We only ever have one contact related to the opportunity, and typically only 1 active opportunity (we do not use the Accounts module; it is disabled). 

I have had some "custom compose" actions on the Emails subpanel to compose emails with a combination of addresses from related contacts/users on the Cases module and I am living to regret it to be honest...

This is also a desired customization for us. We would love it if we could compose email on Opportunity and have the To: field populate with all the email addresses from the related Contact. That's the main reason we don't compose emails from within sugar.

Could you possibly point me in the direction of how this could be accomplished, as well?

the big lesson I learned in the past 12 years of working with Sugar is: "just because you  can , doesn't mean you  should " and trust me you  can  do a whole lot if you set your mind to it.... Pick your customizations wisely

I agree wholeheartedly with this principle, but this particular customization would not be problematic for us.

Thank you again for your help. :)

I am assuming you're relatively new to Sugar (the moderation will go away after a while ;) so bear with it)

You can find information about logic hooks here:

https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_12.0/Architecture/Logic_Hooks/Module_Hooks/before_save/

The easiest thing for you to do is to create a text field in Opportunities using Studio and add it to your Opportunities Record view. Let's call it all_email_addresses_c and make it read only.

Define your before_save logic hook, as explained in the link above in your Opportunities module's custom directory following the extension framework that sugar provides for you:

in ./custom/Extension/modules/Opportunities/Ext/LogicHooks/<file>.php

you can call your php file whatever makes sense to you.

As you can see in the example on that page the logic hook code will look something like:

    if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

    class before_save_class
    {
        function before_save_method($bean, $event, $arguments)
        {
            //logic
        }
    }

notice the parameters that Sugar "magically" passes to the function.

The $bean is the bean of the Opportunity you just saved (you defined the logic hook in the Opportunities module and therefore the Opportunity is passed before it saves).

If you are not familiar with Sugar's Beans read here:

https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_12.0/Data_Framework/Models/SugarBean/

Now using that $bean you can get the id of the related contact

 https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_12.0/Data_Framework/Models/SugarBean/#Fetching_Related_Record_IDs

you will need the link's name. The easiest way I know to find that is going to the vardefs in the cache. Go to:

your_sugar/cache/modules/Opportunities/Opportunityvardefs.php

Look for 'opportunities_contacts' 

it will look something like:

    'contacts' =>

    array (

      'name' => 'contacts',

      'type' => 'link',

      'relationship' => 'opportunities_contacts',

   < and a bunch of other stuff....>

    ),

Notice that the "name" and the "relationship" are not the same.

Your "link" when getting related beans is the "name" field, not the "relationship_name" field.

Once you have the related $contactBean (which you just got following the instructions in the link above)

You can easily get all the email addresses:

https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_12.0/Architecture/Email_Addresses/#Fetching_Email_Addresses_Using_the_SugarBean

You will want to loop through the email addresses as explained in the "You may also choose to iterate the email address list" section of that document and make sure you don't include any that are marked opt-out or invalid.

Concatenate your result in whichever format you like and assign that to your new field:

$bean->all_email_addresses_c  = <your concatenation results>

Because this is a before_save you do not need to, and should not , save the bean inside of this function, the assignment you just did will be passed on to the save process with the rest of the data for that bean.

I strongly suggest you don't try to make your own "compose" until you are quite comfortable with the more basic ways of customizing sugar. I'm not sure I want to show you what I did... it's convoluted and I wish I had just said "No"...

Maybe once the new Enhancements section is ready you can propose a change to the product. I may eve do so myself ;)

sugarclub.sugarcrm.com/.../improvements-to-how-we-manage-defects-enhancement-requests

Best of luck!

FrancescaS

Francesca, thank you for this helpful and informative response. I will try to implement this. It doesn't seem too bad.