Search code examples
salesforceapex

Select random Leads records using SOQL (Salesforce)

I created an Auto Number type field (Lead Id), which by default is stored as string in Salesforce.

From "Lead Id" field, I created formula number field "Formula Lead Id" which should be a number without decimal.

My plan was to use this number to randomly select Leads using SOQL e.g. :

SELECT Id, Name FROM Lead WHERE IsConverted = false AND Formula_Lead_Id__c > randomNumber LIMIT 5

This does not work at all returned Leads do not have "Formula Lead Id" number close to randomNumber. I know there are leads that are much close or even match randomNumber.

Any suggestion please?

Solution

  • Currently, since the ORDER BY clause is missing, if randomNumber is 10 and you have 100 leads (Formula_Lead_Id__c from 1 to 100) that SQOL may return any 5 leads in the range 11-100, so it may return lead89, lead90, lead91, ..., lead98.

    Try to change the SOQL to:

    SELECT Name
FROM Lead
WHERE IsConverted = false AND Formula_Lead_Id__c > :randomNumber
ORDER BY Formula_Lead_Id__c
LIMIT 5

    Please note that using ORDER BY, the default order is ascending, that's why I didn't wrote ORDER BY Formula_Lead_Id__c ASC.
    Moreover there is no need to inlcude the Id explicitly if the SOQL contains any other field.

    Now, since the ORDER BY clause is set, if randomNumber is 10 and you have 100 leads (Formula_Lead_Id__c from 1 to 100) that SQOL will return the leads from 11 to 20.

    If you need also the one with a Formula_Lead_Id__c that match randomNumber you should use >= operator

    SELECT Name
FROM Lead
WHERE IsConverted = false AND Formula_Lead_Id__c >= :randomNumber
ORDER BY Formula_Lead_Id__c
LIMIT 5