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

Hello,

I am trying to implement a calculated field in the Meetings module which has the sole purpose to show a 'Score' i.e. 1,2,3,4 etc... 

The calculation behind this field derives from the number of employees for an organisation in our Accounts module. 

So in more simpler terms;

If an Account has less than 500 employees, then the 'Score' field in the meetings module has to have a value of '1'. If an Account has between 501-1000 employees, then the 'Score' field in the meetings module has to have a value of '2'.

... and so on.

I hope this makes sense and would appreciate some feedback on this. 

Thanks.

Regards,

Punit Desai.

sugar 9.0.2

calculated field

Parents
  • 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?

  • 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

  • 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

Reply Children