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
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.