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)