0

I have been working on an APEX trigger to validate routing numbers on the Lead object. The initial code is as follows:

trigger RoutingNumberValidation on Lead (before update) {
for(Lead AllLeads:trigger.new){  
    if(AllLeads.Routing_Number__c != '123456789'){
        AllLeads.adderror('Oops!!! All routing numbers must go to real banks');

    }
  }
}

I then created a custom setting called "routing_validation__C" with a field called "US__c" that contains all of the numbers that can be accepted. I am having difficulty with how to reference the list custom settings field content to replace the '123456789' that I used as a place holder. How do I compare the data being updated to verify that it is contained in the list custom setting?

  • Why don't you use a validation rule ? – Mihai Neagoe Apr 14 '16 at 15:29
  • 1
    @MihaiNeagoe Because validation rules can't reference List Custom Settings, which you'd need to validate every possible routing number. – sfdcfox Apr 14 '16 at 15:33
  • @sfdcfox -- to Mihai's point, wouldn't VLOOKUP function work here? and thus one could write VR rules on the Vlookup custom sobject, thus preventing bad reference data - something that is hard to do with custom list settings. I realize OP mentioned list custom settings so offer this as an alternative – cropredy Apr 15 '16 at 0:10
  • @cropredy No, because VLOOKUP finds an object and returns a related field value. In this case, we're looking to find an absence of a value, so I don't think there's a way we can leverage VLOOKUP to our benefit. If I'm wrong, though (and I am known to be from time to time), I'd love to see a validation rule that does this. – sfdcfox Apr 15 '16 at 5:29
2

As an alternative to code, you can use the VLOOKUP function in a Validation rule

  1. Create a new Custom Object RoutingNumber__c
  2. Change the label for the Name field to be Routing Number
  3. Populate (the Name field) using Data Loader or your favorite tool all the valid Routing Numbers
  4. Use this Validation Rule in your Lead object:

    ISBLANK(VLOOKUP( $ObjectType.RoutingNumber__c.Fields.Name ,
                     $ObjectType.RoutingNumber__c.Fields.Name,
                     Routing_Number__c))
    

and create an appropriate error message.

The advantage of the custom object over a List Custom Setting is you can write VR rules, workflows, flows, etc to customize the data entry for valid routing numbers. You can ensure against duplicates. List Custom Settings are hard to control data quality without VF+apex solutions and even those can be easily bypassed.

| improve this answer | |
  • @sfdcfox -- here is the VLOOKUP solution alternative – cropredy Apr 15 '16 at 17:11
  • Cool. Looks like we never stop learning, eh? – sfdcfox Apr 15 '16 at 17:28
2

Using custom settings, you'd just ask the system to give you the value:

trigger RoutingNumberValidation on Lead (before update) {
for(Lead AllLeads:trigger.new){  
    if(RoutingNumbers__c.getInstance(AllLeads.Routing_Number__c) == null) {
        AllLeads.adderror('Oops!!! All routing numbers must go to real banks');

    }
  }
}

You could also use the newer custom metadata feature:

trigger RoutingNumberValidation on Lead (before update) {
for(Lead AllLeads:trigger.new){  
    if([SELECT COUNT() FROM RoutingNumber__mdt WHERE RoutingNumber__c = :AllLeads.Routing_Number__c] == 0){
        AllLeads.adderror('Oops!!! All routing numbers must go to real banks');

    }
  }
}

You can play with either way to see which works better for you.

(Note: Unlike regular objects, metadata objects have unlimited queries, so it's actually acceptable to put the query in a loop.)

| improve this answer | |
  • In your custom metadata feature example you have a SOQL query inside a for loop. Are there special exceptions for the new metadata feature that make this acceptable? – dBeltowski Apr 15 '16 at 17:19
  • @DougB Yes, see Custom Metadata Limits, specifically: "SOQL queries per Apex transaction Unlimited". – sfdcfox Apr 15 '16 at 17:22
  • Wow, learn something new everyday. Thanks @sfdcfox – dBeltowski Apr 15 '16 at 17:25

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.