Tech Thoughts

At the start of solution design I searched the internet and asked the AI, and didn't find a good answer. Claude confidently stated that custom Apex is the right solution here. No thanks Claude. Apex can query a set of records and consolidate them, but I'm not a developer, and I'm not comfortable leaving that kind of technical debt behind in a client org for admins to maintain.

Since I couldn't find a satisfactory answer, I went to the drawing board and worked out this declarative method instead. This blog has always been a space to contribute solutions back to the internet when I couldn't find them, so here's the writeup - and perhaps next time Claude will surface this approach and decide it's more appropriate to suggest than vibe coded Apex.

The concurrency problem

The core difficulty is concurrency. When several rows land at once, Salesforce fires a separate flow interview for each one, and any scheduled paths fire per-record as well. At every stage there are multiple interviews running the same logic at nearly the same moment, and each interview is aware only of its own triggering record.

That can easily run into a race condition. If each row's flow checks whether an opportunity already exists for the transaction, concurrent interviews can each find nothing and each create their own opportunity. You end up right back at one opportunity per line item.

The building blocks

The keys to the solution are a batching formula field that identifies which rows belong together, flow pauses in the form of staggered scheduled paths, and a DLRS rollup to aggregate amounts and other data up to the opportunity.

This build assumes the integration follows the architecture I described in a previous post - the raw data feed lands in custom object records first, and gets parsed out to downstream objects in-platform second. The rows discussed here are the staging object records.

The batching formula

The first problem is identifying which rows belong in the same opportunity. In this case the upstream system doesn't send a transaction ID with the record, so a formula field on the staging object approximates one by concatenating the customer ID, event ID, and the year, month, day, and hour of the transaction.

Any rows that produce the same key are treated as one transaction. The screen capture below shows a set of these batch keys alongside the opportunity that each row was ultimately linked to.

The record-triggered flow

The parsing work happens in a record-triggered flow on the staging object, which fires when a new row is created. Everything interesting happens on three scheduled paths - the immediate path does nothing at all.

The staggered delays are what serialize the work. So long as a transaction's rows all arrive within a few minutes of each other, every record finishes each stage before any record begins the stage that depends on it.

Scheduled path one - designate a primary record

Fifteen minutes* after the row is created, the first path decides whether its record is the Primary record of the batch - the one that will own the opportunity. A Get Records element queries all records carrying the same batch key as the triggering record, sorted descending with "get first record only" checked. A decision element compares that first record to the triggering record - if they match, an assignment marks the triggering record Primary, and if not, the triggering record is marked Secondary.

The sort is the detail that makes this work. Every concurrent interview runs this same query, and they all need to agree on which record wins. Any sort criteria will do as long as every interview returns the same result - for this use case I sorted on a formula field that prioritizes the registering constituent in cases where there are guest tickets in the transaction, but record ID would work fine for the core sorting need.

The classification is written to a utility text field on the record, because the database is the only place the other interviews can read that from.

* the first path waits fifteen minutes to start because in this use case I observed a couple of cases where the corresponding record in the constituent table was slightly delayed and created as much as 13 minutes after the transaction record. So a long initial delay was set here to design for the possibility of that happening, but in simpler use cases it should only need a minute or two as long as all records consistently show up at about the same time.

Scheduled path two - create the opportunity

Five minutes after the first path, the second path checks that classification. If the triggering record is Primary, a subflow creates the opportunity, and the new opportunity's ID is written back to a lookup field on the triggering record. Secondary records pass through with no action taken.

Scheduled path three - link the secondary records

Five minutes later, the third path handles Secondary records. A Get Records element fetches the batch's Primary record - the record with the same batch key and that's marked Primary and already has the opportunity lookup populated - and writes that same opportunity ID onto the triggering record.

With that done, every row in the batch points at the same opportunity record.

DLRS does the aggregation

From there, rollups take over. Declarative Lookup Rollup Summaries (DLRS) is a free community built and maintained open source tool that creates rollup summaries across lookup relationships.

A DLRS rollup sums the line item amounts up to the opportunity amount. Text can be aggregated too - the concatenate or concatenate distinct operations work well for assembling line item descriptions into the opportunity's description field.

The original rows stay in a related list on the opportunity. Consistent with the architecture from my previous post, that related list serves as a permanent log of the source data, and each row's opportunity lookup doubles as the signal that the automation run completed.

The takeaway

When an upstream system sends multiple simultaneous rows per transaction, a formula field can stand in for the missing transaction ID, staggered scheduled paths can serialize the concurrent flow runs, and DLRS can handle the aggregation. Three declarative ingredients, no code.

Apex could accomplish the same consolidation, but for organizations without developers on staff it's better to provision a solution the org's admins can open up, read, and maintain.

Generative AI disclosure

This is my second post about extending Blackthorn Events with custom automation, following the post on building a screen flow to upload and manage attendee groups. This build is much simpler than that. As with the attendee group build, note that custom automations are outside the scope of Blackthorn support, so they cannot help if any issues arise related to custom automations in your org.

What makes this possible

The piece that makes this work is an object in the Blackthorn Events data model called Event Registration Submission (ERS). When someone works through a Blackthorn registration form, the package captures their in-progress registration information in an ERS record.

An ERS record in Draft status represents a checkout that was started but not completed. When a person completes their registration, the submission finishes in Completed status. When it is instead abandoned, the Draft record remains.

The registrant's responses are stored on the ERS record as a JSON payload. So the contact information a person entered before abandoning their registration is available to automation.

That is what this solution builds on. A scheduled flow reviews the previous day's abandoned drafts, confirms the person didn't go on to register, and creates an Invited Attendee record for them. That triggers an email containing Blackthorn's AttendeeLink - a unique link that brings the constituent back to a pre-filled registration form containing the information they previously provided.

The four flows

The complete solution consists of four flows:

ERS [Scheduled] - daily cart abandonment email - a scheduled flow that turns the prior day's draft submissions into Invited Attendee records

Send Blackthorn | Event invite email (custom trigger) - a record-triggered flow that sends the invite email containing the AttendeeLink

Scheduled - Attendee - send cart abandonment follow up email - a scheduled flow that sends a reminder email three days later if the person still hasn't registered

BT Logs [AfterSave] - delete duplicate attendee - a record-triggered flow that cleans up when an invited person comes back to the form without using their AttendeeLink

The daily scheduled flow

The first flow runs every morning against the ERS object. Its start conditions filter to records where Status equals Draft and a custom checkbox formula field called Created Yesterday equals TRUE.

A scheduled flow runs once for each record that meets the start conditions. For each draft, formula resources parse the record's Form JSON field to extract the registrant's details. Each formula finds its key in the JSON text, then pulls out the characters between the end of the key and the next delimiter. Here is the formula that parses the email address:

MID( {!$Record.conference360__Form_JSON__c}, FIND('"email":"', {!$Record.conference360__Form_JSON__c}) + LEN('"email":"'), FIND('","', {!$Record.conference360__Form_JSON__c}, FIND('"email":"', {!$Record.conference360__Form_JSON__c}) + LEN('"email":"')) - (FIND('"email":"', {!$Record.conference360__Form_JSON__c}) + LEN('"email":"')) )

Separate formula resources parse the first name, last name, and event item ID the same way.

A decision element then evaluates whether an email address exists and is nominally valid (i.e. contains an @ symbol) in the parsed submission. If there is no email address to re-engage, the draft is filtered out here.

Next the flow gets any existing Attendee record on the event with that email address. This is the check that the person didn't go on to complete their registration in a subsequent transaction. It also prevents creating a second invitation for someone this automation already invited.

If no Attendee exists, then the flow creates the Invited Attendee record. The create records element assigns:

The first name, last name, and email address parsed from the submission

The event item parsed from the submission

The lookup to the event

A Registration Status of Invited

An Invited to Register flag - a custom field identifying the attendee as created by this automation

The invite email flow

The flow fires when an Attendee record is created with a Registration Status of Invited. It sends the Blackthorn Event invite email template from the organization's events email address, and logs the send against the attendee record and their contact.

The email includes the AttendeeLink. Setting the registration status to Invited is what tells the AttendeeLink to greet the person with a welcome message and pre-fill their information on the registration form, so they can pick up where they left off.

The follow up flow

The second scheduled flow also runs daily, and sends a follow up email to invited attendees three days after they were created if they still haven't registered.

Its start condition is a single custom checkbox formula field called Invited 3 days ago, which evaluates the condition at the record level.

The email itself is composed in a Send Email action. It greets the person by name, asks if they're still considering it, names the event, and presents a Complete Your Registration link that points at the AttendeeLink field on the invited attendee record.

Both the contact update and email send steps have fault paths that email an alert to our team if something fails.

Handling the duplicate email error

This design creates one awkward conflict. Once an Invited Attendee record exists, if a person navigates back to the registration form on their own - outside of their AttendeeLink - they encounter an error message, because an Attendee record with the same email address already exists.

We solved for that with the fourth flow and a data dictionary entry. Blackthorn writes these registration failures to its log object, and a record-triggered flow is invoked when a log record is created from a registration. A decision element screens the log's message text so the flow only acts on the duplicate registration error.

The same JSON parsing technique from the daily flow is reused here, extracting the email address and event ID from the log record's information payload. The flow then gets the Attendee record matching that email and event where the Registration Status is Invited, and deletes it, clearing the way for the person to register. Filtering the lookup to Invited status means the flow can only ever delete an invitation, never a completed registration.

The flow also has a scheduled path that runs 15 minutes after the error was logged. It checks whether a Registered attendee now exists for that email and event - meaning the person tried again and got through - and sets the Invited to Register flag on the new attendee record for reporting purposes.

We also added a Data Dictionary entry that modifies the error message, which normally reads "Email address has already been used to register for this event. You must use another one to continue." Our version instead instructs users to please try again in 5 seconds, since the record-triggered deletion typically completes quickly. In addition to the toast pop up error message, it also appears in red under the email address field when encountered. That user friction is not ideal, but it has been workable.

Scheduled flow start conditions and formula fields

A scheduled flow's start conditions only support simple field-to-value comparisons. You can't enter a formula expression there, so a condition like "created yesterday" can't be expressed directly. The recommended workaround is a formula field on the object that evaluates the condition at the record level, which the start condition can then filter on. Both scheduled flows in this solution rely on that pattern.

The Created Yesterday field on the ERS object is a checkbox formula:

DATEVALUE(CreatedDate) = TODAY() - 1

Record-triggered flows offer a Formula Evaluates to True option in their entry conditions, and there's an idea on the IdeaExchange to bring the same option to scheduled flow start conditions. If that were implemented, single-purpose formula fields like these would be unnecessary. Consider upvoting it if you'd like to see that too.

The results

This has been a really successful initiative. Reports indicate that roughly half of re-engaged constituents come back and complete their registration.

The Invited to Register flag makes that reporting possible, by identifying which completed registrations originated from a cart abandonment invitation.

Implementation

Here's the full inventory of components if you want to build this in your own org:

A Created Yesterday formula field on the Event Registration Submission object

An Invited 3 days ago formula field on the Attendee object for the follow up condition

An Invited to Register flag field on the Attendee object, used for reporting

The daily scheduled flow that converts draft submissions into Invited Attendees

The record-triggered flow that sends the invite email with the AttendeeLink

The scheduled follow up flow that sends the three day reminder

The record-triggered flow on the Blackthorn log object that deletes the Invited Attendee after a duplicate email error

A Data Dictionary entry modifying the duplicate email error message

For more information, I recommend reviewing Blackthorn's documentation on the Event Registration Submission process to understand how draft records are created and processed in your org.

Generative AI disclosure

Other document merge package options

Document generation is a mature product category on the AppExchange. Conga Composer, Nintex DocGen, S-Docs, and Apsona are well-known established options. Apsona in particular has a loyal following among nonprofits for its suite of features including document and email merge. Nutrient is another newer option who I just encountered at Midwest Dreamin'. Their product looks compelling with doc merge coupled with in-platform document editing and e-sign capabilities.

All of those are capable products, and for specific needs like complex templates or high-volume batch generation they're worthwhile choices. But for a simple straightforward donation letter workflow, the capability may be included in tools your organization already uses without licensing a new package.

This NPSP Trailhead module on donation acknowledgments recommends either a document generation app or running a report and doing a manual mail merge from the export. The approach in this post sits in-between those two - generation and record updates are automated, while a human stays in the loop on the letter itself.

The merge template is itself just a Google Doc

One of the best parts is that the merge template is itself just a Google doc. Merge fields are plain text placeholders wrapped in double curly braces, like {{FirstName}} or {{Amount}}.

When the template needs to be updated, there's no procedure for loading a new version into a Salesforce package - users can just edit the Google doc directly. As long as they don't mess up the merge tags it'll just continue to work.

How the workflow runs

Here's the full workflow from the user's perspective:

Set the Acknowledgement Status picklist value on the Opportunity to something that indicates the letter needs to be generated, such as Create Letter.

That status triggers a Zap which picks up the record's info and creates a new Google Doc from the template, with the merge fields populated and the record's 18-character ID appended to the end of the filename.

The new letter lands in a Drive folder, waiting for staff to review, adjust, and personalize it as needed.

When the letter is ready, the user drags it into a Completed Letters subfolder.

The file showing up in that folder triggers a second Zap, which parses the record ID from the end of the filename and updates that Opportunity to record the gift acknowledgement.

(optional) The Drive API also includes an option to invoke a PDF of a file, which can be used to load the completed letter back to Salesforce attached to the Opportunity.

The rest of this post walks through the Zap configurations.

Zap 1 - create the letter from the template

The first Zap watches for that picklist change in Salesforce, then runs the Create Document from Template action in the Google Docs connector. Several Formatter steps ensure that specific data points are formatted the way we want to see them in the letter. Then it points at the template, maps Salesforce fields to the placeholders, names the new document, and specifies the Drive folder it will land in.

One heads up here - there's a longstanding quirk where the Template Document dropdown fails to display your template. It's been reported in the Zapier community. The workaround is to open the template doc in your browser, copy the document ID out of the URL, and paste it into the Template Document field as a custom value.

The critical detail - append the 18-character record ID to the filename

Everything in this build hinges on the document's filename. When Zap 1 names the new document, it appends the Opportunity's 18-character record ID to the end. The result looks something like Acknowledgement Letter - Pat Donor - 006XXXXXXXXXXXXXXX. After the Zap finishes and the letter is sitting in Drive, that's the only connection from that document back to the Salesforce record it came from.

Appending it to the end, rather than the beginning, keeps the name human readable and gives the second Zap a stable position to parse from. Other parts of the filename can be edited as needed, as long as the that trailing ID is left alone.

Staff review, then drag it to done

There's now a Google Doc sitting in the Drive folder, waiting for staff to review, adjust, and personalize the donor letter as needed. This is good example of a recurring theme in automation governance, humans need to stay in the loop where discernment is needed and personal touches are appropriate, while making space for that by offloading time-consuming repetitive tasks to the machine.

After the letter has been reviewed and is ready, the user just drags it into the completed folder. That's all it takes to initiate the data transfer back to Salesforce.

Zap 2 - bring it back to Salesforce

The second Zap uses the New Document in Folder trigger in the Google Docs connector, pointed at the completed folder.

A Formatter step then parses the 18-character ID off the end of the filename. Because the ID is a fixed length and always in the same position, this is a simple text extraction.

With the record ID parsed, the Zap updates that Opportunity's Acknowledgment Status and stamps the current date in the Acknowledgment Date field. Note the formatter step that exists to translate the UTC datetime stamp to your local time zone.

From there you have a couple of options for connecting the finished letter back to the record:

The Google API can serve a PDF version of the doc, which could be uploaded to Salesforce and attached to the record.

If staff just need a pointer to the letter, write the Doc's URL to a URL field in the record update step.

Where a document generation package still makes sense

To be fair to the paid apps, there are jobs this approach isn't well suited to do:

Repeating tables of related records - opportunity line items, a year of gift history - and conditional sections would require a far more complex custom middleware automation configuration to achieve.

It runs one record at a time as records are flagged. Big batch jobs like year-end tax summaries for an entire donor base are a different use case.

Generation happens outside Salesforce, so there's no native preview or configuration options inside the CRM.

Zapier tasks are billed as metered usage. Every letter consumes multiple tasks, and it's worth doing the math against your plan to determine cost-effectiveness for your use case.

Implementation

Here's the full parts list if you want to build this yourself:

A Google Doc letter template with plain-text {{placeholder}} merge fields

A Drive folder for new letters to land in for review, and a subfolder for completed letters

A picklist value on the Opportunity that is set either by staff or a flow to queue a new donor letter

Zap 1: Salesforce trigger on that picklist change, several formatter steps, then Create Document from Template, with the 18-character record ID appended to the end of the new document's name

Zap 2: New Document in Folder on the completed folder, then parse the trailing ID, and update the Salesforce record

Optionally, a URL field on the Opportunity to hold a link to the finished letter

For more information about how this works, see Google's documentation on merging text into a document to understand what the underlying API supports, and Zapier's guide to creating and autopopulating Google Docs templates for the template formatting details that keep placeholders detectable.

Generative AI disclosure

NTEN previously published my blog post discussing high level takeaways from the analysis. The full academic paper, Contemporary Practices in Nonprofit Technology Use and Management: An Exploratory Data Analysis, may be accessed through the University of Minnesota Digital Conservancy. This article is primarily a technical walkthrough covering data transformation methods used in the Power Query model to generate the data tables supporting that analysis.

Without further ado, here is the link to download the .pbix file containing the Tech Accelerate transformation data model.

To explore how this model works, download the contents to a local folder, open the .pbix file in Power BI Desktop, and point the FILE_PATH parameter at your local directory where the CSV files are stored.

Note that the provided dataset has been scrambled. It’s the same size and shape as the actual dataset, but all of the scores and organization details are randomly mixed up, intentionally corrupting the example data to help protect the real data’s privacy.

About Power Query in Power BI Desktop

This is part of a series of blog posts that share and walk through Power Query data models. The first post in that series, walking through a data model for consolidating Salesforce profile permissions, also includes a beginner's primer on Power Query.

Power Query in Power BI Desktop is one of the most powerful software tools in my repertoire. My first exposure to it came during my first week working at W4Sight, almost four years ago, when co-founder Molly Mangan introduced me to it immediately. That first weekend I ordered and read a copy of Getting Started with Power Query in Power BI and Excel, and I've been working with it ever since.

Power BI Desktop is a free download from Microsoft. Note that there is no Mac version of Power BI Desktop, but Power Query for Excel is available for both Windows and Mac.

This use case - Tech Accelerate dataset transformation

Tech Accelerate is NTEN's free organizational self-assessment for nonprofit technology adoption. It consists of 97 questions scored on a scale of 1 to 4, spanning four categories: engagement, infrastructure, leadership, and organization.

The data model ingests raw CSV exports and carries them through data cleanup, group identification, data flattening, flag variable creation, and data sampling. Our end result was a primary sample of 1,283 assessments from the same number of organizations, several secondary samples, and a series of aggregate tables that supported the analysis.

The part of the methodology I most want to highlight is risk flag analysis, which was a new technique for analyzing Tech Accelerate data. Every question in the assessment has a warning threshold and a risk threshold designated by NTEN, and those thresholds vary widely - any score of 3 and under indicates risk on question IF 25, while only a 1 indicates risk on question LD 14. That variance makes raw score comparisons across questions difficult to interpret.

Converting answers to binary flag variables normalizes for that. Each answer becomes a 1 if the score was at or below the threshold and a 0 if it was above, which makes risk levels comparable across all 97 questions. The data model enables this by matching the per-question thresholds to more than 130,000 answers and calculating the binary flags in one scripted pass.

Data model walkthrough

There are seven folders in the Power Query model that we'll walk through in order: Source data, Parameters, Exception reports, Data transformation, Answer analysis, Org metric buckets, and Other Queries. The Power BI Desktop file containing the model was delivered to NTEN along with the rest of the capstone outputs.

Source data

The five queries in this folder pull in the source data to be transformed. These queries simply point at the following CSV files and pull them into the data model: TA Funder Groups, TA Group Members, TA Questions, answers, and snapshots.

The answers file is the most important. It's over 130,000 rows long, containing a record for every Tech Accelerate question ever answered, and each answer row identifies the question, the score, and the snapshot.

The snapshot ID joins to the snapshots table, which contains over 3,700 rows - one for each distinct self-assessment ever completed by an organization. Because NTEN uses CRM record IDs to identify constituents in a couple of survey data tables, it's possible to correlate transformed program data with their CRM. We did not utilize that or build it into this model, but the capability exists via Power Query’s Salesforce connector which can ingest data from any object or report in the SFDC.

The TA Questions file contains a table of every question, including their warning and risk thresholds. This table was leveraged when I created the M script contained later in the model which calculates warning flag and risk flag binary variables for every answer.

Parameters

This folder contains two parameters. A parameter in Power Query is a named value stored in the model that other queries can reference, so a value used in many places only needs to be maintained in one. Parameters are technically queries themselves, they just return a single value instead of a table.

The FILE_PATH parameter controls where the CSV files are read in from.

The ANSWER_THRESHOLD parameter controls how many answers must be on a snapshot to include that snapshot in the analysis. We set it to ten for this project.

The EXTRA_QUERIES_ON parameter controls whether alternate samples and a few other “extra” queries load. It’s defaulted to false which renders those queries null to help improve performance out-of-the-box.

Exception reports

This folder contains queries of the data that gets removed from the raw dataset, so that only relevant data is included in the analysis. I'll come back to these below, after covering the transformation queries they mirror.

Data transformation

This folder contains the queries that execute the data transformation. There are a few prep queries in here, followed by the main event - the queries that start with DT are the primary data transformation engine.

The two queries that start with Prep Org Data exist to remove some outliers from the source organization data and merge in analysis buckets that are established in other queries.

The *Qthresh *queries pivot the TA Questions table over to columns and convert the values to numbers. These values are later merged onto the tabular question answer data so that formula fields can compare answer values to the thresholds and derive the binary variables. In the model's naming convention, risk flags are labeled Red and warning flags are labeled Orange.

The Dedupe and Pivot query removes duplicate answers to resolve a minor quirk in the data, filters out -1 and 0 values which indicate that a question was either skipped or not answered at all, then pivots the very long list of answer rows to a tabular format where each question is a column and each snapshot is a row.

The DT 0 query joins the original snapshots table to prepped org data with the analysis buckets included. Then it joins to the Dedupe and Pivot query and brings in the tabular answer data.

DT 1 builds on DT 0 by merging in the Qthresh queries, bringing in the threshold values for warning and risk flags for each question, then adds calculated columns for the binary values. The merge works by adding a hook column that contains a constant value for every row, then joining the single-row threshold tables on that, which lands every question's threshold values alongside every snapshot's answer values. This script is very long and it results in a very wide table - the next several queries are much shorter.

DT 2 is where the transformation starts winnowing the dataset down to the sample that the analysis focused on. Not much happens in this script - it invokes DT 1 and then removes all of the rows flagged legacy.

DT 3 invokes DT 2 and then removes all rows where the count of questions answered in the snapshot is less than the ANSWER_THRESHOLD parameter value.

DT 4 results in the primary data sample used for analysis. It invokes DT 3 and removes all prior snapshots beyond the most recent for each given org, resulting in a sample that contains just one response per organization with only the most recent response kept.

Now, back to those exception reports. The three queries in that folder are the inverse of DT 2, DT 3, and DT 4. They produce a log of the records removed from the final sample at each of those steps. Generating these exception files is a best practice that I held over from my usual practice of using this software to convert CRM data. Exception reports exist for the purpose of checking that included data plus excluded data equals the number of records in the original dataset, to validate that no data got dropped on the floor.

The queries starting with DT 4a through DT 4g are all alternate samples, which were used for secondary analysis in the capstone paper. A Funder Groups subfolder within Data transformation holds queries that slice out per-cohort tables for organizations participating in NTEN's Nonprofit Tech Readiness groups.

Answer analysis and Org metric buckets

These two folders contain a series of queries that produce the aggregated data tables used in the primary and secondary analyses - risk flag prevalence and question score averages segmented by answer year, budget, tech budget, staff counts, decade founded, organization type, and location. These tables were copy-pasted out to Excel for further analysis, where the charts and comparisons in the paper were built.

The way this is built is honestly inefficient - out of expedience, I duplicated queries and then tweaked the code to derive each output. The model would likely be more performant if fewer queries were used to create multiple table visuals for the analysis. It works, but it makes my computer churn for a while to get there. Performance optimization is an area where this data model could be improved going forward.

Other Queries

The last folder is a working area for queries that supported the build but aren't part of the main pipeline. It holds a few record count checks, variants of the question table that were used to generate the repetitive flag calculation code, and a query documenting the budget and employee data outliers that informed cleanup.

The takeaway

This model turns a raw export of survey answers into a repeatable, scripted transformation: inputs get cleaned up, flag variables are calculated against per-question thresholds, a sample is isolated for analysis, and exception reports validate that nothing was lost along the way. Because the whole thing is scripted, the same pipeline can be refreshed periodically to update existing analysis, and/or it can be modified or extended for future analysis efforts.

The capstone recommended that NTEN could keep employing risk flag analysis and build on this data model in the future. My hope is that documenting how the model works makes it easier for whoever picks it up next.

Generative AI disclosure

Salesforce has been steering admins towards permission set-led security for years. In 2023 they announced that permissions in profiles would be retired by 2026, but that was placed on hold, then last month they announced that retirement is officially cancelled.

I'm still pretty excited to share this model though, in part because while the move will no longer be required its still strongly recommended, and because this is a really good turnkey example of how Power Query can be leveraged for Salesforce administration.

Current guidelines recommend using Profiles only for default user settings - Default Apps and Record Types, Login Hours, Login IP Ranges, Session Settings, Password Policies.

All access and permissions should be provisioned through Permission Sets - object and field permissions, app and system permissions, custom permissions, Apex class access, Visualforce page access, connected apps, and tab settings. This excellent March 2026 Salesforce Admin's Guide to Profiles and Permissions explains further.

Something I realized in the process of this work is that the model for creating a new permission set from consolidated profiles is simpler than merging permissions into a single profile. The previous version of this model used parameters to identify which standard profile to collapse everything into, plus queries to compare existing permissions against incoming ones. When the target is a brand-new permission set instead, it's a clean slate with nothing to reconcile, mitigating complexity.

In practice, my client and I didn't just lift-and-shift all the profile permissions into one big permission set. We used the opportunity to analyze that consolidated baseline permission set, discern which permissions belonged in the baseline versus segmented out to distinct permission sets, then created and assigned those accordingly.

This model also extends the original by consolidating Setup Entity Access items, which the first model didn't cover. That table enables access assignments including Apex Classes, Visualforce Pages, Apps, Custom Permissions, and more.

Data model walkthrough

Up front, here is the downloadable resource: Power Query template for consolidating profile permissions into a baseline permission set. Alternately, here's a plain text download of the M script to consolidate profile permissions into a baseline permission set if you just want to explore the code.

To run the data model, you'll need Power BI Desktop installed on a Windows computer. Upon opening the template, it should prompt you to authenticate and connect to a Salesforce environment. Once that's established, it will process the script and you can access the default outputs via Table view on the front end of Power BI Desktop.

If Power Query is new to you, this template file is likely more accessible than the one in the previous post. There are no parameters to configure this time - it's turnkey, with default profile selections and clear instructions on where to modify the logic to get the right list of profiles for you.

How it works

Under the hood its all just metadata. This data model queries your SFDC for all permission table entries that are related to relevant Profiles, and consolidates them into a single consolidated Permission Set. Then you can bulk insert the result into your SFDC as one or more Permission Sets.

Source data

There are twenty three queries built into this model, organized by folders. Fourteen of them are source data queries, split between a main folder and a subfolder.

These seven queries are the raw tables pulled in via the Salesforce connector:

User

Profile

Permission Set

Field Permissions

Object Permissions

Permission Set Tab Setting

Setup Entity Access

Same approach as the first model - I prefer keeping source queries clean and organized in one place, to be referenced as starting points by other queries which execute transformations.

Setup Entity Access table joins

Nested under Source data is a subfolder holding seven more tables:

Application

Connected App

Apex Page Info

Apex Class

Custom Permission

Organization-wide From Email Address

Flow Definition

Setup Entity Access connects to each of these tables, and they're joined in the model for the purpose of looking up item labels. They are all clean source queries, aside from one needed step in Apex Page Info that transforms 15-character IDs to 18-character IDs.

Transformation

These four queries narrow the source data down to the profiles being consolidated.

The Active Users query filters the User table to active users with the Standard user type, and removes any users whose email address is [email protected].

The Active User Profiles query distills that down to the distinct list of Profile IDs those users are assigned.

0 - Profiles selected can be customized per your use case. Everything downstream keys off the list of profiles produced by this query.

The default criteria are twofold:

A profile must be assigned to at least one active user, via an inner join to the Active User Profiles query.

The profile name is filtered to omit "Salesforce API Only System Integrations", "System Administrator", and "Sales Insights Integration User" profiles.

Modify the Filtered Rows step in this query to get the specific list of profiles that's relevant to you.

The Join Profile to Perm Set query then joins each Profile record to the Permission Set record that looks up back to it in a 1-1 relationship, enabling the rest of the permission tables to consolidate back via their ParentId field. This query is referenced by every subsequent query in the Consolidation folder.

Consolidation

Now we're getting to the good stuff. These five queries produce the consolidated outputs, one per permission type. The common idea across them is a union - if any selected profile has a permission enabled, it gets included in the baseline. Each output also carries a placeholder column where the new permission set's record ID will go.

1 - Create New baseline permission set starts from the selected profiles. It removes the fields that aren't permissions - IDs, user license, audit fields, description - then unpivots the rest, keeps only the enabled values, deduplicates them, and pivots the table back to its original shape. The result is a single row holding the union of system permissions across every selected profile.

2 - Create Object Permissions applies the same unpivot, filter, deduplicate, and pivot treatment to the Object Permissions table, after an inner join limits it to the selected profiles. The result is one row per object holding the union of enabled object permissions across those profiles, sorted by object name for review.

3 - Create Field Permissions does the same thing for field-level security.

4 - Create Permission Set Tab Setting handles tab visibility. It's simpler than the others - it keeps the tab settings where visibility is DefaultOn for any selected profile, deduplicates by tab name, and sorts the list.

5 - Create Setup Entity Access covers a lot of territory. The query deduplicates the SetupEntityId values across the selected profiles, then left joins that ID to each table in the subfolder to bring in details about what each row is for applications, connected apps, Visualforce (Apex) pages, Apex classes, custom permissions, and org-wide email addresses.

A caution on this one - I'm honestly unsure exactly how a couple of these setup entity types work, and I'd urge care before inserting any metadata you're unsure about. Some specific gaps I can name:

Organization-wide email addresses are included in the model, but entries associated with Profiles don't show up in that table so its ineffective for the purpose of consolidating them. I don't know how or if it's possible to query which Profiles are configured as Allowed Profiles on the Organization-Wide Email Addresses setup screen. In the absence of being able to query that, consolidating it is outside the data model's scope.

The Flow Definition table is invoked as a source query, but not incorporated in the model. I'm unsure which ID field to merge the entity ID on.

Records in the Apex Page Info table have 15 character IDs but corresponding entity IDs are 18 characters, requiring one of them to be transformed to match them up.

I did not identify or include linked tables for these Setup Entity Types: BotDefinition, CustomEntityDefinition, EmailRoutingAddress, ExternalClientApplication, ExternalCredentialParameter, MessagingChannel, ServiceProvider, or StandardInvocableActionType. I don't know how to query those entities or if it's possible to query them.

Implementation

The data model is hopefully a useful tool for wrangling all the permissions across numerous profiles, but to finish converting that baseline into one or more permission sets you'll need to run bulk insert operations.

The tables that load into the model - available via Table view on the front end of Power BI - are staged to enable this workflow:

0 - Profiles selected - check here to review which profiles are being consolidated

1 - Create New baseline permission set - copy this table, paste into Excel, save as a CSV, Insert as a new Permission Set

2 - Create Object Permissions - copy this table, paste into Excel, save as a CSV, Insert as new Object permissions

3 - Create Field Permissions - copy this table, paste into Excel, save as a CSV, Insert as new Field permissions

4 - Create Permission Set Tab Setting - copy this table, paste into Excel, save as a CSV, Insert as new Permission Set Tab Settings

5 - Create Setup Entity Access - copy this table, paste into Excel, save as a CSV, Insert as new Setup Entity Access items

Once the new baseline Permission Set is established, I recommend automating user assignments by creating a simple User Access Policy that applies to any Active user. If needed, enable user access policies in User Management Settings first.

Getting permissions out of profiles is a big step towards streamlining and modernizing an SFDC. Here's hoping this resource helps you get that done.

Generative AI disclosure

A few months back I wrote about building a hyper-efficient review queue interface in Salesforce, and tucked inside that post was an opinion I stated emphatically - that when you're integrating data into Salesforce from a third-party system, the raw feed should always land on a custom object first and get parsed out in-platform second.

Integration architecture is one of my favorite things to nerd out about, and that principle of always landing a data feed onto a custom object is something I come back to at the start of every build.

Why target a custom object?

The biggest reason is control over your automation. When an integration writes directly to Contacts, Opportunities, or other objects, it can easily trip other automations that are already tied to them.

Untangling user actions from integration events gets messy fast. And when your writes cascade through all that existing automation, you run a greater risk of encountering governor limit errors.

Landing the data feed on its own custom object starts you out with a clean slate. The creation of that record is an unambiguous trigger that means "new data arrived from the upstream system," and from there you decide deliberately what happens next.

It also keeps the raw data untouched. No editing in transit, and no transformation logic hidden inside your middleware. Everything that happens to the data happens in-platform, declaratively.

The goal is a true swivel-chair integration - you take the export from one system and drop it into the next exactly as it came out, with no manipulation in between. Salesforce does the rest.

An immediate benefit is a permanent log of the original data feed. The custom object contains a de facto snapshot of exactly what the upstream system sent, before any parsing, matching, or merging happens downstream. When questions arise later, for instance after a Contact gets merged, that log is a valuable tool to trace data back to its source.

Add a lookup field from the custom object to the Contact or Opportunity or other record it produces. Populating that lookup becomes your audit trail and signal that the automation run completed - more on that below.

Finally, keeping the landing zone simple keeps your upstream options open. Because the destination is just a custom object, you're not married to any particular delivery mechanism.

You can start with someone periodically importing a CSV, graduate to a scheduled connector pushing records in real time, and swap between the two later as needed without re-architecting anything. The integration logic in the SFDC doesn't care whether a row arrived by manual import or an API call.

That flexibility helps you meet a client where they are today.

A natural fit for third-party webforms

One of the most common versions of this is receiving data from a third-party webform service. The form platform drops each submission into your custom object as a new record.

A benefit is how little the form platform has to do. It doesn't need to match contacts, create opportunities, or understand your data model - it just writes a record to one object, and the parsing happens in Salesforce afterward.

Because the bar on the webform side is that low, you only need a platform with minimal Salesforce integration capabilities. That opens up your options - lots of form tools can jot a record into a custom object even if they can't do much more, and any of them will do the job.

The easiest way to get started - Object Creator

If this sounds like more up-front work to configure than pointing a connector at a standard object, here's the good news - Salesforce makes the first step easy. The Object Creator tool spins up a custom object directly from a spreadsheet.

Export a CSV from your upstream system, hand it to Object Creator, and it generates a custom object with fields that line up with your columns. In a few minutes you've got a landing zone that mirrors the shape of your source data - which is exactly what you want, since the idea is to land the data as-is and do the interesting work in-platform afterward. I like to include records from the export when doing this, use those records to debug flows, then wipe them all before go-live.

Another benefit to building the object this way is that because the field names are generated from the CSV, they exactly match the column headers in your data export. So if you upload that export later via Data Loader or the Data Import Wizard, the fields should map automatically each time.

My preference is building a separate custom object for each import you bring in. A dedicated object for each data source gives you a clean log of exactly what that system sent, keeps its fields aligned to that one source, and leaves nothing to reconfigure from one run to the next. If you have multiple sources, then each custom object's record-triggered flow should invoke common subflows to consistently create the downstream Opportunity or Contact or other record.

Almost every field on the custom object should be plain text, minimizing the risk of invalid data triggering errors on the way in. Date and number fields can make sense when you're certain those values will always conform, but the safest default is to land the data as text and handle field type conversions when the record is parsed out in-platform.

From there you can add the lookup field back to your downstream object and start building the automation that parses records out to where they belong.

Parsing the data out with a flow

Once records are landing in the object, moving them where they belong is the job of a record-triggered flow on that custom object. In cases where a single integration is sending records to multiple custom objects, it's wise to use delayed paths that wait to process automations until after all the data arrives.

From there the flow follows your decision tree. Find or create the Contact - is this email already in the system? if not, do you want to make one or queue them for review? Then create the Opportunity or other record and set the lookup field. It usually doesn't take a hundred steps; a couple of well-thought-out decisions covers it.

Delayed paths are your friend. In addition to helping wait for all data to arrive, they separate the next bit of automation from the original flow interview and reset your governor limits.

Tracking integration status and catching failures

I mentioned adding a lookup field. It turns your custom object from a log into a monitoring tool.

Every integration record should end up with its lookup populated, pointing at the Contact, Opportunity, or whatever downstream record the automation created or matched. A populated lookup means the run succeeded, and a blank one means something went wrong. The health of your integration is now queryable with a single filter.

I often recommend a scheduled report of integration records created yesterday where the lookup is blank. Set that report to only send when it isn't empty - Salesforce lets you attach a condition to a report subscription so it only emails when rows exist. On a healthy day you hear nothing. The morning after something breaks, there's an email waiting with links to the records that need help.

From import to integrate, in the real world

Here's a concrete example of how it played out on one of my builds. A client was launching a new program and needed data from the program management software they'd adopted to land in Salesforce.

At the outset they didn't strictly need real-time integration, and turning on the vendor's Zapier-based integration would have meant paying to upgrade that software to a tier that included it. That wasn't a cost that made sense for a program still finding its footing.

So we started simple. Data got exported from the upstream system and imported into a custom object in Salesforce, on a cadence that matched how the program operated, with the in-platform automation parsing records out from there.

No real-time plumbing, no upgraded software tier, just a clean landing zone and the declarative logic to route the data onward. It did the job.

Fast forward a few years and that program is thriving. It's grown to the point where real-time integration is worth the effort now, and we're working towards pointing Zapier at the same custom object that's been receiving those manual imports all along.

This is the payoff of the approach. Because we targeted a custom object, the upgrade is almost entirely an upstream concern - the destination and the parsing automation don't need to change.

The takeaway

In summary, resist the temptation to point your integrations straight at the objects you ultimately care about, and give them a custom object to land on instead.

You get automation that's easier to isolate, a log of the original data feed, a simple way to catch failures, and the freedom to change how data gets delivered without tearing anything down.

Generative AI disclosure

Two software tools that I use a lot to deliver value from my consulting work are Salesforce and Power Query in Power BI Desktop. The latter is not very well known among my Salesforce peers, but I think it's a hidden gem of a powerful free tool for data analysis and scripted transformations. This blog post will demonstrate a use case and share a model that I built for applying this tool to the task of consolidating User Profiles in an SFDC.

Before diving in, here is the briefest of primers on Power BI Desktop for if you're unfamiliar: it's a free Microsoft download that's analogous to Tableau Desktop, which Salesforce recently released a free edition of. Both software packages enable building data visualizations which can be published to their respective (not-free) cloud services. One key difference however is that Power Query is included in the free Power BI Desktop product, whereas the analogous Tableau Prep Builder requires a paid license - it is not included in the free edition of Tableau Desktop. Another key difference is that Power BI Desktop only runs on Windows, whereas Tableau Desktop is available for both Windows and Mac.

Also note that there is a Power Query for Excel, in addition to Power Query for Power BI Desktop. I don't know all of the differences between them as I only work hands-on with the latter, but if you're searching documentation pay attention to this distinction.

Final note - this data model is configured to support collapsing permissions from multiple profiles into a single surviving profile. The model crunches data and then its outputs are copy-pasted to Excel, where they're reviewed and turned into CSV files staged for bulk update and insert operations. There's opportunity in that process to make decisions about what belongs in the Standard profile and segment things out to permission sets as appropriate. This model does not fully unwind permissions from profiles, but that's a similar goal that this model could be adapted to serve.

The rest of this blog post walks through how the Power Query model works.

Data model walkthrough

Up front, here are the downloadable resources: Power Query template and related spreadsheet for Salesforce Profile Permissions reconciliation (please download the newer model from this blog post instead). You will need Power BI Desktop installed on a Windows machine to open the template. On first run it should prompt you to authenticate and connect to a Salesforce environment. Then you'll need to modify a couple included parameters per your environment. It should basically just work after that, producing files that you can copy-paste out to the included Excel template for review, and then parse those out to CSVs to push through Data Loader to implement the changes in the SFDC.

Beginner's tip - if you've never used Power Query before, the basic thing to know is that it looks and feels a lot like other Microsoft products, much of the interface is identical to Excel. The key difference is that every time you take a declarative action, it generates an M script step for that action. So as you manipulate data declaratively, you build out a transformation script in that process, which makes it repeatable. You can refresh inputs later to run the data through the same steps. There's a lot more that it opens up in terms of scripting tricks and advanced functionality, but at a basic level it can be understood as a tool which enables the same kind of data transformations that are often handled in Excel.

There are 18 queries built into this model, organized by folders.

Parameters

I don't think of parameters as queries, they're just placeholders for values referenced elsewhere in the model. But they're technically queries, so they count.

The first parameter is used in one place, to filter Profile names that are relevant to the consolidation.

The second parameter is the record ID of the Permission Set that corresponds to the surviving profile. This is referenced in a numerous places in the model.

Source data

These five queries are the source data pulled in via the Salesforce connector. That's literally all there is to them, because I prefer keeping my source queries clean and organized in one place, to be referenced as starting points by other queries which execute transformations.

Transformation

Now we're getting to the good stuff! These six queries reference and transform the source data. Let's walk through them in order.

The Profile selection query has just one job - it filters the Profiles table down to just the relevant Profiles that need to be consolidated. Note the use of the ProfileNameFilter parameter here. Also note that for this use case the profiles I needed to work with all start with the same text string. If your use case involves more granular profile name selection, then modify the Filtered Rows step in this query accordingly.

The Join Profile to Perm Set query also has just one job - it joins the selected Profiles to their corresponding Permission Sets. In the Salesforce tables, each Profile record has a 1-1 relationship with a corresponding Permission Set record. The Permission Set's name is X{ProfileId} and it does not appear in the permission sets list in Setup. The Permission Set's ProfileId field joins to the Profile, and other permission tables join to this Permission Set ID on their ParentId field.

The Joined tables clean query references the previous query, expands the Permission Set ID field, and removed other un-needed fields. The only reason to do this in a subsequent query rather than in the previous one is because there's a separate validation query (covered later) which also references the first one. Power Query's ability to branch off in multiple directions from any given transformation is one of it's most powerful features, and also a major consideration in how you architect your script (always branch outward, avoid looping back, the performance implications are very real).

The Object Perms inner join query is where the real magic starts to happen. The mechanics of the query are documented in the code comments. The result of this query is a clean list of all enabled object permissions across all relevant profiles.

The Field Perms inner join query is almost identical to the object perms query, except that it produces a clean list of all enabled field permissions across all relevant profiles.

Finally, the Permission Set Tab Setting consolidation query does the relatively simpler job of filtering down to just where visibility = DefaultOn.

Transformation

The Validation folder contains just one query, DIFF check - Join Profile to Perm Set which branches off the Join Profile to Perm Set query from earlier.

This query exists solely to verify an assumption that all of the system permissions listed in the Profile records are identical to the system permissions listed in their corresponding Permission Set records. This query enables exporting those value sets side-by-side to line up and analyze in Excel. There's no reason to think they would be different, since this not user managed and just happens automatically under the hood, but I like to validate assumptions to be safe. It checked out as expected and we'll move on.

Update files

The four queries in this folder are the model outputs, which get copy-pasted out to Excel and then staged there to implement in Salesforce via Data Loader.

The Reconcile Profile permissions query branches off from the Join Profile permissions query, expands the Permission Set IDs into the table, and re-orders some fields. See the instructions in the readme tab in the provided Excel file for how to take the output of this, apply a formula which identifies instances in which each permission is either never present or always present (don't need to update those), isolate the rest, then manually audit them to indicate which permissions you want to update the new Standard profile to incorporate.

The Stage Standard Object Permissions query starts at the Object Permissions source table, filters that down to just the surviving Standard Profile that you're consolidating into, prepends "existing" to the permissions currently set for that profile, then executes a Full Outer join to the Object Perms inner join query that brings in the consolidated enabled permissions from all relevant profiles alongside the existing permissions for that profile. Columns are then reordered to enable viewing existing permissions alongside changes, a calculated field is added to flag where there are vs. are not changes, and the table is filtered to only those rows where there are changes. Finally, null values in the ParentPerSetID field are replaced with the ID of the Permission Set which corresponds to the surviving Profile, for the purpose of staging new record inserts for missing permissions.

See the readme tab in the provided Excel file for how to take the output of this and stage it for review. Once it's been reviewed and you're ready to implement it, then you'll sort it by ID so that it can be parsed out to one CSV for existing records to be bulk updated, and another CSV for missing records to be bulk inserted.

The Stage Standard Field Permissions query is almost identical to the last one, just for field permissions instead of object permissions.

Finally, the Stage Standard Tab Settings query does a very similar thing - starts with the source table, filters to the surviving Profile, joins in the consolidated tab visibility, filters down and outputs a file that can be reviewed and then used to consolidate tab visibility in the surviving Profile.

I feel strongly that the best practice for how an integration should work, specifically when integrating constituent data into Salesforce, is that the raw data feed should land in custom object records first and get parsed out to the downstream objects in-platform second. System integrations that send data directly to Contacts, Opportunities, or other common objects, can be fraught when those objects trigger other automations, and if those records are later modified or merged they then become more difficult to trace back to the data source.

Many integrations are not architected that way though, in part because data storage limits used to be lower and Flow has become a so much better tool over the years. But I maintain that in 2026 if you're building a new data integration into Salesforce, notwithstanding complex Data Cloud powered identity resolution scenarios, targeting the data ingest at a custom object and then configuring integration logic declaratively in-platform from there is the way to go.

Landing your data feed on a custom object makes the automation triggers easier to isolate and manage, and it produces a de facto permanent log of the original data. A custom object field looks up to the Contact, Opportunity, or other object created. This provides an audit trail and serves as the signal that the automation run completed successfully. A scheduled exception report of these records where the lookup is blank can also be leveraged to monitor failures.

I built out new data integrations into Salesforce for two client nonprofit organizations in the past couple of months. One client uses Salesforce for educational program management, and needed to integrate Zoom Webinar registrants. The other client uses Salesforce NPSP for donor management, and they needed to integrate a fundraising platform called Haku.

Both of those software vendors happen to offer Salesforce integrations that follow my preferred approach of sending data to custom objects to be parsed out, and neither one extends beyond that, so these were both projects that called for building that last mile plumbing.

Note that this blog post is not a full writeup of how to integrate either Zoom or Haku with Salesforce. I can say a lot more about them, and if you're interested in contracting for that service then please get in touch, but this blog post specifically is about how I leveraged a trick learned from Skye Tyler's 2024 Midwest Dreamin' presentation to use a Salesforce Labs project called Data Fetcher along with a data table screen flow component to build a super neat interface that's key to both integration builds.

Another best practice that I've learned to appreciate is don't over-automate things. Integrations that create new Contacts or Accounts can too easily become a significant source of duplicate records. A human needs to stay in the loop to manage data quality, whether by catching duplicates proactively in a review process, or cleaning them up reactively later. And the correct approach depends on the situation.

In my first client's case, their Zoom webinars are part of an education program that primarily engages staff from other organizations. The client uses a non-household account-contact data model, about 3/4 of their webinar signups do not match an existing Contact on email address, but most of the organizations they work for have existing Account records in the CRM. For this case an important data quality consideration for staff was to ensure new Contacts get associated with the correct existing Account records.

In my second client's case, all of their Haku donations right now are coming from donors who are sponsoring runners on their Chicago Marathon team, and the organization plans to soon start using Haku for other online fundraising as well. While most marathon donors are not already in the CRM, a much greater share of other online fundraising is expected to come from their established donor base. For this case, if a marathon-associated donor's email doesn't match an existing Contact, the integration auto-creates a Contact for them for the sake of efficiency. But for other online fundraising, where they generally take a higher-touch approach with a more established constituency, we went with a review process of queued donations for the ones that don't match an existing Contact email. The key data quality consideration here is to ensure new gifts from established donors are properly credited to their existing Contact record.

Both of these integration builds called for configuring an interface that empowers humans to efficiently work through an integration data review queue. To serve that need I dug up notes from Skye Tyler's presentation, Elevate Your Flows with Data Fetcher, and applied them to building something that's easy to use and pleasantly hyper-efficient.

The trick is simply this - Data Fetcher can utilize a SOQL statement contained in a formula element that incorporates dynamic input from a text field on the screen. When the end user modifies the value in that text field, the SOQL query dynamically changes and the results rendered in the data table also dynamically change as the user types.

I applied this to a screen containing a data table with Contact fields that align to the incoming data points (name, address, email, phone). The SOQL query is structured to find any partial match of the search string across all of those fields like so:

--

"SELECT Id, Name, MailingAddress, Email, Phone FROM Contact WHERE Name LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or Title LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or MailingStreet LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or MailingCity LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or MailingState LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or MailingPostalCode LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or Email LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + "or Phone LIKE '" + {!Text_PercentageSign} + {!SearchString_Contact} + {!Text_PercentageSign} + "'" + " LIMIT" + {!SOQL_Limit}

--

NOTE - the limit on the end of that query is important! In an early iteration without that, I learned that it could break Salesforce in a really unexpected way - unintentionally loading an unlimited query for a blank search string across all those fields caused the browser to hang repeatedly during flow debugging until I applied that limit. Which of course I had missed a warning about in the presentation notes.

The end result is a user interface that offers a single hyper-efficient search box to dynamically query all fields in the table against existing Contact records. The search field is populated with Full Name by default to help quickly determine whether an existing contact by that name exists. If an existing contact is selected, the flow then runs the rest of the automation to parse the queued record out to a new donation opportunity or other record related to that contact.

If no existing Contact is identified, then staff can instead change to Create Contact from the default Find Contact, which hides the data table and shows new contact input fields. Each field is pre-populated with information from the queued record, so if no changes are needed then just click next and the Flow finishes the work of creating new records. In the case of my first client there's an additional review step to find and select, or create a new Account record to associate the new Contact with.

And voila, from the user's perspective they get a simple tool to very efficiently query Contacts for an existing match, or very efficiently create a new Contact if no match is found. Clicking next also completes the process of creating the donation opportunity record associated with the contact, and navigates the user back to the pending donation queue to work through the rest.

Scrolling up and looking at this blog post, I realize it took a dozen paragraphs to get around to describing the technical solution in the headline. Talk about burying the lede! Really though, the rambling narrative was intentional and meant to illustrate a point that proper solution design involves much more than neat technical tricks. You need to understand the use case, priorities, and considerations before jumping to solutioning. The work of understanding and defining what problem needs to be solved is more important and often more valuable than the engineering phase which comes after. I've learned that leading project discussions with empathy, curiosity, and by listening first is a great approach that sets your team up well to achieve positive outcomes.

---

Postscript

I want to add a personal postscript to this blog entry. I live in Minneapolis, a city where we lived under federal occupation for the past two months with three thousand Department of Homeland Security agents deployed here (roughly double the size of every metro area police department combined). The Trump Administration claims that Operation Metro Surge was about "immigration enforcement targeting the worst-of-the-worst," but they are well known liars. As others have said more eloquently, what we witnessed here is a cruel ethnic cleansing campaign targeting our immigrant neighbors, along with violent repression of dissenters in our community.

Wednesday, January 21, 2026 was a rough day. That morning a friend randomly witnessed and shared video of a boy about ten years old being led away from his mom and detained by federal agents. By that afternoon I was seeing reports of Commander Bovino parading through our neighborhoods with a twenty SUV caravan, piling out at gas stations with embedded media in tow and lingering as angry crowds formed. While that community provocation press tour was happening, about twelve blocks away, ICE agents rammed a vehicle containing two teenage immigrants, ripped them out and arrested them while of course another loud crowd of mad observers formed. As often happened, agents got rough with the crowd and assaulted them too, it's where this photo of them holding someone down and spraying chemical irritant point blank in their face was taken. Bovino's caravan then showed up near that scene and crawled through the neighborhood, escalating things further in a well-publicized confrontation that ended with DHS unleashing a large amount of tear gas in a residential neighborhood.

That day was rough, but not isolated, and not even the worst of it. Three days later a few blocks away from that scene, Alex Pretti was publicly executed, becoming the second person after Renée Good to be murdered in the street in my community last month by anonymous, unaccountable federal agents.

But that Wednesday was also my first day participating in my first ever Salesforce Nonprofit Commons community sprint, an online event I had registered and cleared my calendar for a couple months prior. And I didn't get much sprinting done, I spent most of that day muted off camera, distracted and upset while transfixed by first-hand accounts of terrible scenes in my community. And the juxtaposition of that situation to working with this software company's product isn't lost on me.

I'm well aware of Salesforce CEO Mark Benioff's heel turn from evangelist for the notion that "business is the greatest platform for change" to someone who posts selfies from state dinners honoring murderous dictators and cracks callous jokes about ICE agents menacing his own international employees attending a confab.

Numerous people in the nonprofit Salesforce community have written cogent descriptions of the heartbreak and collective disappointment people feel, holding the tension of feeling betrayed by company leadership while hanging on to the community that people fostered around the practice of leveraging this software to empower organizations to make the world a better place. It's very true that while Salesforce's CEO cozies up to our authoritarian regime and his team tries to sell the government more software to optimize ICE hiring funnels for ramping up their operations, there are nonprofits using the same software as a case management platform for immigrant legal services. I know of an organization whose use of Salesforce included tracking cases in ICE detention facilities, collecting valuable data in the process about detainee conditions and treatment, until that org's operations were gutted by Trump administration budget cuts and stop work orders. And there are so many more examples, literally tens of thousands of nonprofit organizations run their operations on Salesforce.

I contributed to this statement released by my employer in response to Benioff's pre-Dreamforce comments in October, and stand by those words in this moment too. CRM implementations are big investments in software that organizations often use for a decade or more. If your organization uses Salesforce now and the software serves your needs well, making a reactive decision to scrap it would be disruptive and could risk harming your mission. But if you work for a nonprofit considering a new Salesforce implementation, or making contract renewal decisions about which products to continue using, it's always appropriate for organizations to consider a vendor's reputation and values alignment among your criteria.

The scenario is an events center that uses Blackthorn Events and Salesforce for their day-to-day operations. Most of their event registrations are processed in their own system, but some events they host are run by partner organizations who handle attendee registration independently. In those cases the partner organization sends the event center a list of registered attendees to upload, and the event center later issues one large invoice to the partner org for attendee lodging and other charges.

Administering these attendee groups and invoicing them back to the partner organization has been difficult, and we are on a path to improve this. The Blackthorn Events data model contains an attendee group object which it automatically utilizes when multiple people are registered in a single web transaction. That object provided a nice starting point to extend to more robust attendee group management functionality.

This was a technically challenging project that turned out to be the most complex thing I've yet built with Salesforce Flow Builder. The resulting Flow leverages brand new features and overcomes difficulties of working with a managed package's objects. Note that custom automations are specifically outside of the scope of Blackthorn support, so they cannot assist if you decide to leverage this in another Blackthorn org. The tricks and lessons in this post should be applicable to other non-Blackthorn use cases as well.

A reason why it's important to note all that up front is because before building this Flow, I questioned whether it was a good idea to build. Our consulting team inherited a client environment where the existing attendee import process was to use the data import wizard to upload a list of Attendee records with a custom field called Imported Attendee marked TRUE. That boolean value activated a record-triggered flow which would create the Attendee's associated Line Item, Invoice, and Transaction records.

That system fell short largely because of the governor limit errors that staff regularly encountered. It was initially my opinion that we'd be better off improving the existing system's efficiency and implementing Apsona as a better pre-built upload tool with batch size control to mitigate the governor limit errors. But my opinion changed when I saw the added value in how a Flow could accomplish tying together the attendee group upload, group deposit, and group payoff processes. In other words it made sense to do once it addressed a larger business process need than just the upload function.

An early build paired those three functions, but still relied on a CSV upload targeting the Attendee object to salvage the existing record-triggered Flow. That actually did work but only with the batch size set to 2 and there's a 30+ second wait between batches. That poor performance was the deal-breaker which brought me around to deprecating the record-triggered flow and rebuilding its functionality within the screen flow.

The core performance issue in the record-triggered flow was that it chained together multiple write operations on managed package objects. Each of those objects trigger some rollups and other custom automation that we know about. And each of those objects also trigger some Apex code that we don't have any visibility into. But the cumulative impact of it all counts towards the flow interview limits. And I don't think Salesforce bulkifies downstream write operations which are initiated when a record-triggered flow is invoked multiple times by a bulkified write of a collection of new records.

So I took on rebuilding the record-triggered flow functionality within this screen flow, staging each object (attendee, invoice, and line item) within a collection to bulkify the database writes for better performance, and utilizing batch sizes and pause screens throughout to stay within limits. I created a custom object to target the upload to, designed the system to write related object IDs back to the custom object record to track upload progress, and built an additional branch of functionality to resume an upload using those records if needed.

Screen flow user experience

Before diving deep into how the flow is built, here's a tour of what the screen flow does and how it works for the person running it. I like to think that the simplicity of this interface belies the complexity going on behind it.

The screen flow is designed to launch from a button on the Event screen, which also passes the Event ID into a parameter.

The flow first executes a lookup for an existing attendee group. In our use case we are only ever expecting to manage one corporate group per event, so if a group is found then it is selected. If no group is found, then the user is prompted to input an Account ID to create a new group. When a new group is created, a group invoice is initialized.

The four actions that the user can take are all connected back to selected attendee group or main invoice:

Begin attendee upload

Resume attendee upload

Record group deposit

Record group payoff

Begin attendee upload

When a user selects Begin attendee upload they are first presented with a screen detailing the exact column headers that need to be in the CSV file.

The user uploads their CSV file and the next screen shows a preview of records in the file. The file may contain an Event Item ID column, but if that's not included then rows which need an event item assigned are displayed along with a dropdown menu to select an event item to assign them.

After all rows have an event item assigned, then the user selects the batch size to proceed with creating attendees end clicks next.

If the number of rows in the file exceeds the batch size then a pause screen will appear informing the user of the number of records created and prompting them to click next to continue. This will continue until the batch size is complete.

After this process is completed for attendees, it's repeated for invoices and then for line items.

Resume attendee upload

If an attendee upload is interrupted for any reason, it might result in a situation where some attendees were created with invoices but no line items, or attendees with no invoices at all, or a batch where some attendees were created and others weren't. All of those scenarios would be bad, so there is a resume attendee upload feature.

When a user selects Resume attendee upload, the flow queries for any attendee upload temp objects which are missing a corresponding attendee, invoice, or line item object. If any are found, then those records are loaded into the begin upload branch of the flow to finish processing them.

Record group deposit

Group deposits are pretty straightforward. When the user selects Record a group deposit, they are taken to a screen prompting them to record the amount of the deposit, a deposit memo, and indicate whether the deposit was paid or needs to be invoiced.

After clicking Next to continue, the flow creates an event item for the deposit then a line item attached to the invoice. If the user indicates that the deposit was paid, then the flow also creates a transaction. If the deposit was not paid then the invoice link displayed on screen can be sent to facilitate paying by credit card.

Record group payoff

Group payoffs are recorded after the event takes place, when the organization is ready to generate a final invoice for the group.

On the first screen in this interface the user is prompted to select any available deposits and attendees in the group that they would like to apply or transfer to the main invoice. A batch size selector allows for controlling the number of credits/charges to transfer in each batch.

For any deposits that are selected, the flow reverses the line item on the main invoice. It's honestly a little bit awkward that there is a line item for the deposit amount in the first place, given that it shouldn't be recognized as a charge - just as a payment credited towards their eventual final balance. But it's necessary create a deposit line item for the deposit amount in order to support invoicing deposits via Documentlink. So we set a line item for the deposit amount, back that line item out at the point when it's applied, and have our rollups at the event level set to make sure deposit line charges are excluded from invoice totals where that's necessary.

For all attendee line items that the user selects, a loop sends each one through a subflow that reverses each line item on their respective invoices and tabulates the total amount of attendee charges that needs to get added to the main group invoice.

When that process completes, the user is presented with a screen showing the total amount of attendee charges to be added, and is prompted to add the total amount of any miscellaneous charges (typically audiovisual and catering) to the invoice.

After clicking Next, the total amount due of the invoice is shown on screen, and the user is prompted to enter a payoff memo along with the total amount being collected and any amount being written off.

Screen flow architecture

This screen flow has a lot going on, and it's a little bit intimidating when you pull back and look at the whole thing. It isn't as bad as it looks though, much of this is repetitive, and I'll break it down piece-by-piece.

Flow intro

The first part of this flow loads in the Event record using the ID passed into the flow, then searches for an existing attendee group associated with the event.

If a group is found then it makes sure the group has a main group invoice (initializing one if missing) then assigns this group to the Var Group variable used later in the flow.

If no group is found, then the user is prompted to input an Account ID to create a new group. A validation check ensures that an 18 digit string starting with "001" was entered, then it creates a main group invoice billed to that account, sets the invoice on the Var Group variable, and writes that variable to the database.

With these initial steps completed, the user is then presented with four options:

Begin attendee upload

Resume attendee upload

Record group deposit

Record group payoff

Begin attendee upload

A custom object called Attendee Uploads is employed. When the user uploads a CSV file, that custom object is targeted. The uploaded records are assigned to an Uploaded attendees collection and that collection is looped through.

Each row is assigned the Event ID and the flow start timestamp is set as a batch identifier. Then the row is evaluated to determine if it has or needs an Event Item ID. Rows that do and do not need an Event ID are assigned to separate collections. This decision step also looks for the absence of the Last Name field, which if found is taken as evidence of a blank row that is filtered out and not assigned to any collection.

The next steps deal with assigning missing Event Item IDs. A Record Choice Set serves a dropdown of all of the valid event item types for the event. The user is prompted to choose an item, select the rows to apply it to, then repeat until all rows have an Event Item ID assigned. If all rows already have an Event Item ID, then these steps are skipped and the Attendee Upload objects are written to the database.

Now that the attendee uploads are in, the flow proceeds to looping through them. The first decision, which evaluates if an attendee is already associated with the upload row, only applies to resumed uploads. No attendees already exist for new uploads, so the attendee record fields are assigned in the following step and the staged attendee record is then assigned to the collection.

At this point there's a Pause Interval decision. This is where the flow evaluates if either the batch interval or the end of the batch has been reached. If it's time to pause, then a pause screen is displayed which commits the flow interview (writing attendee records in the collection to the database), the user clicks Next to continue, then the loop proceeds to staging further attendee records until either the next pause or the end of the batch. A second loop and write operation then loops through the new attendee records to stage an update batch to write attendee IDs back to the Attendee Upload records which created them, then update those Attendee Upload records in the database.

While useful for mitigating governor limits, there are drawbacks to saving work to the database mid-operation. If an error occurs or if the user simply navigates away from the flow prior to completion, the dataset they're uploading could be left in an inconsistent state. For that reason, each step where the attendees, invoices, and line items are committed to the database is accompanied by a step that updates the AT upload objects with those new IDs, and both of these write operations happen within the same flow interview with a roll back function included to help ensure the IDs tracked on the Attendee Upload object remain accurate in case an error occurs.

After the attendee creation loop is completed, there's a little bit of collection juggling done before moving on. The attendee record creation was bulkified by batching them into a collection and processing a single write operation. That's a vital practice for flow performance, but comes with the wrinkle that the newly created object IDs aren't immediately available in the flow the same way they are when objects are created individually. I get around that by using the batch identifier to empty the records out of the collection and then query the newly created records in order to reload them into the collection. It's also a nice trick for being able to reference the contact or account record that Blackthorn links with the attendee record after save.

The remainder of this branch pretty much repeats the process from attendee creation - the attendees are looped through to create invoices for them, then the invoices are looped through to create line items. At the end of it, the user is returned to the menu asking what they'd like to do next.

Resume upload

The resume upload function exists to ensure that all attendee uploads are complete and accounted for. The first step searches for any pending uploads - attendee upload records where the event ID matches and the related ID either attendee, invoice, or line item is null. If none are found, the user is simply returned to the menu. If any are found, they are looped through twice.

The first loop evaluates if the object has a related attendee but no related invoice, and for each of those records it gets the attendee record and adds it to a collection. A pause interval of 50 is built in.

The second loop evaluates if the object has a related invoice but no related line item, and for each of those records it gets the invoice record and adds it to a collection. A pause interval of 50 is built in.

Those two loops exist to patch in related object IDs to the same collections used in the Begin attendee upload branch, before incomplete attendee upload objects are passed to the Preview attendees step in the Begin upload branch. The rest of the workflow for completing an upload is identical to a new upload as the flow proceeds through the new upload branch.

Record group deposit

Record Deposit is the shortest and simplest branch of this flow. The user is presented with a screen where deposit information is entered, the flow then creates an event item, a line item (on the group invoice), and optionally a transaction record and note on the invoice. The user is then shown a confirmation screen and taken back to the menu.

Record group payoff

The Record Payoff branch ties it all together at the conclusion of an event. The first step in this branch is a query that loads in all line items associated with the attendee group. Collection filters are then applied to parse those line item records out to a deposit collection and an attendee lines collection. A screen is then presented to the user enabling them to select which of those records to proceed with.

Selected objects from each of the filtered collections are then looped through independently. In both cases a Subflow is invoked which creates an offsetting line item to effectively zero out the charge. The difference between the loops is that the deposit loop sums up the amount to display as a credit, whereas the attendee lines are tabulated to sum up a new line item amount to be added to the group invoice.

The user is then presented with a confirmation screen that states the number of line items charges transferred, the sum of those line item charges, and the deposit amount applied. A fill-in field allows the user to input additional charges (audiovisual, catering, etc.).

When the user clicks Next, the flow creates an event item and line item for the room charges, then an event item and line item for the additional charges.

Finally, the Payoff line item screen displays the total charges on the invoice minus the deposit to arrive at an invoice total due. The flow may be ended here and the Documentlink invoice link sent to collect payment. Or if payment was already submitted, then the user may toggle on Create payoff transaction and additional fields are exposed to input the payment amount, payoff memo, and writeoff amount (if applicable).

When the user clicks Next then the flow either completes if no payment was entered, or creates the payoff transaction, updates the invoice with the memo, and creates a writeoff transaction if applicable.

When this is all done the main group invoice contains multiple line items for attendee room charges, additional charges, group deposit, and writeoff amount. Transactions for the deposit and payoff are applied against these charges.

My colleague Molly Mangan developed the plan for deploying this to handle nonprofit organization CRM import operations, and she delegated a client buildout to me. I’ve built a few iterations since.

I prefer utilizing a custom object as the import target for this Flow. You can choose to upload data to any standard or custom object, but an important caveat with the upload LWC component is that the column headers in the uploaded CSV file have to match the API names of corresponding fields on the object. Using a custom object enables creating field names that exactly match what comes out of the upstream system. My goal is to enable a user process that requires zero edits, just simply download a file from one system and upload it to another.

The logic can be as sophisticated as you need. The following is a relatively simple example built to transfer data from Memberpress to Salesforce. It enables users to upload a list that the Flow then parses to find or create matching contacts.

Flow walkthrough

To build this Flow, you have to first install the UnofficialSF package and build your custom object.

The Welcome screen greets users with a simple interface inviting them to upload a file or view instructions.

Toggling on the instructions exposes a text block with a screenshot that illustrates where to click in Memberpress to download the member file.

Note that the LWC component’s Auto Navigate Next option utilizes a Constant called Var_True, which is set to the Boolean value True. It’s a known issue that just typing in “True” doesn’t work here. With this setting enabled, a user is automatically advanced to the next screen upon uploading their file.

On the screen following the file upload, a Data Table component shows a preview of up to 1,500 records from the uploaded CSV file. After the user confirms that the data looks right, they click Next to continue.

Before entering the first loop, there’s an Assignment step to set the CountRows variable.

Here’s how the Flow looks so far..

With the CSV data now uploaded and confirmed, it’s time to start looping through the rows.

Because I’ve learned that a CSV file can sometimes unintentionally include some problematic blank rows, the first step after starting the loop is to check for a blank value in a required field. If username is null then the row is blank and it skips to the next row.

The next step is another decision which implements a neat trick that Molly devised. Each of our CSV rows will need to query the database and might need to write to the database, but the SOQL 100 governor limit seriously constrains how many can be processed at one time. Adding a pause to the Flow by displaying another screen to the user causes the transaction in progress to get committed and governor limits are reset. There’s a downside that your user will need to click Next to continue every 20 or 50 or so rows. It’s better than needing to instruct them to limit their upload size to no more than that number.

With those first two checks done, the Flow queries the Memberpress object looking for a matching User ID. If a match is found, the record has been uploaded before. The only possible change we’re worried about for existing records is the Memberships field, so that field gets updated on the record in the database. The Count_UsersFound variable is also incremented.

On the other side of the decision, if no Memberpress User record match is found then we go down the path of creating a new record, which starts with determining if there’s an existing Contact. A simple match on email address is queried, and Contact duplicate detection rules have been set to only Report (not Alert). If Alert is enabled and a duplicate matching rule gets triggered, then the Screen Flow will hit an error and stop.

If an existing Contact is found, then that Contact ID is written to the Related Contact field on the Memberpress User record and the Count_ContactsFound variable is incremented. If no Contact is found, then the Contact_Individual record variable is used to stage a new Contact record and the Count_ContactsNotFound variable is incremented.

Contact_Individual is then added to the Contact_Collection record collection variable, the current Memberpress User record in the loop is added to the User_Collection record collection variable, and the Count_Processed variable is incremented.

After the last uploaded row in the loop finishes, then the Flow is closed out by writing Contact_Collection and User_Collection to the database. Queueing up individuals into collections in this manner causes Salesforce to bulkify the write operations which helps avoid hitting governor limits. When the Flow is done, a success screen with some statistics is displayed.

The entire Flow looks like this:

Flow variables

Interval_value determines the number of rows to process before pausing and prompting the user to click next to continue.

Interval_minus1 is Interval_value minus one.

MOD_Interval is the MOD function applied to Count_Processed and Interval_value.

The Count_Processed variable is set to start at -1.

Supporting Flows

Sometimes one Flow just isn’t enough. In this case there are three additional record triggered Flows configured on the Memberpress User object to supplement Screen Flow data import operations.

One triggers on new Memberpress User records only when the Related Contact field is blank. A limitation of the way the Screen Flow batches new records into collections before writing them to the database is that there’s no way to link a new contact to a new Memberpress User. So instead when a new Memberpress User record is created with no Related Contact set, this Flow kicks in to find the Contact by matching email address. This Flow’s trigger order is set to 10 so that it runs first.

The next one triggers on any new Memberpress User record, reaching out to update the registration date and membership level fields on the Related Contact record

The last one triggers on updated Memberpress User records only when the memberships field has changed, reaching out to update the membership level field on the Related Contact record

The following articles were very helpful for figuring out how to do this:

How to Use Python in Power BI - freeCodeCamp

Run Python scripts in Power BI Desktop - Microsoft

There's a major discrepancy between those articles - the freeCodeCamp article provides instructions on how to use a Python environment managed via Anaconda in Power BI; whereas Microsoft's documentation warns that Python distributions requiring an extra step to prepare the environment, such as Conda, might fail to run. They advise to instead use the official Python distribution from python.org.

I've tried both, and as far as I can tell both methods seem to work for this purpose. When installing the official Python distribution, the only pre-packaged installer available is for the current version (currently 3.11.4) which requires a little bit of dataprep debugging post-install to get it working. Anaconda makes it easier to install prior Python versions and to switch between multiple Python environments (I successfully tested this in a Python 3.9 installation running in Anaconda). The following instructions are written for the former method though, using the latest version of Python installed via their Windows executable per Microsoft's recommendation.

To conceptualize how Power BI works with Python, it's important to understand them as entirely separate systems. For the purpose of data transformation, a Power Query Python scripting step loads the previous query step into a pandas dataframe for Python to execute, then loads the output of that back to the next query step.

So with that context, the way we'll approach this install is like so:

Set up a local Python development environment

Install the Dataprep library within that

Utilize a test script to debug and verify that the Python environment is working as expected

Configure Power BI to tap into the Python environment

1. Set up a local Python development environment

The first step is easy, navigate to python.org/downloads, click the Download button, and execute the installer keeping all the default settings.

Once you have Python installed, then open a command prompt and run the following commands:

py -m pip install pandas

py -m pip install matplotlib

After installing these two libraries, you've now got the basics set to use Python in Power BI.

2. Install the Dataprep library

Installing the Dataprep library comes next, and to do that you need Microsoft Visual C++ 14.0 installed as a prerequisite. Navigate on over to the Microsoft Visual Studio downloads page and download the free Community version installer.

Launch the Visual Studio installer, and before you click the install button select the box to install the Python development workload, then also check the box to install optional Python native development tools. Then click install and go get yourself a cup of coffee - it's a large download that'll take a few minutes.

After the Visual Studio installation completes, then head back to your command prompt and run the following command to install Dataprep:

py -m pip install dataprep

3. Utilize a test script to debug and validate the Python environment

With the local Python development environment and Dataprep installed, you can try to execute this test script by running the following command in your command prompt window:

py "C:\{path to script}\Test python pandas script.py"

In practice this script will fail if you try to run it using Python 3.11 (it might work in Python 3.9 via Anaconda). It seems that the reason the script fails is because of a couple of minor incompatibilities in the latest versions of a couple packages used by Dataprep. They're easily debugged and fixed though:

The first error message reads: C:\Users\yourname\AppData\Local\Programs\Python\Python311\Lib\site-packages\dask\dataframe\utils.py:367: FutureWarning: pandas.Int64Index is deprecated and will be removed from pandas in a future version. Use pandas.Index with the appropriate dtype instead. 

To fix this error, simply navigate to that file location in Windows Explorer, open the utils.py file, and comment out line 367 by adding a pound sign at the beginning. While you're in there, also comment out lines 409-410 which might produce another error because they reference that function from line 367.

After making that adjustment, if you return to the command line and try to execute the test script you'll encounter another error message. This time it reads: cannot import name 'soft_unicode' from 'markupsafe'

Googling that message turns up a lot of discussion threads from people who encountered the same problem, the upshot of which is that the soft_unicode function was deprecated in markupsafe as of version 2.1.0, and the fix is a simple matter of downgrading that package by running this command in your command line window: py -m pip install markupsafe==2.0.1

After those adjustments have been made, you should be able to run the test script in your command line window and see this successful result:

4. Configure Power BI to tap into the Python environment

You're so close! Now that you have Dataprep working in Python on your local machine, it's time to configure Power BI to leverage it.

In Power BI Desktop - Options - Python scripting, ensure that your Python installation directory is selected as the home directory. Note that if you manage multiple environments via Anaconda, this is where you would instead select Other and paste an environment file path.

Now in Power Query, select Transform - Run Python Script to add a step to your query. The script used here differs in a couple key ways from the test Python script run via your command prompt:

omit import pandas and import numpy

instead of defining df as your pandas dataframe, use the predefined dataset dataframe

My final script, pasted below, leverages the phone and email cleanup functions in Dataprep, as well as leveraging Python to calculate when a proper case cleanup is needed in a slightly more efficient manner than my previous PBI steps to clean that up.

Scripts

Power BI Python script syntax

# 'dataset' holds the input data for this script dataset['FirstName Lower'] = dataset['FirstName'] == dataset['FirstName'].str.lower() dataset['FirstName Upper'] = dataset['FirstName'] == dataset['FirstName'].str.upper() dataset['FirstName Proper'] = dataset['FirstName'].str.title() dataset['LastName Lower'] = dataset['LastName'] == dataset['LastName'].str.lower() dataset['LastName Upper'] = dataset['LastName'] == dataset['LastName'].str.upper() dataset['LastName Proper'] = dataset['LastName'].str.title() from dataprep.clean import validate_phone dataset['Valid Phone'] = validate_phone(dataset["Phone"]) from dataprep.clean import clean_phone dataset = clean_phone(dataset, "Phone") from dataprep.clean import validate_email dataset['Valid Email'] = validate_phone(dataset["Email"]) from dataprep.clean import clean_email dataset = clean_email(dataset, "Email", remove_whitespace=True, fix_domain=True)

Python test script syntax

I figure the best way to brush off the digital cobwebs is to share something amusing. I like gadgets. I'm not embarrassed to admit that, except when I think back to my Google Glass year a decade ago.

Most of my technology purchases now are more reasonably priced, everyday tech, like smart home devices. I've had an Amazon Echo since the first generation was released, and accumulated them in most rooms of our house since. We have a Hue lighting system, and a Ring alarm, and assorted other small connected devices.

There are things I really enjoy about these amenities. I highly recommend the premium lighting package. I enjoy the convenience of controlling lights by voice commands, and calling out for a weather forecast or a song. It’s also neat that my devices have APIs which can talk to each other, allowing me to program custom automations and integrations between them.

But there’s another side to this technology. And I’m not talking about the level of access and control of our personal data we’ve handed to Amazon. *shudder* No, the other side I’m talking about is when the technology just doesn’t work. When my smart home gets really dumb, sometimes comically so.

Too many Alexas

The idea was to have a voice assistant system that works through the whole house. Turns out, “works” is doing a lot of work there. It does not work well when multiple Echos hear the same command, then a timer starts on the wrong device, or a song starts playing in adjoining rooms a couple seconds off from each other.

Zombie automations

A couple years ago when we took a holiday trip, I enabled a Phillips Hue routine which turns the lights on and off on a schedule with some randomness. I thought the latter part meant it would vary which lights came on, but it actually meant that all the lights in the house come on between 3:30 and 4pm and all the lights in the house turn off between midnight and 12:30am.

Five hundred plus days later I still haven’t gotten around to finding the setting to disable that automation. I just got in the habit of calling out “Alexa turn the lights off” in the late-afternoon, and reaching for the dimmer switch on my desk to turn them back on when I'm up late.

Janky integrations

A few years ago, I found that I could interface a motion sensor from the Ring Alarm with the Hue lights so that between 11pm and 6am motion detection would turn on a path of dimmed lights between the bedroom and bathroom. A second automation trigger on a ten minute delay would then turn the lights off. Well, it worked great, except those odd early mornings when a small child wants to hang out in that area before dawn, then the lights start turning off repeatedly.

When the kids get ahold of it

Smart home amenities are cool when you set them up and know how to use them, but can produce some unexpected results once the kids start playing with them. They push the Ring doorbell and yell into the camera every time we come in the front door. If a Hue dimmer switch isn’t working, we have to make sure it’s not controlling another room’s lights because a giggling child moved the switches around. And if we’re hearing Alexa play Who Let the Dogs Out for the thousandth time, I'm just thankful it isn’t Poop Poop Poop, by Poop Man in Fart Land. Again.

When the technology just doesn’t work

This anecdote isn’t about a smart home device, but another kind of home technology. Several years ago I needed to replace a broken toilet valve, and upgraded to a fancier "water saving" mechanism. It made sense to spend a little extra on something that would conserve resources and save money in the long run. Except it didn’t work as advertised. A few months after installing it, I noticed water continuously leaking into the toilet from around the valve seal. I tried a couple times to uninstall and reinstall to get it to seal properly, but each time it would soon go back to leaking again.

Finding parallels and lessons

I like to think that lessons learned from my personal tech fails can help inform my work, and can attempt to draw some parallels here:

Too many Alexas

Once upon a time, I built out a bunch of back end data flows to support my organization’s eCommerce initiative. Zapier was my best friend, and every Shopify transaction triggered a half-dozen different Zaps for different purposes. It was effective but messy, inefficient, and sometimes the nuance of how integrations differed resulted in confusing inconsistencies. We built something amazing with a minimal budget, but I regret the substantial technical debt.

Kind of like accumulating too many Echo devices, the results can be undesirable when an action triggers too many different processes. At home I addressed that by reprogramming the Echo devices to listen for different wake words, proving yet again that doing some basic analysis and disambiguation of tasks can result in low-effort high-impact improvements.

Zombie automations

Have you ever encountered a situation where nobody can quite explain how a particular type of data is showing up in a system? It just is. Probably got set up by a former co-worker, nobody knows which service account is running it, no less how to log in. And there’s a formula misconfigured so a calculated field is often wrong, but current staff are aware of what to look for and used to fixing them so it's all good.

Sometimes we just get too comfortable with inefficient systems, and the inefficiency leads to time constraints that push everyone to the point where nobody has bandwidth to consider change. You know what can be a great impetus for breaking the inertia? Talking through a business process and writing it down!

Business process documentation is a valuable exercise on its own, and it’s a powerful change driver when you look at a difficult system mapped out and go OMG I can’t just document this broken thing, let's actually fix it. Kind of like how writing this story finally prompted me to find and turn off that old holiday light schedule.

Janky integrations

Automations can get stuck in loops. A misconfiguration can trigger repeatedly and eat your entire Zapier monthly task cap in a day. Or sometimes you have a mandate to get something done, but the tools aren't well suited to the challenge, so you do your best to make them work and have to live with the poor result.

Years ago my then-new org wanted member data integrated with the CRM, but memberships were sold through a magazine fulfillment house whose only viable integration method was sending a nightly series of CSV files to an FTP server. The vendor had no way of receiving automated updates back or even bulk updating from a spreadsheet. Dirty data we’d corrected kept showing up. New duplicate customer records ran rampant. Transaction rows contained no unique identifier. I eventually dubbed that integration my river of shit, and wished I could get a do-over to narrow its scope and impact.

You’re best off identifying situations like that early, and rethinking them before they become fully implemented problems. My motion sensor / lights hack didn’t last long. Another Hue presence sensor purchase could have worked better, but wasn’t needed after I just put a cheap dumb motion sensor on the light in the laundry closet that we also walk by on the way to the bathroom.

When the kids get ahold of it

Your co-workers obviously aren’t small children, but there is an aspect of deploying systems that involves giving people new toys and then watching how they use them in unexpected ways. Observing behavior, adapting systems to meet it, and building in appropriate guardrails are all tactics that help serve your users better and prevent them from breaking new systems.

Conversely, our light switches are all labeled on the back in sharpie marker so we know where they’re supposed to go.

When the technology just doesn’t work

Sometimes you need to acknowledge when a system isn’t living up to expectations. After finally cutting my losses and getting rid of the “water saving” toilet valve in favor of a standard flap and pull chain, our water bill dropped by $5 to $10 a month.

The use case at hand is for box office staff working at the American Craft Show to look up and check in members from our database. Free admission to the show is a benefit of membership in the American Craft Council, and a decent chunk of those member tickets are claimed on site the day of the show.

There's a longer backstory to how this evolved, but the pertinent part is that we have a relatively new Salesforce CRM database that we're now able to leverage to build systems on top of to make tasks like this more efficient. The first iteration of this app was built on a platform which enabled querying on the last name field to pull contact records into a grid, from which box office staff could then double tap a small edit button next to any one of them, which opened a modal window where they check a box and then the Update button to send that data back to the CRM.

It was a good first effort, but had some pretty glaring shortcomings. In particular we faced adoption challenges with box office staff, who had been used to a different vendor platform for years that required them to click through multiple screens to verify membership and then offered no way to check them in. We succeeded at provisioning an interface that made the lookup portion more efficient, but staff weren't used to checking people in and the mechanism for that was just too clunky. Four taps may not seem like much, but it's a big deal in a fast-paced environment with customers lined up, and especially when the edit button is too small and requires a quick double tap on just the right spot to work.

So I went back to the drawing board, and at a point in that process it occurred to me that I might be better off moving the data out of Salesforce to broaden the pool of potential options. That led me think of the Google Sheets Data connector for Salesforce, followed by a brief flirtation with building something native in there using Google Apps Script, followed quickly by the revelation that's beyond my technical depth and I'm not up for the learning curve right now, but hey maybe there's something else out there which can help leverage this.

Enter Glide Apps. They're a pretty new startup that promises the ability to create a code-free app from a Google Sheet in 5 minutes. And even better, Soliudeen Ogunsola wrote this tutorial on Creating an Event Check-in App with Glide. It was a magic "this looks perfect" moment, and sure enough after having spent hours trying a few things that didn't work I gave this a shot and within a half hour had a functional prototype of something that worked perfectly.

Some of the things that I learned while building this out and getting it ready to use in production:

It's important for both consistency and efficiency's sake to make the data refresh process as easy and repeatable as possible. With that in mind, it's really important to consider that the Salesforce Data Connector deletes and reloads all data in the spreadsheet whenever it's refreshed. On the up side though, as long as the column headings remain consistent then the mappings set up in Glide Apps continue working seamlessly after the spreadsheet data is refreshed.

Because of that, I created a custom checkbox field on the Contact object for ticket pickup which is set to FALSE by default. That enabled it to be included in the query rather than added manually each time the data is refreshed.

Glide allows only two fields to be displayed in the List layout. In our case I wanted to display the member's full name as the title, then their City, State, and ZIP Code as the subtitle. I initially concatenated those fields in the Google Sheet, but to make it more easily repeatable I subsequently created a formula field on the Contact object in Salesforce that did the same concatenation and then included that field in the Data Connector query.

Adding a Switch to the Details layout in Glide enables the user to edit only that data point. The allow editing option can remain off so they can't change any other details on the contact record - the only interactive element on screen is the button to check them in.

I attempted to build a connection back into Salesforce to record the ticket pickup on their record via Zapier. However, when I tested checking one person in it triggered thousands of Zapier tasks. Something in the architecture is causing Zapier to think lots of rows were updated when I think they maybe had just been crawled. Point is that didn't work and thank goodness Zapier automatically holds tasks when it detects too high a volume all at once.

The volume of records that we're dealing with (in the tens of thousands) causes the Glide App to take a couple minutes to load when it's first opened. It requires coaching a little patience up front, but the good news is that's a one time deal when the app is initially opened. From that point forward everything works instantaneously. It's ultimately an improvement over the previous iteration, which would load quickly but then took several seconds to bring contact data into the grid on every member lookup.

I'm not on site at our San Francisco show, but can see that on day one of the show there are dozens of members recorded in the spreadsheet as having checked in, and I haven't gotten any frantic phone calls about it not working, so at this point I'm going to assume that means it's a success. But then I was pretty confident it would work. When it's just this simple to use, the odds seem good:

I mean I didn’t really have to, I could have gone through the process of staging an update and pushing code to production. It’s more accurate to say I wanted to avoid that for this particular one-off project.

So while I had a general idea of how this could be built using a display:none property that toggles on/off by clicking another element, I did a quick web search for a code sample before reinventing any wheels. And sure enough, found this Codepen - Pure CSS read more toggle by Kasper Mikiewicz

Using that as a starting point, I stripped down the code and it works like a charm. The end result can be seen on my org’s conference schedule page. And here’s the code sample:

<style> .read-more-target {  display: none; }

.read-more-state:checked ~ .read-more-target {  display: block !important;  margin-top: 1em; }

.read-more-state ~ .read-more-trigger:before {  content: 'Show description'; }

.read-more-state:checked ~ .read-more-trigger:before {  content: 'Hide description'; }

.read-more-trigger {  cursor: pointer;  color: #0000FF;  font-size: 0.85em; } </style>

What's a soft credit?

Before diving into the steps, a quick review of terminology.. Soft credits are when a donor is credited with a donation that they didn't actually write the check for. A common example is when the check comes from a family foundation or donor advised fund. The hard credit for that donation belongs to the entity that sent the check, but the soft credit goes to the donor who directed them to cut that check. Other examples can include matching gift campaigns or crowdfunding efforts. Guidelines for what gets counted as a soft credit vary at different nonprofit organizations, and consequently Salesforce provides some flexibility around how to configure them as referenced in the documentation on Soft Credit and Matching Gift Setup.

Contact soft credits vs. Account soft credits

A key concept that tripped me up at first is the distinction between Account Contact Soft Credits and Account Soft Credits. Finding this description in the NPSP Soft Credits Overview documentation was my aha moment where how to do this really clicked: "If you enable Customizable Rollups, you can create Contact Soft Credit rollup fields at the Account level. ... With Account Contact Soft Credits, it is now possible to see the total giving for all Household members."

In short, Account Contact Soft Credits are exactly what I wanted to configure. Account Soft Credits on the other hand are a very different thing, they're an entirely separate feature which enables directly soft crediting an Account for a donation made by another entity.

On to the tutorial

Based on the aforementioned aha moment, and on information gleaned from each of the articles linked to above, it dawned on me that the process of configuring Salesforce Customizable Rollups to roll up contact soft credits to Households is really quite straightforward:

Create new custom fields on the Account object

Add those fields to the desired page layouts

Configure customizable rollups to populate data to those fields

1. Create new custom fields on the Account object

First step in creating any customizable rollup is to create a field to populate the field to. In this case I'm creating fields on the Account object which mirror existing fields on the Contact object, so I just place two windows side-by-side - both in Setup - Object Manager, one to reference the existing Contact fields and the other to create new corresponding Account fields of the same field type. Then just copy-paste the title over, mirror the other details, save, and repeat for the remaining fields.

2. Add those fields to the desired page layouts

In Setup - Object Manager - Account, open the page layouts that you want to display Soft Credit info on and add them to the page layout. In the example here, I've created a new section on the page titled Soft Credit Totals and dragged the fields into them. If you want this to mirror how it's setup on the Contact page layout, you could also pull that up in another window and mirror the setup.

3. Configure customizable rollups to populate data to those fields

Now to configure the Customizable Rollup, would you believe that I'm about to tell you that's as easy as pulling up the existing Contact rollups in one window and mirroring the configuration in another window for new Account rollups? Because it's actually that easy. In NPSP Settings - Donations - Customizable Rollups, click Configure Customizable Rollups, then New Rollup. Target Object = Account, Target Field = a field you created in Step 1, Rollup Type = Opportunity -> Account (Contact Soft Credit). All other settings should simply mirror the Contact rollup in your other window.

Conclusion

It needs to start with some acknowledgements. These SQL scripts derive from the scripts in Jon Goldberg’s post on Exporting Raiser’s Edge for CiviCRM. I don’t know how we would have completed our migration without that resource. And these field mapping spreadsheets derive from templates provided by Andrea Hanson of Redpath Consulting Group. Andrea and the whole team over at Redpath have been amazing implementation partners.

Downloads

In case you’re just here for the downloads, and you didn’t spot the links in the preceding paragraph, here you go:

Download Raiser’s Edge extraction SQL scripts (.zip)

Download Raiser’s Edge to Salesforce field mapping templates (.zip)

These resources were used to migrate into Salesforce from The Raiser’s Edge Version: 7.93.5782.1

In order to execute the SQL queries, you will need access to the server on which RE is running and administrator credentials for SQL Server Management Studio. My preferred method to pull data out is to copy the query results in the Remote Desktop window then paste them into an Excel sheet on my local desktop.

Recommended Tools

Ablebits Ultimate Suite for Excel is an essential tool for comparing and merging tables in Excel.

A programmable keyboard helps automate repeated keystroke routines to cut through data combing tasks like a warm knife through butter. My go to model is the Logitech G610. That’s been a solid choice that was reasonably priced and serves me well, though I miss the dedicated macro record key from my trusty old G710 (now discontinued). The more expensive Logitech G910 appears to have a macro record key and could be a better choice for beginners.

DemandTools is a data quality power toolset that is essential for deduplicating data once it's in Salesforce. Up until mid-2018 the product was available via donation for most c3 nonprofits. The vendor's current NFP program offers a 20% discount to most nonprofits and via donation to nonprofits with less than $250,000 in net assets.

Tips

Like I wrote to open this blog post, it’s overdue. I have my excuses - another, even more daunting data migration came on the heels of this one; I also had a grad school program to contend with. But ultimately it’s been so long since our RE migration, and there were so many quirks involved, I would be hard pressed to write up a full case study at this point. But I still remember some of the pain points and can provide some tips here on what to watch out for.

Raiser's Edge has a Records table and a Constituents table. Constituents are basically a full-fledged customer. Records include constituents plus other leaner contacts in the system, such as spouses or affiliated contacts of a constituent.

In many of the back end RE tables, the primary key field is labeled CONSTIT_ID. This usually corresponds to the ID field from the Records table, not the CONSTITUENT_ID field. Except in a couple of cases where it doesn't

The ID fields in both the Records and Constituent tables are sequential numbers which began at 1, and for a significant chunk of customers who were imported into RE years ago when that was deployed, these two ID numbers are exactly the same. This can be a huge gotcha when figuring out how to join data between tables, because if you join the tables on the wrong ID it may appear accurate at first until you find later that it wasn't. For this reason, it's also important to check a true random sample of data, don't only spot check the longtime members who you know well.

Check your work thoroughly at each step to ensure the data is coming together as expected. Save your work often. Save a new version of the files you're working at least once a day. File system space and organization be damned, it's just more important to have point in time copies you can revert back to because at some point something will goes sideways and you'll need it.

Several of the SQL scripts are just a SELECT * FROM {table} function. These are pretty straightforward data points that I would extract with the basic query and then subsequently use Ablebits to merge into the import template by matching the ID number up.

Email addresses are technically a type of phone number in RE's data structure. In order to export them you have to join the records table to the constituent address table, joined to the address table, joined to the constituent address phones table, joined to the phones table. But on the bright side, once you get there you can export phone numbers as well

I counted 594 distinct tables in our Raiser's Edge SQL database. Of those 308 were completely empty. That has no real bearing on anything, but it's interesting trivia

The way that spouse relationships are recorded is a challenge. In some cases they each have a relationship pointing in the other's direction, others only have one relationship recorded moving in one direction. In our case about a third of them had it going both ways, which resulted in some duplication that I had to sort out. I don't remember exactly how I did that, just that it was tricky and time consuming and generally made me grouchy

Do not deduplicate customer records prior to import. There's too much risk that trying to do so could result in orphaned donation records, it's less risky and just easier to put them all into Salesforce first and then use DemandTools to merge the dupes. Also, if your RE deployment was anything like ours, expect a significant number of duplicate contacts to result because a contituent was also been recorded as an affiliated contact on other consitutent records in multiple instances.

The TABLEENTRIES table in Raiser's Edge is the key to translating many of the fields located in other tables. The other tables often contain a mysterious numeric value in places where you might expect to see actual data, that value needs to be matched to the data via TABLEENTRIES. Some of the data which this applies to are salutation types, campaign types, address types, countries, phone types, payment types, and relationship types. I used Ablebits to merge these in Excel as opposed to incorporating those mappings in the SQL scripts.

The way that campaigns are structured in Raiser's Edge is fundamentally different from how that works in Salesforce. This presents a challenge in terms of converting the data to fit the new system, but also presents an opportunity to streamline your campaign data structure!

In Raiser's Edge there are four defined levels - campaign, fund, appeal, and package. Objects at each level exist in a many-to-many relationship (i.e. a fund can be associated with multiple child appeals, and an appeal can be associated with multiple parent funds). Individual donations can be tied to an object at any level.

In Salesforce there is only one type of object, called a campaign. Campaigns can be structured in hierarchical relationships in a one-to-many relationship (i.e. a campaign can be associated with multiple child campaigns, but a campaign can only have one parent campaign). Individual donations can be tied to an object at any level.

Our legacy Raiser's Edge database contained hundreds of distinct campaigns, many of which were entered years ago and only used for a single donation or handful of donations. Through consolidation we reduced the number of campaigns by over 70%, then mapped legacy campaigns to new campaigns on the gift records prior to importing those so that they all magically landed where they were supposed to in the new structure

A breakthrough in our cleanup process came when we realized that most of the appeals and packages that were associated with multiple parent campaigns could be migrated to picklist values on the Salesforce campaigns rather than needing to remain as a distinct campaign in the new schema.

As part of this transformation effort, we implemented a canonical naming structure so that child campaigns incorporate the parent campaign's name. That makes them all line up hierarchically when viewed in a flat alphabetical list, which is nice, but due to Salesforce's 80 character limitation for campaign names required abbreviating in some cases.

The spreadsheet which I created to analyze and consolidate RE campaigns ended up being twenty feet long printed out - 46 11x17” pages taped together 2 across and 23 down. We rolled that out on the conference room table so that development and finance staff could work through it together, making final changes in red ink that I then incorporated into the data staged for import. The photo of them working through that ginormous spreadsheet is my favorite image from this project.

Raiser's Edge SQL extract queries

You can use this link to download the entire set of SQL queries as a zip file, or for your copy-pasting convenience they're also listed below. These are just the ones that involve table joins, the zip file also includes single table exports, most of which are simply SELECT * from {table name}.

Address - phones

USE RE7 SELECT RECORDS.id , RECORDS.is_constituent , RECORDS.last_name , RECORDS.first_name , RECORDS.org_name , PHONES.phonetypeid , PHONES.num , PHONES.DO_NOT_CALL , ADDRESS.address_block , ADDRESS.city , ADDRESS.state , ADDRESS.post_code , ADDRESS.country , CONSTIT_ADDRESS.type , CONSTIT_ADDRESS.indicator , CONSTIT_ADDRESS.preferred , CONSTIT_ADDRESS.sendmail , CONSTIT_ADDRESS.seasonal , CONSTIT_ADDRESS.seasonal_from , CONSTIT_ADDRESS.seasonal_to FROM RECORDS LEFT JOIN CONSTIT_ADDRESS ON RECORDS.id = CONSTIT_ADDRESS.constit_id LEFT JOIN dbo.ADDRESS ON CONSTIT_ADDRESS.id = ADDRESS.id LEFT JOIN CONSTIT_ADDRESS_PHONES ON ADDRESS.id = CONSTIT_ADDRESS_PHONES.constitaddressid LEFT JOIN PHONES ON CONSTIT_ADDRESS_PHONES.phonesid = PHONES.phonesid where num is not null order by records.last_name asc, records.first_name asc, records.org_name asc

Address - seasonal

use RE7 SELECT RECORDS.id , RECORDS.last_name , RECORDS.first_name , RECORDS.org_name , CONSTITUENT.records_id , CONSTITUENT.id , CONSTITUENT.key_name , CONSTITUENT.first_name , ADDRESS.address_block , ADDRESS.city , ADDRESS.state , ADDRESS.post_code , ADDRESS.country , CONSTIT_ADDRESS.type , CONSTIT_ADDRESS.indicator , CONSTIT_ADDRESS.preferred , CONSTIT_ADDRESS.sendmail , CONSTIT_ADDRESS.seasonal , CONSTIT_ADDRESS.seasonal_from , CONSTIT_ADDRESS.seasonal_to FROM RECORDS LEFT JOIN CONSTITUENT ON RECORDS.id = CONSTITUENT.records_id LEFT JOIN CONSTIT_ADDRESS ON CONSTITUENT.id = CONSTIT_ADDRESS.constit_id LEFT JOIN dbo.ADDRESS ON CONSTIT_ADDRESS.address_id = ADDRESS.id where seasonal = -1 order by records.last_name asc, records.first_name asc, records.org_name asc

Address

use RE7 SELECT RECORDS.id , RECORDS.last_name , RECORDS.first_name , RECORDS.org_name , CONSTITUENT.records_id , CONSTITUENT.id , CONSTITUENT.key_name , CONSTITUENT.first_name , ADDRESS.address_block , ADDRESS.city , ADDRESS.state , ADDRESS.post_code , ADDRESS.country , CONSTIT_ADDRESS.type , CONSTIT_ADDRESS.indicator , CONSTIT_ADDRESS.preferred , CONSTIT_ADDRESS.sendmail , CONSTIT_ADDRESS.seasonal , CONSTIT_ADDRESS.seasonal_from , CONSTIT_ADDRESS.seasonal_to , CONSTIT_ADDRESS.date_from as valid_from , CONSTIT_ADDRESS.date_to as valid_to FROM RECORDS LEFT JOIN CONSTITUENT ON RECORDS.id = CONSTITUENT.records_id LEFT JOIN CONSTIT_ADDRESS ON CONSTITUENT.records_id = CONSTIT_ADDRESS.constit_id LEFT JOIN dbo.ADDRESS ON CONSTIT_ADDRESS.address_id = ADDRESS.id

Attributes

SELECT DESCRIPTION , at.CODETABLESID , LONGDESCRIPTION FROM TABLEENTRIES te LEFT JOIN AttributeTypes at ON te.CODETABLESID = at.CODETABLESID ORDER BY DESCRIPTION --- SELECT DISTINCT * FROM AttributeTypes at LEFT JOIN TABLEENTRIES te ON te.CODETABLESID = at.CODETABLESID ORDER BY DESCRIPTION --- SELECT * FROM ConstituentAttributes

Constituent Codes

USE RE7 SELECT cc.constit_id as record_id, t.LONGDESCRIPTION, cc.DATE_FROM, cc.DATE_TO, c.NAME FROM dbo.Constituent_Codes AS cc INNER JOIN dbo.TABLEENTRIES AS t ON cc.CODE = t.TABLEENTRIESID INNER JOIN dbo.CODETABLES AS c ON c.NAME = 'Constituent Codes' AND t.CODETABLESID = c.CODETABLESID

Gifts

use RE7 SELECT gs.GiftId , g.CONSTIT_ID as records_id , p.APPEAL_ID as appeal_id , p.ID as package_id , gs.Amount , g.RECEIPT_AMOUNT , g.DTE as gift_date , g.DATE_1ST_PAY , g.DATEADDED , CAMPAIGN.DESCRIPTION as campaign , FUND.DESCRIPTION as fund , APPEAL.DESCRIPTION as appeal , p.DESCRIPTION as package , g.PAYMENT_TYPE , g.ACKNOWLEDGE_FLAG , g.CHECK_NUMBER , g.CHECK_DATE , g.BATCH_NUMBER , g.ANONYMOUS , gst.LONGDESCRIPTION as giftsubtype , g.TYPE , DBO.TranslateGiftType(g.TYPE) as type2 , g.REF , g.REFERENCE_DATE , g.REFERENCE_NUMBER , g.ANONYMOUS , g.ACKNOWLEDGE_FLAG , g.AcknowledgeDate , g.GiftSubType FROM GiftSplit gs LEFT JOIN FUND on gs.FundId = FUND.id LEFT JOIN APPEAL on gs.AppealId = APPEAL.id LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id LEFT JOIN GIFT g on gs.GiftId = g.ID LEFT JOIN Package p on gs.PackageId = p.ID LEFT JOIN TABLEENTRIES gst on g.GIFTSUBTYPE = gst.TABLEENTRIESID

Pledge payments

/* Find all pledge installments, and their related payments if they exist. */ SELECT i.InstallmentId , i.PledgeId , i.AdjustmentId , i.Amount as scheduled_amount , i.Dte , ip.Amount as actual_amount , ip.PaymentId , g.CONSTIT_ID , g.RECEIPT_AMOUNT , g.DTE as receive_date , g.TYPE , DBO.TranslateGiftType(g.TYPE) as type FROM Installment i LEFT JOIN InstallmentPayment ip ON i.InstallmentId = ip.InstallmentId LEFT JOIN GIFT g ON ip.PaymentId = g.ID /* Adjustments are stored in here too - when an adjustment happens, the pledge ID of the original value is blanked */ WHERE i.PledgeId IS NOT NULL ORDER BY i.AdjustmentId /* Write-off Types: Covenant WriteOff, MG Write Off, Write Off */

Pledges

/* Find all GIFT records with one or more associated Installment records. These are pledges OR recurring gifts. */ SELECT DISTINCT g.CONSTIT_ID , g.ID as GiftId , g.Amount , g.DTE as receive_date , FUND.DESCRIPTION as fund , FUND.FUND_ID , CAMPAIGN.DESCRIPTION as campaign , APPEAL.DESCRIPTION as appeal , g.PAYMENT_TYPE , g.ACKNOWLEDGEDATE , DBO.TranslateGiftType(g.TYPE) as type , g.REF as note ,DATE_1ST_PAY ,g.DATEADDED ,g.DATECHANGED ,INSTALLMENT_FREQUENCY ,NUMBER_OF_INSTALLMENTS ,POST_DATE ,POST_STATUS ,REMIND_FLAG ,Schedule_Month ,Schedule_DayOfMonth ,Schedule_MonthlyDayOfWeek ,Schedule_Spacing ,Schedule_MonthlyType ,Schedule_MonthlyOrdinal ,Schedule_WeeklyDayOfWeek ,Schedule_DayOfMonth2 ,Schedule_SMDayType1 ,Schedule_SMDayType2 ,NextTransactionDate ,Schedule_EndDate ,FrequencyDescription , r.CONSTITUENT_ID FROM Gift g LEFT JOIN GiftSplit gs on g.ID = gs.GiftId LEFT JOIN FUND on gs.FundId = FUND.id LEFT JOIN APPEAL on gs.AppealId = APPEAL.id LEFT JOIN CAMPAIGN on gs.CampaignId = CAMPAIGN.id LEFT JOIN RECORDS r ON g.CONSTIT_ID = r.ID JOIN Installment i ON g.ID = i.PledgeId

Salutations

SELECT R.ID, R.Constituent_ID, R.FIRST_NAME, R.LAST_NAME, R.[DATE_LAST_CHANGED], case when R.PRIMARY_ADDRESSEE_EDIT = -1 then R.PRIMARY_ADDRESSEE else dbo.GetSalutation(R.PRIMARY_ADDRESSEE_ID,R.ID,'',0,0,0,getdate()) end as 'PRIMARY_ADDRESSEE', case when R.PRIMARY_SALUTATION_EDIT = -1 then R.PRIMARY_SALUTATION else dbo.GetSalutation(R.PRIMARY_SALUTATION_ID,R.ID,'',0,0,0,getdate()) end as 'PRIMARY_SALUTATION', ADDSAL_TYPE.LONGDESCRIPTION as 'ADDSAL_TYPE_DESC',CS.SALUTATION_ID, CS.SEQUENCE, CS.EDITABLE, case when CS.EDITABLE = -1 then CS.SALUTATION else dbo.GetSalutation(CS.SALUTATION_ID,R.ID,'',0,0,0,getdate()) end as 'SALUTATION_CORRECT', CS.SALUTATION as 'SALUTATION_FIELD_INCORRECT' FROM [RECORDS] R left outer join CONSTITUENT_SALUTATION AS CS ON R.ID=CS.CONSTIT_ID left outer join TABLEENTRIES AS TE ON R.TITLE_1=TE.TABLEENTRIESID left outer join TABLEENTRIES ADDSAL_TYPE on CS.SAL_TYPE=ADDSAL_TYPE.TABLEENTRIESID ORDER BY CS.SEQUENCE

Soft credits

USE RE7 SELECT GiftId , ConstitId , Amount , 'Soft Credit' as soft_credit_type FROM GiftSoftCredit

Solicitor relationship

SELECT CONSTIT_ID , SOLICITOR_ID , TABLEENTRIES.LONGDESCRIPTION as solicitor_type , AMOUNT , NOTES , cs."SEQUENCE" as weight FROM CONSTIT_SOLICITORS cs LEFT JOIN TABLEENTRIES ON cs.SOLICITOR_TYPE = TABLEENTRIES.TABLEENTRIESID ORDER BY weight

Additional resources

In addition to the essential Exporting Raiser’s Edge for CiviCRM post mentioned above, Accessing the Raiser’s Edge database using SQL by SmartTHING was a useful reference during our migration.

Salesforce Data Migration - Raisers Edge by Larry Bednar is insightful reading for planning an RE migration. The NW Data Centric downloads page is also a treasure trove, and I wish I’d found their Standard NWDC Raisers Edge to Salesforce Data Processing Download sooner.

These Do’s and Don’ts of Data Migration by Megaphone Technology Consulting provide some very sound advice on planning for the human side for a data migration.

Another great resource on change management and planning for the human side of a database implementation is Your CRM Is Failing and It’s All Your Fault, a session presented by Karen Graham, Danielle Gangelhoff, Kelly Kleppe, and Libby Nickel Baker at the 2017 Minnesota Council of Nonprofits Communications and Technology Conference.