By Bethany Avery
This week, a customer needed to create an exclusion file. We had a file with IDs from System A but needed the main Blackbaud CRM™ lookup ID to drive the exclusion file. The IDs from System A were stored as alternate lookup IDs in Blackbaud CRM™. In addition to the lookup ID, we also needed to pass along some of the data in the original (System A) file that was not in Blackbaud CRM. This is a fairly common use case and one I’m sure most organizations encounter from time to time.
In the olden days, my go-to method would have been to use the “Import Selection” functionality in Blackbaud CRM™, re-export the data with lookup ID, and then use a VLOOKUP in Excel to append the main ID to the original file. This would work just fine, but let’s be honest, VLOOKUP often makes me cranky. So, I decided to take an alternate approach, leveraging a quick BVDL front-end mapped process and the BVDL Query Views. These query views are SO powerful! Read on to learn the step-by-step:
Step 1 - Added a new mapping that mapped Alternate Lookup ID + Last Name. I also mapped the 4 columns from the original file that were not in BBCRM but which I needed to “pass back”, to placeholder fields (more on that below).
Step 2 - Ran the new mapping in a Staging Only import job. In staging this data, it matches my existing records, so everything I need is now in the BVDL Staging Tables. No processing of the data is even required!
Step 3 - Created a “BVDL Import” ad-hoc query. In the top right section of my query, I included records where the System Record ID is equal to the GUID of the Import I ran in Step 2. You will find the System Record ID field here:
To populate it with the GUID of an import, go into the Import Exceptions screen of the BVDL process and pull the record ID from the end of the URL, as shown here:
All the fields I staged were then available to output on the bottom right-hand side of my query. I use the query tree to drill into BVDL Staging Instance > Staging Source > BVDL Constituent and grab the main Constituent Lookup ID that I needed (bc it had matched via alternate ID and this is now populated in the Staged Data), as well as the data I mapped to those Placeholders just to “pass along.”
From there, I saved and exported these query results using standard export functionality, and my exclusion file was complete!
Those who have been following the BVDL for some time may remember that the original name was BrightVine Data Loader. Maybe you even slip up and occasionally call it that, like some of us old-timers do 🙂 But this was yet another reminder of the accuracy of its current name, the BrightVine Data Link. Using the tool to connect IDs across systems and pass data from vendor to vendor (not all of which reside in both systems), all without ever having to actually process an import. That sure qualifies as “linking” in my book!
コメント