Trying to export gifts and send to one drive

Samuel Ofosuaah

Hello, I am attempting to pull every gift from 2008 to today in order to automate my tableau dashboards, I am fairly new to power automate but I have been trying to create a flow that allows me to do this. So far what I have created below is the closest I can get yet whe I run the flow it keeps on failing. I am almost at my wits end! What I intend to do is to get gifts ID, gift value and appeal ID from every gift above 1 in value. And send the completed excel speadsheet to sharepoint. If anyone has had experience with doing something like this any help/advice would be greatly appreciated.

Many thanks in advance.

Params

Alex Wong

@Samuel Ofosuaah
power automate isn't too hard to use, that's until you get to a lot of ONE-to-MANY relationships, and if you don't understand that relationship you wil have a hellish time trying to get data and report on it.

So first, let's understand the relationships:

• ONE gift record can have 0 to more than one soft credit
• ONE gift record can have 0 to more than one gift fundraiser (gift solicitor in database view terminology)
• ONE gift record must have at least 1 and can have more than one gift splits (CFAP: campaign, fund, appeal, package, and split amount)

So from what you want to get: gift id, gift value, and appeal id (you are probably goingt o want to care about constituent system id, gift date, gift type at the minimal too), you will need to care about the gift split one-to-many relationship, UNLESS, your organization does NOT allow the use of split gift where one gift record can go to different CFAP.

I am going to assume, by appeal id, you do not mean the integer appeal system record id, but the Appeal ID that is user defined (alphanumeric up to 20 characters).

You will NOT want to deal with so many apply to each, well actually, not even ONE apply to each. You will not want to write tens/hundreds of thousands of gift record ONE at a time into the excel, so you will want to check out this post: on how to save to excel quickly. https://community.blackbaud.com/forums/viewtopic/426/65782

In a nutshell:

• You will need to separate get list of appeals, and save that as a separate excel file
  https://developer.sky.blackbaud.com/api#api=nxt-data-integration&operation=GetAppealList
  • this “table” will give you the mapping of appeal system record id to appeal id and appeal description, etc.
• You will need to save 2 excel file for gifts: gifts and gift splits
  • gift excel file will have row data that is ONE-to-ONE relationship: gift system id, constituent system id, gift date, gift amount, gift type, etc
  • gift splits excel file will have rows data that is ONE-to-MANY relationship with a gift and have the following columns: gift system id, gift split id, campaign system id, fund system id, appeal system id, package system id, split gift amount.
    • gift system id of this gift split table will map to the gift system id of the gift table

Complicated enough? You are in luck!!!!! Query API just became Generally Available for all user: https://community.blackbaud.com/blogs/69/9628

there are some post on community on how to use Query API you can look for, so basically you can setup a query in database view for all the data you want, then use Query API to obtain the data in CSV and save to onedrive directly for Power BI to consume.

Samuel Ofosuaah

@Alex Wong
Thank you very much!!!

This makes a lot of sense. and I have been able to use the query api for my exports.

Thanks,

Sam