+ Start a Discussion
cbrocbro 

Validation Rule - Don't allow change to Formula Field once value is in place

I have a field that is a formula field that is a concatenation of two other fields.

 

The Custom field is on the Standard Contract Object "Contract.SourceContractID__c" and it's a formula field which concatenates of "Account.Field__c" & "Contract.Number__c" 

 

So if Account.Field__c = 12345

and Contract.Number__c = 00001

 

then, SourceContractID__c = 1234500001.

 

The problem is this:  This SourceContractID__c field is a link to another system.

 

If Account.Field__c CHANGES, then the Formula changes the Contract.SourceContractID__c field to concatentate the new value entered into the Account.Field__c plus the Contract.Number__c (which is a system generated number and cannot be changed.

 

So if Account.Field__c WAS 12345, but changes to 99999

and Contract.Number__c REMAINS 00001

then, Contract.SourceContractID__c CHANGES to 9999900001.

 

This throws off the integration to the other system, because the ID it's looking for has now changed!!!, and screws everything up.  

 

There is a business need to be able to change Account.Field__c, so I can't lock that field down.

 

QUESTION:

Is there a way via a Validation Rule for me to LOCK Contract.SourceContractID__c after it has been initially filled in (even though it is a formula field), so that it cannot be changed if Account.Field__c changes?

 

Thanks in advance for any and all help!

 

Here is the formula field on the Contract Object:

Contract.SourceContractID__c: 

 Account.Field__c & Number__c

 

 

Best Answer chosen by Admin (Salesforce Developers) 
liron169liron169

Hi,

 

Formula field it's not value that store somewhere and can be manipulated.

It will always look on the fields that build the formula.

 

You can instead create text field, and populate it with workflow rule when the record created (by concatenation of Field__c + Number__c).

If the workflow run only in creation, changing either of the fields won't change the concatenation field.

All Answers

liron169liron169

Hi,

 

Formula field it's not value that store somewhere and can be manipulated.

It will always look on the fields that build the formula.

 

You can instead create text field, and populate it with workflow rule when the record created (by concatenation of Field__c + Number__c).

If the workflow run only in creation, changing either of the fields won't change the concatenation field.

This was selected as the best answer
cbrocbro

That's a good idea and it will work, I think.  

 

However, the problem now is that I need to pull from one field (SRC_Contract_ID__c) if a condition is true, but a different field (SrcContractIDTEST) if the condition is false.

 

For example:

 

If SrcContractIDTEST = null, then use SRC_Contract_ID__c, otherwise use Contract__r.SrcContractIDTEST

 

like this:

 

IF(Contract__r.SrcContractIDTEST = null, Contract__r.SRC_Contract_ID__c, Contract__r.SrcContractIDTEST)

 

but it does not seem to work.  Am I doing something wrong?

 

Thanks!

sobroachsobroach

If I were you I'd change SourceContractID__c to a text field, and create a trigger to update SourceContractID__c when Account.Field__c or Contract.Number__c changes.  When the trigger fires I'd re-populate SourceContractID__c.

liron169liron169

The piece of code seems OK.

 

Do you get error?

Where you are using it? inside work flow?

maybe the workflow is not runnig.

cbrocbro

I've settled on basically the first suggestion above.  It's a little more involved - and I will post what I've done here in a little while.  Locking it down and testing today before deploying.  Need to make sure nothing goes wrong with integration.  I think we should be fine.  (also, the workflow was not running before, duh).  So the code above would (and did work), but I've settled on another simpler solution.