0

I have a field (wallet number) on contact object for which i want to get results using a SOQL query.
The list contains more than 20k records for which i want to get related field values from contact object.
example

Select id, name, email 
from contact 
where wallet_number__c in ('a123','a114','b143','b121','b1124'...and so on till 20k values)

I ran the query but it will give error if i put more than 200/300 values.

Is this something i can achieve using SOQL. Can i query the column from the file directly to get results ? Or some other way to achieve this ?

2

Pankaj, the right way to query a list is the following

List<String> walletNumbers = new List<String>();
//populate list 
List<Contact> myRecs = [select Id from Contact where Wallet_Number__c in :walletNumbers];

Keep in mind that SoQL will not let you return more than 10000 records on a query, so you may have to either mark your VF page as read-only or use batch apex (or something else) for this solution if you truly need 20000 records at once.

Another thing to consider is that if Wallet_Number__c is not an indexed field then you may experience severe performance issues with the query, perhaps leading to an Apex CPU Time governor limit exception.

  • I need an extract of this query and need not update it somewhere else in SF, We just need to get the details in a csv format. Is it possible using the above query. Want to use data loader or workbench for this purpose. Pls advise – Pankaj Gupta May 15 '18 at 19:00
  • 2
    This query works in apex. You may not write a query that long in any of the tools you describe. There are other solutions, like creating a new temporary object that links to the relevant contacts, and query 100% of those rows (and the related contacts) via Data Loader, for example. – Sebastian Kessel May 15 '18 at 19:02
  • Pankaj, you can use https://workbench.developerforce.com to query and export the result in CSV format. – Hemendra Singh Rajawat May 15 '18 at 19:09
  • @HemendraSinghRajawat He is already aware of workbench. – Sebastian Kessel May 15 '18 at 19:10
  • 1
    @Sebastian : Thanks. Will check the object option. – Pankaj Gupta May 15 '18 at 19:10
0

If you're comfortable with Python (or some other scripting language with a Salesforce binding library), it's pretty easy to iterate over a collection of identifiers and build queries that fall just under the relevant limits to extract this data directly (without writing any Apex or modifying your org metadata).

Here's a lightly-modified example from a past project I worked on (note that this uses the simple_salesforce library):

# Perform a series of queries against `field_name` with the supplied `value_set` as possible values.
def perform_field_queries(self, sobject_name, field_name, value_set):
    query = 'SELECT {} FROM {} WHERE {} IN ({})'

    ids = value_set.copy()

    id_list = ''

    while len(ids) > 0:
        id_list = '\'' + ids.pop() + '\''

        # The maximum length of the WHERE clause is 4,000 characters
        while len(id_list) < 3980 and len(ids) > 0:
            id_list += ', \'' + ids.pop() + '\''

        # `self.context.connection` here is the `simple_salesforce.Salesforce` object representing the connection.
        # The first format parameter is the fields to return.
        results = self.context.connection.query_all(
            query.format('Id', sobject_name, field_name, id_list)
        )

        # Error handling supplied by the reader

        for rec in results.get('records'):
            # Do something with this object, like write it to a `csv.DictWriter` instance.

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.