EveryAction URL formulas

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.

  1. 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 us 5F96B28.
    • Then, take that string and reverse it, giving us 82B69F5.
  2. 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 is 10.
    • 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 letter K.
  3. Finally, we concatenate each of those pieces together. 82B69F5 and K become 82B69F5K, 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)

💡
=JOIN("", ARRAYFORMULA(MID(DEC2HEX(A2), LEN(DEC2HEX(A2)) - ROW(INDIRECT("1:"&LEN(DEC2HEX(A2)))) + 1, 1)))&CHAR(MOD(A2, 17) + 65)

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)

💡
="https://domain.everyaction.com/ContactsDetails.aspx?VANID=EID" & JOIN("", ARRAYFORMULA(MID(DEC2HEX(A2), LEN(DEC2HEX(A2)) -ROW(INDIRECT("1:"&LEN(DEC2HEX(A2)))) + 1, 1)))&CHAR(MOD(A2, 17) + 65)

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

Email signup