Importing a CSV list of Constituents

Chris Zello

I'm wondering if Power Automate could be used to import a large list of constituents (in the ballpark of 40,000-50,000 records/month from our Grateful Patient program) from a CSV data file in SharePoint?

The import process would have to involve setting up parameters that check for matched records already in RE, but if no match is found, then it would write a new record. Very important that we don't have a ton of duplicates to merge.

The records would have biographical information (title, name, suffix, address, phone, email, gender, DOB), as well as 4 columns of prospect ratings. It would have to add the Addressee/Salutations. There is a Patient constituency as well as one Attribute to add.

We currently use Omatic to handle this process, and I'm trying to see if there is a more efficient way to do this with Power Automate?

Thanks.

Austen Brown

@Chris Zello - There are sometimes unforeseen limits actions within PA that you may bump into, the limit amount is dependent on what license you have. Here's a Mircosoft support article that talks more about limits: https://learn.microsoft.com/en-us/power-automate/limits-and-config.

Alex Wong

@Chris Zello
Omatic is a “better” option due to its flexibility to match to existing constituent record with many dup search capabilities and processing data into existing record in a way that has a lot more control.

However, it is possible if you want to create a way to import using power automate. You will have to figure out each step of the way yourselves though. question like the follow will have to be answered/taken care of.

• what if more than 1 records were found matching, do you want to “skip” and have it more manually handled?
• on match, do you automatically overwrite data in RE with new incoming data or only “insert” new data where existing record does not have it.

regarding limits, normal client of Blackbaud has a 25K API call limit per 24-hours, so unless you are "importing all 40-50K in the same day, you should be ok.

Chris Zello

@Austen Brown Thank you! The hyperlink picked up on your period at the end. But I was able to find it when using

Chris Zello

@Alex Wong Yes Alex, several of the things you are mentioning with addressing duplicates are things we handle manually for the “needs attention" records with Omatic Cloud. It sounds like that would have to be addressed in some way regardless of the way we import. Thank you.

Karen Diener

@Chris Zello
The only thing I'm going to toss out is that this is A LOT of new constituents each month. Do you have a process in place to also remove constituents from your database? The record count increases will be staggering, and Blackbaud has started moving organizations into the next record level automatically once they cross their threshold.

Another option might Omatic's List Management. I know that isn't exactly what you're asking about, but importing directly to your production database has a lot of consequences. When I was with a hospital foundation we had a completely separate database for our patient data and they were only added to the production database when they made a gift.

Karen

Alex Wong

@Karen Diener
I agree with you, keeping in mind your NXT record count quota is important, as next band is always a lot more expansive.

Omatic List Management is only database view usable, which is a shame in my opinion, I wish Omatic has a web-able solution with list management. However, I undeerstand it is likely not possible without Blackbaud SKY API allowing listing of non-constituent records.

Although, if you feel adventurous, you can create your own list management on the webview, but you cannot store info into RE (LIst Management still creates record in RE database as non-constituent, so at least it is still living in RE, even though it cannot be used outside of List Management directly in RE). But b/c of the nature of List Management, it isn't all that different than having the "non-constituent' living in a sharepoint list/excel. Do-able, and may not be as difficult as one would think.

Chris Zello

@Alex Wong @Karen Diener Yes, I agree with you both. The other thing we ran into with demoted records from List Management is that our email software (NetCommunity) does not communicate unsubscribes into our Solicit Codes when there is a demoted record. We have had to promote all of our List Mgmt records. So we've had to increase our bandwidth for records.

Chris Zello

I checked with Omatic to see if upgrading our API calls to 250K in 24 hours, to see if this would improve our performance. Support said that the processing speed of our Omatic Cloud formulas is independent of our BB contract and the number of API calls we have.

Omatic did say they recently deployed a change to the processing speed in OC that should improve our experience. So I'll have to see what happens when I process the next batch.

Alex Wong

@Chris Zello
25K API call per 24-hour is a Blackbaud limit, not Omatic.

With OMatic's solution (Omatic CLoud) I do not think they use your API limits. Partners like Omatic has a much higher API limit than ourselves as client of Blackbaud, which is what is being used to make API calls on your org's behalf.

SmartTHING Developers

@Alex Wong @Chris Zello Just to add to the list - SmartIMPORT would easily handle this data and you could possibly do something more direct via the platform too in the longer term. Partners do have a higher API limit. Anyway, if interested do get in touch with our team support at smartthing.org.

Sowjanya Yalavarthi

@Chris Zello Do you have any template power automate flow that you could share for scenario of importing constituent file(CSV) and create the constituents