Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 313 subscribers
  • Views 1893 views
  • Users 0 members are here
Related content from around SugarClub

calculated fields - return specific value

Cédric Raoul

Hi all,

I have an odd request from my sales team and I'm not sure I can do  that. We do segment our accounts (companies)  per status. We have a dropdown that shows several value. 

We would like to do the same for our contacts in order to target our audience in a more effective way.

We are a small group with 3 different entities that we can call A, B and C

For example : 

  • John (contact) works for Company 1
  • Company 1 has the following status : Client C and Prospect A (multiple value from dropdown)

In the contact module, under John, we would like to have a calculated field that would do the following : 

  • check the value returned in Company 1 Status dropdown
  • As we have 2 values (Client C and Prospect A), Client takes the lead
  • Therefore, the calculated field should return "Client" under Max

If the value in Company x is either client or prospect, it should then return "none" in the contacts module. 

Basically, we are segmenting are contacts with 3 values : client, prospect, none

We need it to appear in the contacts module but calculated from the status field that appears in the companies module

Is there a way to have a formula that would check the value entered in a field, if it sees "client x" than it returns client, if it sees "prospect x" only, then it returns "prospect" and if it sees none of the 2 it simply returns "none" ? 

Sounds weird I know but I'm gladly looking forward to hearing from you guys. 

I thought about using the 

ifElse(equal(getDropdownValue but not sure how to implement it. 

  • 0

    Hi Cédric,

    ifElse(
    contains(
    getDropdownValue(
    "your_status_list_name",related("insert your related status field here")),"client"),"Client",
    ifElse(

    contains(

    getDropdownValue(

    "your_status_list_name",related("insert your related status field here")),"prospect"),"Prospect","none"))

    Formular breakdown:

    Get the field from your account module and check whether it contains the string "client", if it does write "Client"

    If it does not check whether your field contains "prospect", if so write "prospect", if not write "none".

    Hope this helps!

    EDIT: Ah I see the trouble now, there is no way of accessing a multiselection field in Studio unfortunately.

  • 0 in reply to Robert Schrepfer

    Hi Robert,

    That was fast ! Thanks a lot !

    I'll try your suggestion on a single selection list to see if that works and will get back to you (it's never lost). However, you are indeed right, I don't think it is currently possible to access a multiselection field. 

    Anyway, I'll still try and will do my best to find a workaround. 

    Thank you once a again for you quick reply, it is much appreciated. 

  • 0 in reply to Cédric Raoul

    Just tested it, it needed a little tweeking. Added "toString" when reading the dropdownValue, now it works.

    ifElse(
    contains(

    toString(
    getDropdownValue(
    "your_status_list_name",related("insert your related status field here"))),"client"),"Client",
    ifElse(

    contains(

    toString(

    getDropdownValue(

    "your_status_list_name",related("insert your related status field here"))),"prospect"),"Prospect","none"))

  • 0 in reply to Robert Schrepfer

    Hi Robert,

    Thanks a bunch for the time your spending on this ! 

    Adding "toString" did help returning some results however, for some reasons, it only displays "none". I tried several times but I can't make either "Client" or "Prospect" appear. However, "none" seems to be working all the time

    I think we're fairly close to a solution. I tried different parameters but so far, no luck. If you have any idea, feel free to pitch in. In the meantime, I'll try a few more combination. 

    Cheers ! 

  • 0 in reply to Cédric Raoul

    I found a solution ! Well, at least it works for me. We also changed some parameters in the meantime : 

    ifElse(
    contains(related($accounts,"statut_client_c"),"_CL"),"Client",
    ifElse(
    contains(related($accounts,"statut_client_c"),"_AC"),"Client",
    ifElse(
    contains(related($accounts,"statut_client_c"),"prescripteur"),"Prescripteur","Prospect")))

    Robert, you clearly brought us on the right path ! So thanks a lot for your help. 

    for some reasons, the getdropdownvalue never worked. Well, there's always a workaround.