I have a simple validation rule on my Opportunity object:

IF(RecordTypeId = "012A0000000zPJq",
    NOT(TRIM(Market_Project__r.Venue_Details__c) = TRIM(Venue_Location__c)), 

Put simply: if the record is of a specific record type, ensure these two fields are equal in content (ignoring line breaks, extra spacing, etc).

The behavior I am experiencing is that the rule works perfectly if the content is copy/pasted from the edit box (after double clicking the value), but will trigger the validation rule if the value is copied from the detail page.

Any ideas what I could do to deter this?

enter image description here

  • Just want to say, avoid using hard id's like this. If the validation rule goes from one sandbox to another, or to production, the id would become invalid. Use the record type name instead. – Drew Kennedy Nov 28 '17 at 18:53
  • Yes of course. This was a quick "management needs something done now" kind of thing. It works but I am now just trying to get around the "why does it work this way but not that way" question. Will get around to future-proofing eventually (sounds familiar) – JaredT Nov 28 '17 at 18:58
  • 1
    Why not just copy the field value (e.g. Process Builder) instead of making the user copy-paste text? That's terribly non-user-friendly. – sfdcfox Nov 28 '17 at 19:13
  • I completely agree. One battle at a time. Unfortunately when this record is created an email alert goes out to an external user; using a process/workflow I would not be able to guarantee the field is populated before the email alert is sent. – JaredT Nov 28 '17 at 19:18

TRIM only covers spaces before the first non-white-space character and after the last non-white-space character, not the ones in the middle. As such, TRIM is rarely ever suitable for this purpose, because the database automatically TRIMs input in most field types before it is saved. Instead, you probably want to use something like the following:

AND($RecordType.Name='My record type name',
  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Market_Project__r.Venue_Details__c, ' ',''),'\r',''),'\n',''),'\t','') <>
  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Venue_Location__c, ' ',''),'\r',''),'\n',''),'\t',''))
  • This rule does not appear to work properly when the inputs do not match – JaredT Nov 28 '17 at 19:17
  • @JaredT Make sure 'My record type name' matches. This is how one future-proofs against using Record Type Id values. – sfdcfox Nov 28 '17 at 19:20
  • Ah ha, I had a typo in my recordtype name. Thanks! – JaredT Nov 28 '17 at 19:23

You could stop using a text field and start using a formula as well. That way you can guarantee the fields always match, then there'd be no reason to even have a validation rule to begin with. That does however, depend on what values you expect the field to have when the record type is different.

Alternatively, throw together a process builder to enforce the rule. If the record type is of the specified Id, update the value of the field to match the value in the desired record. Again, this would force the field to have the correct value meaning you don't need a validation rule (and you're employees can save time by not doing 1 extra copy/paste)


In response to your comment about not being able to use a PB. You actually can use a PB for this. Combine both your field update and your email alert into a single PB. Put your field update in the first branch. Use the record type as the criteria for executing the update. Then, you should see a "Stop" to the right of your field update. Click on that and select "Evaluate Next Criteria". Then, in the second branch, set no criteria and put in your email alert.

This way, if the record type is incorrect, nothing happens and the email sends. If the record type is correct, the field updates and the email sends.

I was able to find this screenshot on the interwebs:

example flow

  • See my second comment on the OP as to why I cannot use PB/WFR – JaredT Nov 28 '17 at 19:19
  • @JaredT That's not true actually. You can move them into a single PB and set up the flow to guarantee the field updates first. – gNerb Nov 28 '17 at 19:39
  • @JaredT Updated my answer – gNerb Nov 28 '17 at 19:46
  • Yes I completely agree with your methodology and have recommended the same to my management team. – JaredT Nov 28 '17 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.