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

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

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