+ Start a Discussion
zen_njzen_nj 

Is there a way to selectively disable a formula field for when say Opportunity is Closed?

Hi

 

We have a formula field (text) in our Opportunity called Account Manager which essentially is just a formula field that is referencing the same-named field in the Account entry: Opportunity.Account_Manager__c=Account.Account_Manager__c

This allows us to show our users who is the Account Manager associated with this Opportunity.
This works great for when the opportunity is still Open since we want to know who is the Account Manager associated with this Opportunity while it's still being worked on.

 

But once the opportunity is closed, we don't want the value in the Opportunity.Account_Manager__c to be updated any more with subsequent changes to the Account.Account_Manger__c since when we run reports later, we want to make sure we show who was in fact the Account Manager associated with this opportunity during its life cycle and not have a situation where after the opportunity has closed, the Account went through an ownership change and so the Account.Account_Manager__c now has a new value and this then gets automatically pushed into our closed opportunity's Opportunity.Account_Manager__c field (even though this person is not at all involved with the opportunity)

 

I tried to do something in the formula like this where if the Opportunity is closed, then the formula field won't get changed:

Opportunity.Account_Manager__c=If (IsClosed, Account.Account_Manager__c, Account_Manager__c)

but i get the error message "Formula cannot use another formula field that directly or indirectly refers to itself"

 

Is there a way to selectively disable a formula field from being updated?

Or can someone think of a way to do this elegantly?

 

I thought of using workflow rules and instead of Opportunity.Account_Manager__c being a formula field, just a text field. And then the workflow will automatically update Opportunities each time it is being updated (i.e. field update to always get the Account.Account_Manager__c value for as long as Opportunity is Open), but this won't work if someone just updated the Account entry and the opportunity itself doesn't get updated.

 

I thought of having a custom button from account page labeled "New Opportunity" so that when someone clicks on that, it will create a new opportunity with the Account Manager field pre-populated in Opportunity. But again, this doesn't allow changes in Account.Account_Manager__c to be automatically reflected in the Opportunity.Account_Manager__c field.

I think I can do this using apex code/trigger but I want to avoid doing that if there is another way to do this.

 

Best Answer chosen by Admin (Salesforce Developers) 
zen_njzen_nj

I think I figure out a way to do this without relying on an apex trigger. It's a combination of using 1 formula field, 1 regular/text field, and workflow rule.

 

So essentially, in Opportunity, I have 2 fields:

 

1. Open Account Manager (text field) - this field will only get updated by workflow rule when the Opportunity changes from Open to Close. When Opportunity is closed, the workflow rule will issue a field update on Open Account Manager and set the value to  be Account.Account_Manager__c

 

2. Account Manager (formula text field) that returns following  value:

   If (IsClosed, Open_Account_Manager__c, Account.Account_Manager__c)

 

This way, whenever an opportunity is first created off an Account, since it's not Closed, the Account Manager field will always point to whatever is the value for Account.Account_Manager__c value.

And so if someone is making changes in the Account entry and updating the Account_Manager__c value there, the Opportunity.Account_Manager__c value is automatically reflected.

 

And now when we decide to close out the opportunity, the workflow rule (that will fires whenever opportunity is changed from open to closed) will set Opportunity.Open_Account_Manager__c to be Account.Account_Manager__c and so essentially  the Opportunity.Open_Account_Manager__c field will store what was the value of Account.Account_Manager__c at the time the opportunity is closed.

 

And when the Opportunity is closed,

Opportunity.Account_Manager__c will now always refer to the  value of Open_Account_Manager__c, which will no longer get updated any more since opportunity is closed.   And so even if someone later changes the Account.Account_Manager__c value, the value for Opprotunity.Account_Manager__c remains unchanged since it will always refer to Open_Account_Manager__c as long as Opportunity remains close.

 

I tested it out with various possible scenarios and it seem to be working!

 

 

 

All Answers

Shannon HaleShannon Hale

I don't think you're going to be able to do this without using Apex.

 

Formula fields are, by nature, dynamic and will update to reflect the latest data whenever the record is viewed. And as you point out, the workflow rule will work only when the opportunity is updated, not when the account is updated. You won't be able to create a workflow rule on the account to update children, so you'll need a trigger.

zen_njzen_nj

I think I figure out a way to do this without relying on an apex trigger. It's a combination of using 1 formula field, 1 regular/text field, and workflow rule.

 

So essentially, in Opportunity, I have 2 fields:

 

1. Open Account Manager (text field) - this field will only get updated by workflow rule when the Opportunity changes from Open to Close. When Opportunity is closed, the workflow rule will issue a field update on Open Account Manager and set the value to  be Account.Account_Manager__c

 

2. Account Manager (formula text field) that returns following  value:

   If (IsClosed, Open_Account_Manager__c, Account.Account_Manager__c)

 

This way, whenever an opportunity is first created off an Account, since it's not Closed, the Account Manager field will always point to whatever is the value for Account.Account_Manager__c value.

And so if someone is making changes in the Account entry and updating the Account_Manager__c value there, the Opportunity.Account_Manager__c value is automatically reflected.

 

And now when we decide to close out the opportunity, the workflow rule (that will fires whenever opportunity is changed from open to closed) will set Opportunity.Open_Account_Manager__c to be Account.Account_Manager__c and so essentially  the Opportunity.Open_Account_Manager__c field will store what was the value of Account.Account_Manager__c at the time the opportunity is closed.

 

And when the Opportunity is closed,

Opportunity.Account_Manager__c will now always refer to the  value of Open_Account_Manager__c, which will no longer get updated any more since opportunity is closed.   And so even if someone later changes the Account.Account_Manager__c value, the value for Opprotunity.Account_Manager__c remains unchanged since it will always refer to Open_Account_Manager__c as long as Opportunity remains close.

 

I tested it out with various possible scenarios and it seem to be working!

 

 

 

This was selected as the best answer
Shannon HaleShannon Hale

Nicely done!