How to make a field dynamic based on a criteria from multiple fields in the Accounts Module

Hello!

I've tried using a few formulas to try to accomplish what we need, but haven't found the right one. I'm super new to Sugar so I'm not familiar with how it all works, but we're getting there!

Here's a breakdown of what I'm looking for:

If Type_c (dropdown) is Customer and Last Transaction Date (date) is empty, then Account Status (dropdown) is Not Activated

If Last Transaction Date (date) is within 90days from today, then Account Status (dropdown) is Activated

If Last Transaction Date (date) is after 90days from today, then Account Status (dropdown) is Dormant

If Practice_is_Active_c checkbox is unchecked, then Account Status (dropdown) is Deactivated.

Please let me know how we can achieve this. Thank you!

  • Hi Joey,

    I used date_modified instead of your transaction date because I did not see a database field name given for it, so you will need to edit the following. But, this is how I read your statement in SugarLogic:

    ifElse(and(equal($type_c,"Customer"),equal($date_modified,date(""))),"Not Activated",ifElse(isAfter($date_modified,addDays(today(),-90)),"Activated",ifElse(isAfter(addDays(today(),-90),$date_modified),"Activated","")))

    Here is it broken down to try make it more readable:

    ifElse(
    	and(
    		equal($type_c,"Customer"),
    		equal($date_modified,date(""))
    	),
    	"Not Activated",
    	ifElse(
    		isAfter($date_modified,addDays(today(),-90)),
    		"Activated",
    		ifElse(
    			isAfter(addDays(today(),-90),$date_modified),
    			"Activated",
    			""
    		)
    	)
    )

    I hope this helps!

    Regards,
    Patrick McQueen
    Director, SugarCRM Support

  • Hey Patrick,

    Thanks for looking into this!

    I used the formula above and it gave me an error: "The formula must be of type boolean"

    I also tried this formula to match our custom fields:

    ifElse(
    and(
    equal($account_type,"Customer"),
    equal($last_transaction_date_c,date(""))
    ),
    "Not Activated",
    ifElse(
    isAfter($last_transaction_date_c,addDays(today(),-90)),
    "Activated",
    ifElse(
    isAfter(addDays(today(),-90),$last_transaction_date_c),
    "Dormant",
    ""
    )
    )
    )

    But the error was the same:  "The formula must be of type boolean"  

  • Hi Joey,

    Are you entering the formula in the Calculated Field part of the field editor, or the Dependent Field part, or the Required If part?

    The formula I gave you is for the Calculated Field part of the field editor. If the field type you are attempting to calculate does not have a Calculated Field option, then your version of Sugar does not support calculating the content of that field type. (Calculated Dropdown fields is new in 12.3.0)

    The Dependent Field part is just about whether the field displays in the layout. It is not about populating the field. A formula in this part must be boolean.

    The Required If part is just about whether the field must have a value before the user can save. It is not about populating the field. A formula in this part must be boolean.

    Regards,
    Patrick McQueen
    Director, SugarCRM Support

  • Ah, that makes sense why its not working. I was using the Dependent Formula field to create this, and was wondering why it wasn't working. We currently have version 12.1.0. What do you suggest I can do to make this possible with our current version?

  • Hi Joey,

    I recommend upgrading, but I completely understand that is not always an immediate option.

    SugarBPM or a custom-coded PHP logic hook could be used to automatically set a dropdown type field in 12.1.0. With these, the change will be evident after the save.

    Keep in mind that one awesome feature automatic to calculating a field is that it is locked from user entry. If you have a BPM or logic hook edit the field after the user clicks save, it can potentially confuse users if they set a value and then the automation changes it.

    Regards,
    Patrick McQueen
    Director, SugarCRM Support

  • Hey Patrick, we upgraded to 12.3 in our sandbox and the formula worked!!! Thank you so much!