date of last meeting field

Hi Amy,

You can achieve this functionality by creating a few calculated fields via Studio. 

Meetings Module

1. Go to Admin > Studio > Meetings > Fields > Add Field
2. Create a custom date field (e.g. meeting_held_date_c) with the following formula in the 'Calculated Value' section: 
   

   date(ifElse(equal($status,"Held"),$date_start,""))

   This formula checks to see if the status is held, and if so, inherits the start date of the meeting.

3. (Optional) Add the field to the Meetings record view layout

Accounts Module

1. Go to Admin > Studio > Accounts > Fields > Add Field
2. Create a custom date field with the following formula in the 'Calculated Value' section:
   

   maxRelatedDate($meetings,"meeting_held_date_c")

   This formula reviews all related meetings to the account to return the highest date value in the previously created custom field.

3. (Optional) Add the field to the Accounts record view layout

Once both fields are created, any meetings saved will populate the custom field if their status is held. Similarly, any accounts saved will populate the highest related date from the custom field. 

Any pre-existing records will be empty. To remedy that issue, you will need to perform 1 of the following operations on the existing leads:

1. Individually save the necessary meetings and then the related accounts
2. Run a 'Recalculate Values' operation from the Meetings list view on the desired records and then again on the Accounts list view

I hope this helps!

Chris...thanks so very much for your help!!!!!!!

Please note that due to bug #76484 there's no 'Recalculate Values' option in the Meetings list view mass action dropdown.
As a work-around you can do a Mass Update and update the Tags with an empty value (make sure to select the "Append Tag(s)" checkbox in order to not whipe out existing Tags).

hi Chris Raffle
Is there a way to only use one custom field on Accounts module by using

maxRelatedDate($meetings,"date_end")

and include the

ifElse(equal($status,"Held")

within that as well?

hi Chris Raffle
Do you know of how to get the opposite, in other words instead of
maxRelatedDate get minRelatedDate

an example would be to calculate the earliest Opportunity Closed Won date for each customer.

Hi ,

There is a Sugar Logic function that achieves this use case called rollupConditionalMinDate. I'm not sure on when this was introduced as it doesn't appear to be in the documentation for Sugar 12. I confirmed it is in Sugar 11; it may be available in earlier releases. On the Accounts module, I created a date field with the following formula:

rollupConditionalMinDate($opportunities,"date_closed",createList("sales_status"),createList("Closed Won"))

After adding the field, I then recalculated values on the account records and confirmed that the earliest closed date of an account's won opportunities populates as expected. 

hi Chris Raffle
Thank you very much for that, I had totally missed that rollupConditionalMinDate existed, as I expected minRelatedDate to exist.
Conversely, rollupConditionalMaxDate doesn't exist either 

In case it helps I can also confirm for anyone else wondering, rollupConditionalMinDate exists in v10 onwards.

Hey Chris,

Is there a way to find the related record with the highest date value and then copy fields from that record? For example, I would like to copy the Sales Stage of the most recently created related Opportunity.

Thanks!

Hi Mustafa,

I don't think there is a way you could achieve this with Sugar Logic. Sugar Logic functions do not have the means of comparing one related record to another (i.e. give me the most recent created record). 

This specific use case can be achieved in SugarBPM though. Here is how I would build the process definitions:

1. Create a dropdown field (e.g. Most Recent Opportunity) with the following dropdown options
   Note: For my examples, I am going to use the Opportunity Type field but you will want to use your own custom field to ensure it is isolated for your use case
   1. blank
   2. Yes
   3. No
2. Create a SugarBPM process definition on the Opportunities module
   
3. The definition has two start events. The first start event should be set to trigger on new records only:
   
4. The action after that start event updates all the opportunities related to the parent account. It will set your custom dropdown field value to 'No'
   
5. The next field action sets the value of your custom dropdown for the current opportunity to 'Yes'
6. The last action then sets the field on the account equal to the opportunity sales stage value
   
   Note: For copying the sales stage value, you will need to manually enter the text shown above because the field is not available to select from the list of field variables: {::Opportunities::sales_stage::}
7. Then, the second start event will be set to trigger on all updates where the opportunity's custom dropdown value is set to 'Yes'
   
   This start event will feed directly into the last action to update the parent account.

This definition would ensure that only the most recently created opportunity will update the account record with its sales stage value. I caution that this is a very specific use case though. If you had to handle a scenario where you had to make comparisons between the related records (e.g. copy the sales stage of the opportunity with the nearest expected close date), then this solution would not suffice. Much like Sugar Logic, SugarBPM is not equipped to handle comparisons between related records. Your best solution for those more complex comparisons would be a custom-developed logic hook.

Chris

Hey Chris - Thank you so much for your help! That's a perfect solution for my use case and it's working well after configuring on my end. You rock!