+ Start a Discussion

query by array - similar to SQL "IN"

I have to insert/update a large number of opportunities (several hundred to several thousand) on a daily basis.  There are 500,000+ opportunities stored in salesforce.  To make it worse, the financial system can't store the salesforce ID.  That means I can't use retrieve because I don't have the salesforce IDs.  So I'm stuck with query.

Each record from the financial system has a "sale number" which is stored in a custom field in salesforce.  What I need to do is something like this:

select <columnlist> from Opportunity where SaleNumber__c IN (sale number array)

I know SOQL doesn't support the IN keyword, so does anyone know of a better way to get this done?  What we have to do today is pull the ENTIRE list of opportunities and then do a local match to determine insert/update - big performance hog.

My real solution is to build an intermediate database that contains both the salesforce ID and the sale number, do a local match and then an upsert.  That's in the works, but will take longer.  In the meantime...

This sounds bad, but if I use this syntax:

select <columnlist> from Opportunity where (SaleNumber__c=1 OR SaleNumber__c=2 OR SaleNumber__c=3 OR...)

How many ORs can I use?  It could be a very long string of them.
make SaleNumber__c an externalId (in the custom field properties in setup), then use the upsert operation specifying SaleNumber__c as the identifiering field.
That was fast, thanks!  It's actually worse than that.  The SaleNumber is unique within a record type, but not across the entire system.  So what we end up with is several different fields holding SaleNumbers (SaleNumberGA, SaleNumberFL, SaleNumberNC, etc.).  This comes from the financial system because there are actually several financial servers that don't talk to one another (don't ask, old system) - salesforce is becoming the centralized point.

I am working on a staging db to generate my own unique keys which will allow upsert,  but that will be a while before is happens.  At this time only one field per object can be designated an External ID - I need several.  If I could index several fields as External IDs, my problem would be solved because I can separate my records by record type.  So that leads me back to query for now.  Ugh.
You can have as many ORs as you can squeeze into the 10k SOQL length limit.

You could also add a new customField, that is the combination of both the source system, and its key and upsert on that. (i.e. make the externalId in salesforce a compound key that includes something that identifies the key source, s1:key_foo, s2:key_foo, s3:key_bar etc.)

With DBAmp, you could do the 'select <columnlist> from Opportunity where SaleNumber__c IN (sale number array)'  query AND do a bulk insert/upoad into the Opportunity table. If you don't have SQL Server, just use the free one from Microsoft.

Because the salesforce object looks like another table, you could build complex SQL join statements to match up the SalesNumber permutations with the external Id sitting on the Salesforce side.

See http://www.salesforce.com/appexchange/detail_overview.jsp?id=a0330000000nsOlAAI