2

Our product backend has an ID field for each customer's instance of our software, so we have a custom Number field on our SFDC Account object to track each customer's Product_Org_Id__c. Often folks need data bulk loaded to SFDC accounts, and they supply the Product_org_id__c value as the unique identifier.

I have to then export all the Account Id values using a dataloader query: SELECT Id FROM Account WHERE Product_Org_Id__c IN (4738, 782, 5623...)

This has always worked just fine. However, the team began setting up new customers on a new "Silo 2" and these get ids in a different format: 4001316002, 4001325002, 4001313002... i.e. the id values are larger numbers.

When I try to export the account Ids using the same query, it gives me an error whenever I include "Silo 2" id values:

Account WHERE Product_Org_Id__c IN (4001316002, 4001325002, 4001313002
                                   ^
ERROR at Row:1:Column:47
For input string: "4001316002"

The Product_Org_Id__c field has a datatype of "number" and a length of 18 digits, no decimal places. I cannot for the life of me figure out why Data Loader has an issue with these larger values and not the "Silo 1" values.

Or if anyone can suggest a better way to handle these bulk updates when I'm given a list of product org id values and no SFDC Id values...

2 Answers 2

2

If you make the field an External ID field, you can use the "upsert" function to automatically map the ID to a Salesforce record. I don't have an answer as to why the query is failing when you're dealing with those numbers, but this method allows you to skip the query entirely, since you can insert/update in a single call. This, of course, presumes that your ID values are unique, or you'll get errors regarding those ID values.

1
  • Thanks, I had thought of that but unfortunately the values are not always unique, as some accounts share product instances (e.g. subsidiaries)
    – OBerm
    Aug 1, 2018 at 16:55
0

I was not able to solve this bug. I ended up skipping the WHERE...IN query and just queried all Account WHERE Product_Org_Id__c != NULL, then used a VLOOKUP to match my list of Product_Org_Id__c values to SFDC account Ids. Oh well.

You must log in to answer this question.

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