0

Let's say I have a "Contact" object full of 50,000 records, 30,000 of which have a value in "MyExternalId__c."

And then I have a CSV file full of 10,000 records, all of which have a value in "MyExternalID" (they're exported from a database where it's the primary key).

  • I have reason to believe that about 99.5% of the records in my CSV file will match existing "Contact" records based on this external ID, and honestly, I'm not really interested in updating any data in those "Contact" records (it's presumed that such data is actually more recent than data from the CSV file).

  • With the remaining .5% (50 records), I'd like to do some special handling by reviewing them manually, one-by-one.

    • For the half of them (25 records) I expect to discover represent people who I believe are already in the "Contact" table, but whose external ID I simply don't have on file in Salesforce, I'd like a chance to manually type it in, and then treat them like the other 99.5% (do-nothing).

    • Then I'd like to be able to run a process with my original CSV file so that all that remains for actually doing something are the are the .25% (25 records) who really are new to my Salesforce "Contact" table -- these I would want to insert (while no-oping the other 99,975).

      • I can do the "no-op anyone who matches and insert anyone who doesn't" pretty easily with any old ETL tool by INSERTing all 10,000 records and letting 99.5% of them fail with a "MyExternalID__c value already exists" error. (It's not pretty, but it is doable with an ETL tool that I can configure behind the scenes and expose "run buttons" to business-unit power-users, because it's not my role to actually load or review the data every day -- plus I can bypass the whole "CSV file" thing and pull the query straight from the source database with this tool -- so I'm tempted. Anyway, point is I have an algorithm for that part, even if it's not a good algorithm yet.)

But if I did that with no review, I'd insert 50 records, not 25, and then there'd be annoying duplicate cleanup to do.

What I haven't figured out is a business-unit-power-user-friendly way to throw 10,000 records at my Salesforce org and simply see what matches without actually doing any DML in Salesforce.

CRMFusion PeopleImport can give me a pretty good dummy-check (because it downloads all 50,000 Salesforce "Contact" records to PC memory, does the cross-check, and then displays the results to its user for manual review), but if someone clicks the wrong button, they end up importing data they shouldn't have. I don't really consider it a turnkey solution to hand over to a business-unit power user where I say, "Click this button and you can get a preview of which records don't match on MyExternalId__c."

If there's some sort of "pretend-UPSERT these 10,000 rows and return the contents flagged would-be-INSERT vs. would-be-UPDATE but don't actually do any DML" or some sort of "pretend-INSERT these 10,000 rows and return the contents flagged would-be-SUCCESS vs. would-be-ERROR but don't actually do any DML" functionality to Salesforce and its APIs that I don't yet know about that'd be amazing.

In pseudo-code, I'm fantasizing about the ability to give a user a button that does:

SELECT *
FROM OurERPDatabaseOrCSV
WHERE MyExternalID NOT IN
    (SELECT MyExternalId__c FROM OurSalesForceOrg.Contact)

Any luck?

  • You can see what it there or not by doing an upsert only mapping the external id field. The results will tell you if it was created or not. You can pull out the ones that were not created and will be left with only those that did not have a match. Then you can update those with the rest of the data – Eric Jul 13 '17 at 5:51
  • Or, simply pull a list of all records with the external id and use a vlookup in excel to find matches then remove the matches from the file – Eric Jul 13 '17 at 5:52
  • @Eric, your upsert idea, though, would update everything that DID match! I want to do a no-op (there's data in the CSV file that I DO NOT want overwriting its corresponding data in Salesforce when a match already exists). Thanks for the VLookup suggestion ... trying to avoid telling people to spend their day VLookup-ing since this will be a daily process. :-) – k.. Jul 13 '17 at 19:43
  • that's why I said only map the external id field. It would change the last mod date but no data would actually update – Eric Jul 13 '17 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.