You need to sign in to do that
Don't have an account?
lithe951
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.
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.
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 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
Bill