SKY API to export and import data from blackbaud raiser edge

Su Fang Lim

Hi,

Do you know which SKY API that is suitable to export data with filter and import data back to Blackbaud raiser edge?

Alex Wong

@Su Fang Lim
There are some API that allows you to get list of records (list of constituent, list of gifts, etc). which is “like” an export. the new Query API is more like an export though that function like Query in database view.

However, there is no import API. There are API that allows you to create new records (one at a time), but definitely cannot be counted as importing.

what are you trying to do?

Su Fang Lim

@Alex Wong Thanks for your reply. Basically i would like to export data from blackbaud, and perform updates on the exported data and import the updated data back to blackbaud.

Alex Wong

@Su Fang Lim
sounds to me like you are going this one-time or perhaps occasionally, and the “update on the exported data" is manual.

Then I do not recommend you dealing with SKY API, just use RE database view to export data into csv, update the csv, and then import the data back into RE using RE's database view Admin>Import function.

Su Fang Lim

@Alex Wong Yes, the update data would be manual process. You are right that it would be easier by using the RE database view to export the data to csv with the wizard to configure the query fields, output fields and etc. However, we need a way to automate the process (click a single button then export data and send the job id to other application for manual data cleansing process). But after going through the Query API, it seems like multiple Query API needs to call in order to export.

1. Execute job by query Id (user needs to know which query id to execute, else need to find out from a list of query)
2. Get job status by job id

Do i need to have admin access in order to have the Import function in the RE database view? Because currently i have the write access in the sandbox environment, i can only export the data to csv, however, i do not see the “Admin” from the left menu.

Kindly advise.

Thanks

Alex Wong

@Su Fang Lim
Not sure what data you are trying to “clean up” as it must be data that can be updated using the Admin > Import function.

Admin > Import does not require admin access, but it does require having the Import function given to a security role that you are a part of, if you are not the admin of RE, talk to the person that is the admin and ask. By the way, Import should not be “mess” with if you are not familiar as Import is meant to be used to change huge number of records, so if you do not have the knowledge of using Import and what is needed, I recommend you take a Import class from Blackbaud first. This doesn't have anything to do with the Query API.

• Query API
  • this is easy, you can create a “link” on RE NXT homepage under “Action” tile to “Run Clean Up Export”, which will run a webapp (i.e. assuming it is Power Automate HTTP triggered flow)
  • You would already know what the Query ID is to run, as it will be a Query that is saved in database view already, and you will just hardcode that Query ID into your webapp, it is unlikely (unless someone made a mistake of deleting the Query that was setup to do this work, which is why you will always want to make sure no one else can edit/change the query using query permission).
  • Running the query using Query API will provide your webapp with a csv that you can then email or save to a cloud location (i.e. SharePoint/OneDrive), and notify whoever needs to do the work that a new file is there waiting
• the saved query
  • as mentioned above, you will need to know a lot more about how import works in order to create a query that will eventually be used as the import file for updating records
  • For example:
    • a constituent has title, first name, last name, gender, etc that is a ONE-to-ONE relationship to a constituent record, if you want to make update to these, your query will need to have the “identifier” needed to identify the constituent record (Constituent ID or Constituent Import ID) and then make update (i.e. updating gender and title from first name)
    • a constituent code has the code, start date, end date. that is a ONE-to-MANY relationship to a constituent record (one constituent can have multiple constituent codes). so in order to update constituent code, you will need the constituent code import id that identifies the specific constituent code to update
• the import
  • depending on what you updating, selecting the right import “type” is important and will make importing easier. as long as what you are trying to update, you got the ID needed to identify

Su Fang Lim

@Alex Wong Thanks a lot

Su Fang Lim

@Alex Wong another question.

If i have different clients that have access to the SKY application i registered. When each of them make a SKY API call (eg: query API), how to differentiate client request by account? Is it by the access token only? The subscription key, application id and application secret would be the same for all clients, please correct me if i am wrong yea.

Alex Wong

@Su Fang Lim
I don't know exactly what you trying to ask.

each client/app that made the API call will get responded to

If you have a program that call a sky api endpoint, that program will get the respond back, not any other program.

Su Fang Lim

@Alex Wong Sorry for confusion.

Imagine i have a program, that allow clients to make a call to SKY API(Query API) without going through blackbaud solution.

In the program, can i hardcode the application id, application secret and subscription key to be reused by all clients? The only variable is from client is their access/refresh token?

Alex Wong

@Su Fang Lim:

I think you have some confusion:

call to SKY API(Query API) without going through blackbaud solution

you calling SKY API is going through Blackbaud API endpoints, so I do not know what you mean by not going through their solution. Perhaps you are thinking about Power Automate, but Power Automate is not Blackbaud solution, it is a Microsoft automation platform that is promoted by Blackbaud.

If you have a program, written in any programming language, local application or webapp, you can make calls to SKY API (this is just making HTTP calls with specific header, query parameters, HTTP body). Everytime you make a SKY API call it will have a response (success or fail). In order to make SKY API call, you will need the authorization token obtained by going through OAuth, which involves user logging in and authroizing. Your program must be able to handle the OAuth and your developer application on Blackbaud must have the URI Redirect that goes back to your application.

You cannot make any SKY API call without the auth token and subscription key. If you want to avoid the OAuth that requires user interaction, then you can use something like Postman to do the user interaction (your account on Blackbaud) of logging in and authorizing the connection, this will return the auth token and refresh token, which you can save securely in your program area. auth token is only good for 60 minutes, while refresh token is good for 365 days. However, now that you have a refresh token, you no longer need user interaction to login, if/when your auth token expires and before you issue another SKY API call, you just need to issue a HTTP call to the refresh token API endpoint: https://developer.blackbaud.com/skyapi/docs/authorization/auth-code-flow/confidential-application/tutorial#refresh-access-token

and get a new auth token (along with a new refresh token).

here's a few post that talks about different way to do this:

Su Fang Lim

@Alex Wong. Thanks for your information.

Am i rite to say a refresh token can be shared or reused by different users? When they need to make a SKY API call, we just need to use the refresh token to get a new access token for the subsequent SKY API call.

Alex Wong

@Su Fang Lim
Technically yes, if the other users have everything needed to issue the refresh token API.

I wouldn't recommended, and would suggest you are careful with info such as subscription key, application id (client_id), client_secret, and tokens. Each authorize developer should really have their own account with rights appropreiate for their work.