As an admin, you sometimes just need to figure out the IDs of a large number of Salesforce records based on some data from some other source. For example, at my last job, I was often given a list of email addresses that needed to be added to a newsletter list. The first step I had to take was figure out which emails were already associated with Contacts in my instance. I had to grab the IDs of those records in order to add them to that list. Doing this by searching for records one-by-one would be prohibitively time consuming.
For what felt like ages, the best tool for me was DemandTools’s Find/Report IDs function. As an admin for nonprofit organizations, this tool was free for me, and it was easy. I just plugged in my input spreadsheet file, configured my search query, and the tool popped out another spreadsheet with the record IDs and any other fields I might need.
Unfortunately, the new owners of DemandTools, Validity, have decided to forgo their predecessor’s generous nonprofit discount of free. I was offered a quote of $4800 per year for a DemandTools license going forward, and that’s for a small org with only ~14 users. As the new pricing model is based on number of users, I have heard from admins of larger orgs who have received even more outrageous quotes.
I will not stand for this treatment. So I did what anyone with a developer’s mindset and an ax to grind would do: I made my own tool.
Announcing the Report IDs and Values Tool
I’m announcing a new open source project meant to meet the needs of the nonprofit Salesforce community for an affordable (read: free) tool for looking up the Salesforce IDs of records in bulk.
How to install
Click here to install the package directly into your sandbox org
Click here to install the package directly into your production org
After installation, you may want to add a custom tab and add it to your app to access the Visualforce page through normal navigation.
Note that it will not work properly in Lightning! You will need to switch to Classic.
How to use
- If you are a Lighting user, switch to Salesforce Classic. The current version doesn’t work in Lightning.
- Prepare a .csv file with your input data.
- The first row should be a header row.
- The first column should contain your search values. For example, if you are searching for Contacts by their email address, the email address should be in this column.
- You should deduplicate your data in your spreadsheet editor before loading it in to this application. Duplicate search queries will not return additional results.
- You can include as many additional columns as you need. These will be included in the output file. Note: for large jobs, tons of additional fields could cause you to reach page size limits.
- NOTE: If a search value matches multiple records (eg. multiple contacts with the same Email Address), these will be returned on multiple lines of the output file.
- In Salesforce, navigate to the Visualforce page. It should be at https://yoursalesforceaddress.com/apex/YES_ReportIDsAndValues
- Select the query object. Your output file will include records of this object.
- Select the query field. You will be matching records based on values in this field.
- Select Additional Fields to show in Output File. This tool will always return the record ID. Additional fields selected here will also be returned in your output file.
- Upload your query csv file and click “Run Query”.
- If you experience an error that says you reached page size limits, this is happening because the size of the outputted csv is too large. Visualforce pages are limited to 132kb. Consider breaking your file up in to smaller batches of ~500 records or less, or reducing the number of fields either in your input file or that you are returning from matched records.
- Likewise, if you experienced an Apex CPU Time Limit error, Salesforce is trying to match too many records. You will need to break your file up in to smaller batches of ~500 records or less.
- If everything worked, you should see a link that says “Click to Download Results”. Your result file will include the columns from your original file alongside the Salesforce ID of the matched record(s) and the additional fields you selected.
Known Limitations (as of 11/21/2018)
Some things to be aware of if you are thinking of using this tool instead of something fancier.
- It doesn’t work in Lighting.
- Visualforce pages are limited to 132kb, meaning you can’t use this tool for extremely large jobs.
- No “Scenarios” or reusable settings like Data Loader or DemandTools.
- Can only look up records by one field value, and it can’t do fuzzy lookups or operations. The single field limitation might be overcomeable by using formula fields in your input data and on the Salesforce records being queried.
Thank you for creating this! I am like you in that I had been depending on DemandTools until it was purchased by Validity. anyway, I decided to try it in my developer org. I created a report of all contacts in that org, exported it, and then used it to query. My results are strange. Under the ID column, it states “No Match with USA” or whatever country. Do you know why that is? I do not have State and Country Picklists enabled in my dev org, which can sometimes cause these issues. Thanks
It uses the first column of the csv as its search query, or the field you are matching by. Sounds like you’ve got your Country field in the first column, and it’s not finding matches. Is that right?
I apologize. i should have read the instructions. It just seemed so intuitive. However, the first column was the Salutation, then the First Name, Last Name, etc., basically the way Salesforce’s standard Accounts and Contacts report is formatted. The Mailing Country is in column I. So I moved the email column to the first column according to the instructions and tried again. My results: it found 3 of the 18 contacts in the file. All of these contacts are in the system and they all have email addresses. The three it found have complete addresses, meaning Cities, States, and Countries. The others that do not have City, State, or Country still have No Match with USA” or Chicago, or Singapore, etc. in the ID column. So it’s not just Country. Is the full address, at least city and country, a requirement?
Karen – without seeing your file, it would be hard to say for certain what you should do, but first know that you can only search by one field at a time, such as by email, and that field has to be in your first column.
No particular field is a requirement, just that there are matches to that first column.
If you need to search by multiple columns, or a combination of columns (such as an address), you could create a formula field in SF that concatenates your address fields together, then a field in your spreadsheet that does the same.
Hi Jon: I do know that you can only search on one field at a time. There really is no way to search using more than one field. I am only searching by email, but my results are saying otherwise unless there is a match. Since I cannot upload a file, or provide a screenshot, I can explain what I’ve done and my results. I installed and created a Tab. I switched to Classic. I exported contacts via a report from my dev org, so they are all fake. Moved the Email column to the first column, headers are already there from the report. Selected Contact as my query object, selected email as my query field and ran it. I already had the addresses in the file but I chose to include names and email addresses in the output file again, However, as I wrote, most of the results state “No Match with “country or city” under the ID column. I have no idea why. It found 3 IDs and none of the others, but the only thing they have in common is those three all have a City, State, Zip, and Country. The rest do not. I’d be happy to send you the file if you’d like. Thanks,