How to write sugar logic for assigning value to list items

Our company has determined there are 6 levels of contacts we need to make within each customer. We'd like a way to easily see which levels have been reached (via Contact creation). In preparation a dropdown field was added to the Contacts Module with each of the 6 categories of people (Contact Purpose). For viewing how many levels of people we've reached and as opposed to taking up multiple columns on the Accounts grid I would like just 1 field to showcase the status.

My thought was to create a 7-digit field where each place value would represent one of our 6 contact levels (there would not be more than 9 people in each level). Each of the Contact Purpose categories would be assigned a value which would update the 7-digit value shown on the Accounts grid.

Contact Purpose Values:
Accounting Decisions = 10
Damage Decisions = 100
Specialty Decisions = 1,000
Claims Decisions = 10,000
Contract Management = 100,000
Executive Level = 1,000,000

If the Account had 2 Executive & 1 Contract person & 0 Claims persons & 2 Specialty persons & 0 Damage & 5 Accounting.... then the field would total 2,102,050.
If the account had 0 Executive & 0 Contract person & 5 Claims persons & 7 Specialty persons & 2 Damage & 0 Accounting.... then the field would total 57,200

We could keep the 1's value open for a future need.

How could this CASE statement or other logic be written into Sugar... or is there a more mainstream idea for determining levels of contacts? We already have a field that counts the number of contacts and shows the value on the Accounts grid. This is unhelpful as there can be 100's or 1000's of contacts in an account with the vast majority not falling into a main category (like entry-level support people).   

Any help is appreciated.

  • I suppose those Contacts, whose dropdown field "Contact Purpose", are related to Accounts, so there is the solution:

    • Make the dropdown values (which is saved into database) exactly the values you described (as regular number values).
    • Create an Integer field on Contacts and make it a calculated field whose formula is : ifElse(equal($contact_purpose_c,""),0,number($contact_purpose_c))
    • Create an Interger field on Accounts and make it a calculated field whose formula is : rollupSum($contacts, "contact_purpose_num_c")

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • Andre,

    Thank you very much for the input - my issue is solved. I did find out that a MULTISELECT does not appear in calculated field so I needed to transfer all the Contact Purposes to a new Dropdown type field called Contact Guide... a minor inconvenience using Mass Update. 

    After creating the calculated field I needed to conduct a Recalculate Values on my Contacts so the new Contact_Guide_Num field would populate then do the same on my Accounts so the Contact_Guide value would sum. 

    Both your formulas worked as written. Here was the final formula for Contact_Guide_Num (using the Drop Down Item Name, not Display Label):

    ifElse(equal($contact_guide_c,"Full_Account"),"1000000",
    ifElse(equal($contact_guide_c,"Contract_Pricing"),"100000",
    ifElse(equal($contact_guide_c,"ACDM"),"10000",
    ifElse(equal($contact_guide_c,"SCDM"),"1000",
    ifElse(equal($contact_guide_c,"MD"),"100",
    ifElse(equal($contact_guide_c,"Accounting"),"10","0"))))))

    Here is how the Accounts Module grid appears. Showing the Contact Count in one column with the Contact Guide ranking in the other. We left the lower level contact out of the ranking system as we were only looking to tag the top 6 positions within our accounts. 

    Thanks again.