How to create calculated field by using a field from another module.

Hi Punit,

To give the best guidance, can you clarify how you are tracking the number of employees on the account record? Are you using the stock 'Employees' field or a custom field? If you are using a custom field, what field type (e.g. text, integer, etc.) is associated with the field? The stock 'Employees' field is a text field so there is no guarantee that the value in the field will be numeric. If you are using the 'Employees' field or a custom text field, what score would you want associated if the field contains non-numeric values?

The default sugar field was a text field which I ended up changing recently. It is now an integer field. There is no way the field can contain regular characters now. I made sure to change that before starting this whole process. 

Hi Punit,

You can achieve this scoring in the Meetings module with the following calculation:

ifElse(
   not(
      greaterThan(
         related($accounts,"employees"), 500
      )
   ),
   1,
   ifElse(
      not(
         greaterThan(
            related($accounts,"employees"), 1000
         )
      ),
      2,
      ifElse(
         not(
            greaterThan(
               related($accounts,"employees"), 2000
            )
         ),
         3,
         4
      )
   )
)

Using not() with greaterThan() creates the same effect as a "less than or equal to" formula. If the employee number is greater than the number evaluated, the false condition of ifElse() evaluates the upper limit of the next scoring tier. This formula also assumes you are evaluating against the native Accounts <-> Meetings relationship and would only function if the meeting record is related to the account through the stock flex relate field. If you have a custom relationship between the 2 modules to always capture the related account, then modify the $accounts variable accordingly. 

I hope this helps!

Chris

Much appreciated there Chris. Works like a charm!  

Thank you! 

Sorry to drag this on! But lets say, i have another field called 'Contact Quality' which I want to add in to this formula which is in the Contacts module. The field consists of 5 options in a drop down list. 

In order for the score to be determined, I would like the employees field value range to be met and the specific option from the drop down list in the 'Contact Quality' to determine what the score would be. i.e.

if an account has less than (Employee field) 500 employees and the (Contact quality drop down list) Option 1 was chosen, then assign a score of 1. 

if an account has less than (Employee field) 5000 employees and the (Contact quality drop down list) Option 3 was chosen, then assign a score of 10. 

And so on... 

Any thoughts on how this can be implemented within the score field? Chris Raffle

Hi Punit,

My assumptions on your use case are the following:

• The account is related via the flex relate field on the meeting
• The contact is associated as an invitee and not via a custom relationship
• You do not intend for more than 1 contact to be associated with a meeting

Are those accurate? 

1 and 2 are correct but 3 is not. In our particular case, we can have more than 1 invitee per call. Chris Raffle

Hi Punit,

Thank you for clarifying. What is the expected behavior if 2 or more contacts with different 'contact quality' values are invited to a meeting?

Then we would take the contact with the highest quality value. i.e. if A & B were contacts that were invited to the meeting, and B had a lower contact quality status than A, then take the value of A Chris Raffle

Hi Punit,

One more inquiry before drafting a solution is regarding the contact quality dropdown field. How are the key/value pairs designed in the dropdown list? Is it one of the following or something else?

1. Key is numeric, display value is numeric (e.g. 1 => 1, 5 => 5, 10 => 10) - Higher key value correlates higher quality.
2. Key is numeric, display value is alphabetic (e.g. 1 => Low, 5 => Medium, 10 => High) - Higher key value correlates higher quality.
3. Key is alphabetic, display value is alphabetic (e.g. Low => Low, Medium => Medium, High => High) - Definition correlates higher quality

Thanks!