[SalesForce] Validation Rule Issue – user lookup

I'm trying to create a validation rule.

There is a user Field(lookup to user), say(test__c) on object X.
I would like to ensure if value is this field is changed, then role of new user should remain same as of existing user.

I thought I'll use isChanged or PriorValue functions. for e.g

Ischanged(test__r.UserRoleID) or PriorValue(test__r.userRoleID) <>
PriorValue(test__r.UserRoleID)

But issue is both these functions can't work on reference(look up) fields.

I made it work by creating New Formula field having RoleId and applying these functions on that. It is working fine.

My issue is , can it be done without creating new Formula field?. Just for validation Rule, I don't want to create new (formula) Field. We already have 100s of field on same object.

Best Answer

There's a conceptual issue here

Validation rules fire upon DML of an object (insert or update). The VR has access to the before and after values of the object (update use case). But lookup fields are in different SObjects and those aren't undergoing DML when the VR on your target object is executing. Hence, PRIORVALUE and ISCHANGED on lookup fields won't get you what you want.

The formula fields on the target object do get evaluated on the before and after values.

That said, when DML on an object executes, the only thing that can possibly change with regards to the lookup objects is the foreign key itself. So, perhaps your application can detect changes in the foreign key (lookup field) and use that for your checks.

Related Topic