function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MattLMattL 

Must query entire org, yet too many records

I've run into a bit of a problem with the governor limits on the SOQL query rows returned. Here's my situation.

The code needs to query the organization for all accounts whose type is "Customer". Then, it needs to find the largest existing Account Number, and increment it by 1, storing that new value into an account that's type is updated to Customer, or a new Customer account.

Trigger:
Code:
trigger Account_Number_Inc on Account (after insert, after update) {
 if(Trigger.new[0].Account_Number__c == null && Trigger.new[0].Type == 'Customer')
 {
  list<Account> AcctNums = [SELECT Account_Number__c From Account where Account_Number__c != null and Type = 'Customer' ORDER BY Account_Number__c DESC NULLS LAST];
  System.assert(AcctNums != null);
  Long curhigh = long.valueOf(AcctNums[0].Account_Number__c);
  curhigh++;
  Account acc = new Account(Id=Trigger.new[0].Id);
  acc.Account_Number__c=String.valueOf(curhigh);
  update(acc);
 }
}

 
Test Code:

Code:
public class AccountNumberTriggerTest {
 static testMethod void testAccountTrigger() { 
    Integer highestnum = integer.valueOf([Select Account_Number__c from Account where Account_Number__c != null and Type = 'Customer' ORDER BY Account_Number__c DESC NULLS LAST][0].Account_Number__c);
    Account b = new Account(name='blah2', type='Customer');
    insert b;
    Integer acctnum = integer.valueOf([Select Account_Number__c from Account where id =:b.id][0].Account_Number__c);
    System.assertEquals(highestnum+1, acctnum);
    }
}

 Now, my problem is that when I attempt to deploy it from the Sandbox, I get an error "Too many query rows". Upon trying to LIMIT my query, I've discovered that the LIMIT occurs before the ORDER BY, so if I limit the query, I don't get all the records and may not actually get the highest account number (depending on how Salesforce returns the records). But if I don't limit the query, I get errors of Too many query rows.

Any advice?

SuperfellSuperfell
as your query only cares about the first record, just use a LIMIT 1 clause in your soql.
MattLMattL
Yes, I already tried that.
As mentioned above, the LIMIT executed before the ORDER BY, so as a result I only really get the first record returned by Salesforce, unordered.
I tested it in my dev org, and have two accounts already in existance. One with a number of 561, and one with a number of 1223. When I create a third, it got assigned 562, instead of the correct value of 1224. Hence my problem.
SuperfellSuperfell
LIMIT should be applied last, perhaps you have another problem. (why don't you just use auto number anyway, your code as it stands has some interesting race condition issues)
MattLMattL
We can't use an auto-number because the field is keyed off of for an external system (integration), and therefore needs to be text. What sort of race conditions are you speaking towards?
SuperfellSuperfell
Well a text field won't order by on the numerical value, it'll order by on the actual text, so you're seeing the results i'd expect from that.

concurrent inserts will be both receive the same number, because your code can't see the other ongoing uncommitted transaction.
MattLMattL
We were able to modify the integration to expect a number field, hence we changed the field and were able to adapt the Trigger.

The reason we can't use an auto-number field is because we only need the Account Number field to update based on Type="Customer". To my knowledge you can't set criteria for auto-numbers.
dmchengdmcheng
This is a kludge, but if the Account Number value can only increase with new records, then perhaps you could include a "Account_Number__c > 1000" condition in the WHERE clause (changing the 1000 to whatever value makes sense your number range).