7

We have reports to search all cases that contain certain serial numbers (strings) in a custom text area field that can contain a comma-separated list of serial numbers. Additionally, serial numbers can be in a text field on a custom object related to case. Due to the maximum length of report filter arguments, we can just search for about 100 serial numbers in one run.

Now we have lists of thousands of serial numbers to search for. How can I do this? These ideas come to my mind:

  • Can you search for a long list of arguments using data loader? I cannot use the SOQL = operater because the serial numbers are in comma-separated lists. Data loader doesn't allow to use SOSL, does it?
  • I could build a visualforce page where a user can input the (long) list of serial numbers and a SOSL query is created. A SOSL query is limited to 20.000 characters, so this would allow to search for 1.000 to 2.000 serial numbers in one run. It could look something like this (ignoring the custom object):

    FIND {1234567890 OR 1234567891 OR ...} RETURNING Case

Any other ideas?

Edit: The most elegant way for users to provide the list of serial numbers would be to upload an Excel or CSV file. But they could also just paste a comma-separated list ("12345,23456,6543,3456") into a long text area.

Maybe the Analytics API could help (available as a closed pilot since Summer '13 ), but I could not find any public information on that.

Improve this question
2
  • 1
    I've dealt with numerous requirements similar to this. Usually it comes down to breaking down the problem into bite-sized chunks. How many thousands of numbers are you searching for? A single VF page can make 100 SOQL queries so normally you can break your query up into 100 chunks. Can you? Some more specific requirements may help, including how your users specify the thousands of serial numbers they are searching for.
    – jkraybill
    Jul 3, 2013 at 14:38
  • I've added more details in the question.
    – georg w.
    Jul 5, 2013 at 8:19

3 Answers 3

Reset to default
2

If you have access to Apex as a solution, split the string on commas and dump the serial numbers in a set. Then you can use the SOQL IN operator:

Set<String> serialNumbersToSearch = new Set<String>();
List<String> serialNumbers = csv.split(',');
for (String serialNumber : serialNumbers)
{
    serialNumbersToSearch.add(serialNumber.trim()); // If there are spaces in your csv...
}
List<Case> reportCases = [SELECT Id, Etc__c FROM Case WHERE Serial_Number__c IN :serialNumbersToSearch];
Improve this answer
1

(full disclosure: CTO of Kona) We can do this in Kona DataSearch. We can feed the list into a query string and then run the query. Not sure what the response time would be, but it would be interesting to try in a sandbox. In any case, we aren't restricted by any hard limit.

Improve this answer
0

Instead of giving exact serial numbers,you can give the ranges i.e use greater/less than operators to get the report. For Example, "Serial number greater than 123"

You can also do the same with SOQL query.

Improve this answer
3
  • Thanks for the input. Unfortunately, the serial numbers are not in any particular order, so I cannot use ranges.
    – georg w.
    Jul 3, 2013 at 9:05
  • If you're available with Serial numbers, sort it with utilities like Excel and then find the ranges(max and min).
    – Vignex
    Jul 3, 2013 at 9:16
  • We need to find cases with serial numbers that are not in a consecutive list. Image something like 45, 78, 23, 879, 23, 65, 231. Ranges do not help here.
    – georg w.
    Jul 3, 2013 at 9:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .