0

I have a requirement, where account name and account number if do not match with any other records, a custom field called alert_text__c will be updated with some text.

It is as follows:

List<Account_Codes__c> records = [SELECT ID, Account_Name__c, Account_Number__c from Account_Codes__c where Account_Name__c != Null AND Account_Number__c != Null]; 

final String accName = records[0].Account_Name__c;
final String accNum = records[0].Account_Number__c;
for (Account_Codes__c record : records)
{
    if (record.Account_Name__c != accName && record.Account_Number__c != accNum)
    {
        record.Alert_Text__c = 'Account Number and Account Names are different';
    }
}

Now, the above logic is not working as expected and even when i provide different Account Name and Account Numbers, I am not able to populate the text in Alert_text__c field.

Can anyone please suggest changes in the above code so that i can get this done.

Thanks!

  • How is List<Account_Codes__c> records populated? Also you may wanna check the code written or post the full version of your code as ppa variable is not declared, so likely it will give an error during runtime. – maniac coder Sep 8 at 17:40
  • @maniacCoder, Ah, my bad. It is not ppa, it is record. I have updated the question. Please suggest – SFDCUser Sep 8 at 17:47
  • How is List<Account_Codes__c> records populated? – maniac coder Sep 8 at 17:56
  • @maniaccoder, I have updated the question above with the query. Alert Text is not still not getting populated. Please suggest – SFDCUser Sep 9 at 5:39
0

There are a few issues with the approach you're taking

  • It only works on a single record at a time
  • Your query will run into selectivity issues eventually
  • Its not clear when this code would be run

My best guess is that you're trying to run this via anonymous apex to clean up existing data. It's almost always a better idea to make sure the data is clean before it's allowed to create a record in SFDC, but if you have existing data you're going to need some way to deal with it. Just keep in mind that you should strongly consider having a trigger in place to keep the data clean after you're done cleaning the existing data.

In the interest of keeping this answer under novel-length, here are my suggestions for the broad approach you should take:

  • First of all, you should somehow mark the records (use an existing checkbox field, create a new checkbox field, set Alert_Text__c to something like "needs to be processed") so you know which records you've already processed
    • A new field would allow you to set a default value, otherwise, you'd need to use data loader (or a similar tool) to update existing records
  • Having a formula field to concatenate your target fields is going to end up helping subsequent queries
  • To avoid selectivity and other governor limit issues, your initial query should limit itself to something like 1000 records (that's just an arbitrary number, but 1000 should be safe for things like query rows, cpu time, and dml rows)
  • You don't need to (and shouldn't) query for all Accounts. You only need to know if your working dataset has any matches
  • Maps are your friend

Turning that into an example (you won't be able to blindly copy/paste this, and that's done on purpose).

// A big assumption here is that all of the records being processed are already saved to Salesforce

// Ends up holding the values of your two fields so we can use it in the second query
Set<String> targetFormulaValues = new Set<String>();

// Limit the working set to help avoid issues with governor limits
List<MyObject__c> myObjList = [SELECT Id, Formula_Field__c FROM MyObject__c WHERE Is_Processed__c = false LIMIT 1000];

// gathering data to use in the next query
for(MyObject__c myRec :myObjList){
    targetFormulaValues.add(myRec.Formula_Field__c);
}

// Maps are your friend
// With this, we can easily store and recall which values for the target formula field we've
//   encountered as well as know how many times they appeared
Map<String, List<MyObject__c>> targetFormulaToRecords = new Map<String, List<MyObject__c>>();

// The reason why you want a formula field is that when you're trying to filter a query on the
//   combination of more than one field, you end up with the cartesian product.
// I.e. if you query for FirstName IN ('Mike', 'Vijay') AND LastName IN ('Smith', 'Pande'), you'll
//   get results for "Mike Smith", "Vijay Smith", "Mike Pande", and "Vijay Pande" (if they all exist)
//   instead of only for "Mike Smith" and "Vijay Pande"
// The formula field allows us to turn a multi-field filter into a single field filter
for(MyObject__c myRec :[SELECT Id, Formula_Field__c FROM MyObject__c WHERE Formula_Field__c IN :targetFormulaValues]){
    // Note that the query above isn't querying _all_ of the records
    // We're just trying to find out if there are any matches for _the subset of records
    //   we've chosen to work on_

    // standard map population pattern
    if(!targetFormulaToRecords.containsKey(myRec.Formula_Field__c)){
        targetFormulaToRecords.put(myRec.Formula_Field__c, new List<MyObject__c>());
    }

    targetFormulaToRecords.get(myRec.Formula_Field__c).add(myRec);
}

// We've gathered all of the information that we need now.
// Time for the final loop.
for(MyObject__c myRec :myObjList){
    List<MyObject__c> matchingRecs = targetFormulaToRecords.get((myRec.Formula_Field__c));

    // If all the records are in Salesforce already, then it should be impossible for
    //   matchingRecs to be null
    // ...but a bit of safety doesn't hurt
    if(matchingRecs == null || matchingRecs.size() == 1){
        // In here, the value of the formula field is guaranteed to be unique
        myRec.Alert_Text__c = 'It\'s unique!';
    }else{
        // Not unique (or perhaps no longer unique)
        // You could use this opportunity to clear the alert text
    }

    // In either situation, we've processed the record.
    // Mark it as processsed so it doesn't get picked up (and processed) again
    myRec.Is_Processed__c = true;
}

// Unless you're in a before trigger context, and are operating on record instances stored in
//   trigger.new/trigger.newMap, you need to use DML to persist the changes you've made
update myObjList;

The idea is then that you'd be able to run this multiple times through the developer console and eventually process all of your data. When your debug logs drop in size and see that the initial query is no longer returning any data, that's when you'd be finished.

This approach is also fairly close to what you'd want to use for an insert trigger to keep your data in good shape. In that case, you'd get your working dataset from a trigger context variable instead of a query.

| improve this answer | |
0

Can you please change your above to code to as below:-

    List<Account_Codes__c> records = [SELECT ID, Account_Name__c, Account_Number__c from Account_Codes__c where Account_Name__c != null AND Account_Number__c != null];
    Map<String,List<Account_Codes__c>> recordMap = new Map<String,List<Account_Codes__c>>();

    for (Account_Codes__c record : records)
    {
        if(recordMap.containsKey(record.Account_Name__c+record.Account_Number__c)) {
            recordMap.get(record.Account_Name__c+record.Account_Number__c).add(record);
        }
        else {
            recordMap.put(record.Account_Name__c+record.Account_Number__c,new List<Account_Codes__c>{record});
        }
    }
    for(String key:recordMap.keySet()) {
        if(recordMap.get(key).size() == 1) {
            recordMap.get(key)[0].Alert_Text__c = 'Account Number and Account Names are different';
        }
    }

The above code will form a map with key as concatenation of Account name and Account number with value as List<Account_Codes__c> so when we traverse the map again and find against each key what is the size of list. So if its 1 then we update the Alert_Text__c.

| improve this answer | |
  • I'm getting error while using the suggested query @maniacCoder. 'Unexpected Token newPPAs' – SFDCUser Sep 9 at 15:13
  • I have updated the question. Could you please suggest the changes if possible? – SFDCUser Sep 9 at 15:19
  • ok so whenever we have a record whose Account name and account number don't match we update that record alert_text__c ? – maniac coder Sep 9 at 15:37
  • Yes. The Alert text field should be populated with the given text value – SFDCUser Sep 9 at 15:40
  • I have updated my answer based on your clarification – maniac coder Sep 9 at 15:56

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.