- Why would we want to be able to generate record URLs?
- Example with Salesforce
- URL scheme in EveryAction
- Spreadsheet formula for EveryAction
A useful tool in any CRM admin’s toolbox is the ability to quickly and easily generate URLs for database records using the record’s ID. In a CRM like Salesforce this is easy, but in EveryAction and NGP VAN it’s a bit trickier.
In this post, I’ve documented a Google Sheets formula that uses an EveryAction or NGP VAN record ID to generate the URL for that record.
Why would we want to be able to generate record URLs?
Why would we want to generate record URLs from record IDs? Lets say you’ve exported a list of donors from your CRM. You’ve pulled in the most important fields into the export. But not all of the data about each contact is easily summarized, and you want people who are using the spreadsheet to be able to easily reference the full record for each contact in your CRM. Or maybe you want people using the spreadsheet to be able to make edits to records directly in your CRM.
Often, spreadsheets, not CRM reports, are the best way to represent and work with CRM data, but we still want to be referencing or updating the CRM directly. Generating record URLs makes this possible and convenient.
Example with Salesforce
First, let’s take a look at how this works in Salesforce Classic.
Most record URLs in Salesforce Classic look something like this:
https://domain.salesforce.com/003d000002E6I1h
The ID on the end of the URL above, 003d000002E6I1h
, is the same as the ID for the given record. This ID is available in reports and can be added to the page layout for the record. If you have a spreadsheet of Salesforce records, including a column for the record ID, it’s easy to generate record URLs with a simple spreadsheet formula.
URL scheme in EveryAction
In EveryAction, the record ID for a contact record has a special name, a VANID. Let’s take a look at an example VANID and its URL:
- VANID:
100231976
- Record URL:
https://domain.everyaction.com/ContactsDetails.aspx?VANID=EID82B69F5K
As you can see, the URL has a clear scheme, but the two IDs don’t match. The string after VANID=EID
, in this example 82B69F5K
, is the part of the URL that changes across different records. To build the spreadsheet formula, I needed to figure out how to get from 100231976
to 82B69F5K
.
I looked at pairs of sequential VANIDs and URL IDs until I figured out the encoding, explained below.
The string (82B69F5K
) that comes after VANID=EID
has two parts: 82B69F5
, and K
.
- The first part,
82B69F5
, is created in two steps. - First, take the original VANID (
100231976
) which is a decimal number, and convert it into hexadecimal, which gives us5F96B28
. - Then, take that string and reverse it, giving us
82B69F5
. - Second, we get the last character,
K
, in two more steps. - Take the original VANID (
100231976
) and calculate modulo 17 (the remainder when the number is divided by 17), which in this case is10
. - This number will always be a number from 0–16. Map that number onto the letters A–Q, where 0=A, 1=B, 2=C, etc.
10
corresponds to the letterK
. - Finally, we concatenate each of those pieces together.
82B69F5
andK
become82B69F5K
, which goes on the end of the base URL.
Spreadsheet formula for EveryAction
This process can be automated with a spreadsheet formula. Here’s one that works for Google Sheets, though it does not work in Excel:
Formula for the URL ID (Google Sheets only)
This formula will generate only the URL ID from the record ID, not the full URL for a record. This formula appears to work with most EveryAction objects I’ve tested it with, including Source Codes, Contributions, Activist Codes, and more.
Formula for a contact record URL (Google Sheets only)
This will use a VANID to generate the full contact record URL for a particular record. Make sure to change https://domain.everyaction.com/
to whatever your EveryAction or NGP VAN instance URL is.
Formulas for other record URLs
For other record URLs for other objects besides the contact object (for example, Source Codes, Contributions, or Activist Codes), you will simply need to change the base URL to the appropriate string. Here are examples of a couple record URLs without the URL ID on the end.
- Source Codes:
https://domain.everyaction.com/SourceCodeDetails.aspx?CodeID=EID
- Contributions:
https://domain.everyaction.com/PaymentDetails.aspx?ContactsContributionID=EID
- Activist Codes:
https://domain.everyaction.com/ActivistCodeDetails.aspx?ActivistCodeID=EID