After adding a new validation rule, existing records may be 'invalid'. This causes issues for me because processes and batches will fail to update those records.

How can I identify which records are invalid? perhaps by some sort of batch operation?

  • 1
    here is a thought to - maybe - reduce your workload :) ... validation rules enforce business rules or processes. And in my experience these changes often only apply to a subset of records, e.g. "current and future records". Not always. But often. I suggest that you avoid applying new validation rules to records that should be viewed as out of scope of the new process you are implementing. You could easily rework your validation rules to EXCLUDE "dead and gone" records, e.g. special status, owner, dates, etc. Food for thought. – krigi Jan 31 '19 at 23:23
  • yes that is a good approach, which addresses my concern about automated processes failing. It would still be nice to identify invalid data so that data quality can be improved. – Ablue Feb 1 '19 at 0:27

The safest way is to query for records that would cause the Validation Rule to fire and run a Data Loader update to fix only those records. You should be able to write a SOQL query for just about any Validation Rule (other than those using, e.g., PRIORVALUE(), which I don't think is the area of concern here) to identify records that would fail the rule on an otherwise-innocuous.

I'd suggest executing that query through the Data Loader's query module or Workbench in 'Bulk CSV' mode to avoid time-out issues and ensure you have access to the data for correction.

For example, if your Validation Rule were

AND(ISPICKVAL(Industry, "Finance"), ISBLANK(BillingStreet))

you could query

SELECT Id FROM Account WHERE Industry = 'Finance' AND BillingStreet = null

and then work to remediate those Accounts.

There's another approach involving performing a global update operation with the Data Loader and finding the "bad" records in your error file. It's a lot less safe and can have negative consequences depending on how your org is customized and whether your automations have side effects, so I really don't recommend it.

I'll elaborate a bit. Let's suppose you're working with Leads, and you want to do your global update on Lead to find the bad records. There's three areas where I've seen negative side effects:

  • Automation. If you've got automation on Lead that, say, creates a Task each time Lead in New status is updated, you're going to get a lot of duplicate Tasks! Yes, it's better for this type of automation to be idempotent so that doesn't happen - but realistically, it does, particularly if the update happens at a point in the record lifecycle where it wouldn't normally be touched.
  • Integration. Certain types of integrations that work on the basis of record change events will fire a very large volume of messages in a very short span of time. Some outbound integrations can be overwhelmed by this influx, or poorly-implemented integrations can back up for hours.
  • Loss of Audit Fields. In some orgs, the last modified date and last modified by fields are considered nearly sacrosanct. In others, they are irrelevant. In the former type of org, performing a global update will cause consternation when, e.g., list views and reports based on time of last modification are no longer valid.
  • would you elaborate a bit on this? regarding the global update operation, you assert... "It's a lot less safe and can have negative consequences depending on how your org is customized and whether your automations have side effects, so I really don't recommend it." What specifically have you seen, what should we watch out for? – krigi Jan 31 '19 at 22:53
  • the way I see it, the global update operation is as safe as clicking "edit/save" on a record without modifying any fields. Essentially all you are attempting to update is the system mod stamp. Tread lightly, I understand, but generally I do NOT view this as an unsafe operation. Should I change how I think about this? – krigi Jan 31 '19 at 22:57
  • @krigi I've added some details on negative side effects I have seen. In fairness, two of them have to do with fragility in other parts of the system, but they're fragilities that are very real in many systems and need to be accounted for. – David Reed Jan 31 '19 at 23:03
  • @krigi Whether or not you view that as an unsafe operation is definitely context-dependent. If you're working in orgs that are better-architected than some I've had recently... you can get away with quite a bit more :) – David Reed Jan 31 '19 at 23:06
  • People forget about dangerous side-effects, its so good that you pointed it out : +1 from me. – Pranay Jaiswal Jan 31 '19 at 23:31

A simpler way to do this would be to use dataloader. For example, let's say you made a bunch of new validation rules on Account (any sObject, follow same pattern) and want to identify existing records that are now "invalid" and un-editable, due to the new validation rules. Follow these steps to easily identify this subset of records.

1. Perform export via Dataloader. Simply export the ID column, nothing else is needed.
2. Perform Update via Dataloader. Simply match ID to ID.
3. Observe the failed updates in the "errors" CSV that is automatically generated. Read the error message to identify the cause behind the failure. Should be easy to pinpoint the records that failed due to your new validation rules.

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.