Gift split system ID via Power automate

@Ashley Gelderblom

id field inside the gift_splits property is the gift split system record id

https://developer.sky.blackbaud.com/api#api=58bdd5edd7dcde06046081d6&operation=ListGifts&definition=GiftSplitRead

if you did not import gift into RE where you specified the gift split import id, then the gift split import id will always be the same format (00001-553-0000058274) with the gift split system record id at the suffix (i.e. 58274)

with SKY API for get gift list, you can only get 5000 gift records per call, so if you have more than 5000 gift records to get, you will need a do until.

I have 1.7M gift records that I sync using power automate, so yes, you can use do until for many more loops than you did, you need to change the do until limit, don't use Timeout but specify a Count that is enough to get all the records. (i use my limit count at 500, meaning 500x5000 = 2.5M gift records, when # of gifts exceed 2.5M, i'll increase this Count), unfortunately, this Count cannot use a variable, not allowed

SKY API limit is 25K API calls per 24hr period. NOT # of records returned per 24hr period.

My total # of gifts require a FULL gift load to loop 356 times, meaning making 356 api calls only.

@Ashley Gelderblom
I use OPENJSON and it takes few seconds to handle 5000 records at a time: https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16

As for SQL table, I use 4 different tables for all the info that comes back from the list gift API endpoints: gift table (for all 1-to-1 relationship gift data), gift soft credit table (for all gift soft credit data since 1 gift can have 0, 1 or more gift soft credit), gift splits table (for all gift splits on campaign, fund, appeal, package, and split amount, since 1 gift can have 1 or more splits), and gift fundraisers table (for all gift fundraiser/solicitors, since 1 gift can have 0, 1, or more fundraisers/solicitors). Combine that with the RE database Schema documentation to know the data type used for the fields (as you cannot tell that info from the API endpoint).

Below is my table definition. Note that I do not define the fields being nullable or not, and I do not use foreign key as it makes managing bulk insert and update difficult.

CREATE TABLE prod_gift (
gift_id INT PRIMARY KEY,
gift_amount NUMERIC(11,2),
constit_recordid INT,
gift_date DATETIME,
batch_number VARCHAR(50),
gift_status VARCHAR(20),
is_anonymous VARCHAR(5),
constituency VARCHAR(100),
lookup_id VARCHAR(50),
balance NUMERIC(11,2),
payment_method VARCHAR(20),
gift_subtype VARCHAR(100),
gift_type VARCHAR(30),
receipt_amount NUMERIC(11,2),
gift_code VARCHAR(100),
date_added DATETIME,
date_modified DATETIME,
ack_date DATETIME,
ack_status VARCHAR(20),
letter VARCHAR(100),
post_date DATETIME,
post_status VARCHAR(10),
receipt_num INT,
receipt_status VARCHAR(20),
reference VARCHAR(255)
)

CREATE TABLE prod_gift_soft (
id INT PRIMARY KEY,
amount NUMERIC(11,2),
constit_id INT,
gift_id INT
)

CREATE TABLE prod_gift_splits (
id INT PRIMARY KEY,
gift_id INT,
amount NUMERIC(11,2),
appeal_id INT,
campaign_id INT,
fund_id INT,
package_id INT
)

CREATE TABLE prod_gift_fundraiser (
id INT IDENTITY(1,1) PRIMARY KEY,
gift_id INT,
constituent_id INT,
amount NUMERIC(11,2)
)

@Ashley Gelderblom
INSERT action will insert ONE row at a time, not good.

use Execute SQL action and INSERT statement directly:
INSERT INTO….
SELECT * FROM OPENJSON…

If you want to create view, just do that directly with CREATE VIEW sql statement using SSMS

For data transformation and table relationship management, it is done in Power BI