Details about this thread
  • State Verified Answer
  • Replies 6 replies
  • Subscribers 293 subscribers
  • Views 1089 views
  • Users 0 members are here
Related content from around SugarClub

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

Joey Maguliman

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!

Parents
  • 0

    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

Reply
  • 0

    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

Children
  • 0 in reply to Patrick McQueen

    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"