Years Giving and Years Consecutive

Chris Zello

I'm trying to use Power Automate to access a List to create an Excel file with the ConsID, Name, Years Giving and Years Consecutive fields in columns. When I run this on-demand, I'd like to have it save the results in a SharePoint folder. (No need for an email notification.)

I've been looking at the Power Platform template showcase as a reference to find something that creates an Excel file and then saves to SharePoint. Does anyone have any guidance to find something similar?

In my flow, what operation should I choose to add columns for the total_years_given and consecutive_years_given fields to write to my Excel columns? I see these in the Constituent API under Lifetime giving (Get).

Thank you.

Ben Lambert

Hi @Chris Zello,

Take a look at these actions within the Blackbaud Raiser's Edge NXT Gifts connector:

• https://learn.microsoft.com/en-us/connectors/blackbaudgifts/#get-constituent-first-gift
• https://learn.microsoft.com/en-us/connectors/blackbaudgifts/#get-constituent-greatest-gift
• https://learn.microsoft.com/en-us/connectors/blackbaudgifts/#get-constituent-latest-gift
• https://learn.microsoft.com/en-us/connectors/blackbaudgifts/#get-constituent-lifetime-giving

These actions in the connector call the underlying endpoints you've found in the Constituent SKY API. Given the semantics are oriented around gift information, we felt that they were more appropriately exposed as part of the Gifts connector rather than the Constituents connector.

Alex Wong

@Chris Zello
What do you mean by “to access a List to create an Excel file”? List as in RE NXT webview's Constituent List? (meaning that list's filter for constituent are those you want to create an excel file for?)

If yes, using the Blackbaud List Constituents Power Automation connector action allows you to get the Constituent that is filtered by that list. (you may need to work with looping multiple calls to List Constituents depending on how many are in the list, 5000 is the max limit per loop/call).

The easy (but slow way) to do this would be to then loop through each constituent record, calls List gifts action for the constituent's id. From there you will have a list of gifts for this constituent, which then you can do some logic to get your “years giving” and “years consecutive" (i'm not sure what you mean by “years giving” and “years consecutive”).

Once you have the data you want, you can create an excel file, and add to the excel file (see below for what I do to create excel file in PowerAutomate), row by row. if you have hundreds and thousands of constituent in list, it will be a very slow process.

To do this faster, you will need to use other services (i.e. SQL Server as data warehouse dump, SharePoint List as data dump and then query on the List, or data dump into excel (using Office Excel Script, which is similar to TypeScript)). But all these other methods will require more technical knowledge on your end.

To create an excel file in SharePoint, I first save manually a “blank” excel file in SharePoint. Then in PowerAutomate flow, use the SharePoint Get file content action:

Then use SharePoint Create file action and use the output body from the Get file content action.

Then the new excel file is created. (I then use Run script action in Excel connector to do what I want to do in terms of data populating the excel). You can learn from here:

https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration

Excel Script is very powerful, there are some limitation, but it can work with CSV to Excel, JSON (which is what you get back from Blackbaud connector's list actions) to Excel and does it very efficiently. However, this requires technical knowledge on how to write TypeScript (similar to JavaScript, a webdev language).

Alex Wong

There are PAID service out there that does the convert csv to excel, json to excel, etc for you (i.e. one I know about is Encodian). So if you are not a developer, using these service may help you handle the business automation scenarios low-code/no-code.

Ashley Moose

@Chris Zello In addition to all of Alex's great feedback, here are a couple links to similar community posts that may be helpful:

https://community.blackbaud.com/forums/viewtopic/586/53047

https://community.blackbaud.com/forums/viewtopic/586/57215

https://community.blackbaud.com/forums/viewtopic/586/52046

Chris Zello

@Alex Wong @Ben Lambert
Yes, I was referring to creating a Constituent List in webview, and using those names to get the fields I'm referring to. The First, Greatest, Latest and Lifetime Giving are not the same thing.

In NXT in the Giving panel in NXT, the numbers show for the fields I'm trying to get. I have located them in the Constituent API under Lifetime Giving (Get), where they are named total_years_given and consecutive_years_given. I'm simply trying to use the math that is built into RE, rather than do anything fancy.

I will look in more detail at the info you've provided that allow me to pull the fields out into a SharePoint Excel document. That looks very helpful.

Thanks.

Chris Zello

@Ashley Moose

Thank you Ashley. These were very helpful. Where I'm stuck with this now is with the Create File step's final field “File Content”. What goes here? Thanks.

Chris Zello

@Ashley Moose
I put in Body, and when testing this creation. It failed during my output section (step 3), before getting to the Create file. So some progress. Thanks.

Chris Zello

@Ben Lambert
This is the Constituent Endpoint Reference that I was referring to:

Alex Wong

@Chris Zello
Where I'm stuck with this now is with the Create File step's final field “File Content”. What goes here? Thanks.

Are you referring to the Create File action for the excel file? The File Content is the dynamic field from the Get file content action:

Ashley Moose

@Chris Zello
If Alex's last suggestion didn't work, can you reply back to the post with a screen shot of the error where it is currently failing?

Chris Zello

@Alex Wong @Ashley Moose
So now I'm even more confused about the individual steps I need to take to get this to work. It seems like there are two steps involved to get the CSV file to save to SharePoint. Is it correct that I have one step to output/select the fields and then a second step to create & save the field in the CSV file?

I do not see the same options for content that you are showing Alex to choose what goes into the File Content field in my step named “Create file”. Instead, I see this:

Alex Wong

@Chris Zello
here's what i think your steps should be:

First you need to create a SharePoint location to have a BLANK excel file. let's assume it is https://bbtest.sharepoint.com/teams/BusinessAutomation (path: Documents > Apps > Template > Template Excel.xlsx)

• call the constituent list to get constituent by the webview list
• use Get file content action in SharePoint connector on https://bbtest.sharepoint.com/teams/BusinessAutomation (path: Documents > Apps > Template > Template Excel.xlsx)
• use Create file action in SharePoint connector. Assuming you use the same SharePoint team for this: https://bbtest.sharepoint.com/teams/BusinessAutomation (path: Documents > Blackbaud Constituent Report > Constituent Giving Analysis.xlsx)
  • You will want to make Constituent Giving Analysis.xlsx having some random or date+timed part of the file name so you are not creating the same file that will be an error (i.e. Constituent Giving Analysis 2023-03-07 1219121928.xlsx (where 2023-03-07 is from utcNow() expression and 1219121928 is a random number generated).
  • In this action, you will use the File Content dynamic field from the Get file content action above for the File Content field of this Create file action.
• Use the Create table action in Excel connector and target the file created in the Create file action above
  • File field of Create table action should be using the Path dynamic field from Create file action, but need to use an expression to replace/remove the /Shared Documents (as it will be prefixed with this, but File field of Create table action does not want this)
  • Table range field is in A1 notation, so since you have 4 columns, you can do A1:D2 (2 rows 4 columns) and give it a Table name and the 4 columns header name in the Columns names field
• Now, you will have your Apply to each constituent from the constituent list of returned, inside Apply to each, you will:
  • call the Blackbaud API for the constituent giving summary
  • use Add a row into a table action in Excel to add to the excel table created above and add the 4 data point into the table.

Heather McLean

@Chris Zello

Would you be willing to share this project at user group on 3/21?

Chris Zello

@Alex Wong Thank you for all the info Alex! I was able to make my way (very slowly) through most of this. The last bullet point (Apply, API) was something I couldn't figure out.

Chris Zello

@Heather McLean Yes, I would be glad to share what I have done so far. Know that it doesn't yet work. So as long as no one is expecting what I'm showing to work, I'm fine showing where things stand.

Known issues: I was unable to find the fields for Years Giving and Years Consecutive, so I've only chosen ConsID and Name.

Also, I don't understand the last bullet point where Alex stated the Apply to the API and pulling in each row. I have an API error.

Alex Wong

@Chris Zello
There is no Blackbaud connector action for the Lifetime Giving endpoint (at least I didn't find it), so you need to use the Send an HTTP request action in the Blackbaud SKY Add-ins connector:

Then you will need to process the returned info in JSON with a Parse JSON action, you can get the schema by copying the sample json on the API page and using the Parse JSON Generate from sample option. There is a very detailed post on how to use the Blackbaud HTTP Request action by Ben Regier: https://community.blackbaud.com/forums/viewtopic/586/62295?post_id=245531

The basic flow in the apply to each looks like below.

Heather McLean

@Chris Zello - that's great! We look forward to seeing this Work in Progress tomorrow!

Chris Zello

@Alex Wong Thank you. Just a slight clarification, I'm not seeking Lifetime Giving. I'm looking for the fields that count Consecutive Years and Years Given. I see in your screenshot that you are showing these two field outputs.

Alex Wong

@Chris Zello
The 2 fields you looking for is in the Lifetime Giving's API endpoint.

Chris Zello

@Alex Wong Thank you again for all your help today in the Power Automate User Group meeting.

Before I share this final version in the showcase, I'm trying to work out the final step for where the data is recorded on the file. I made an update to the name of the file when it is written to remove the time portion, by using the expression formatDateTime(utcNow(), 'MM-dd-yyyy')

I wasn't able to get that expression to go into the final step though.

What is odd is that when I tested this, it created the new file where it wrote the data AND it also wrote the data to my “template” file. So in two places!

Is there a way to not have it show in the “template” file?

I figure that when someone manually runs it, having the data pulled once a date, should be enough.

Thoughts?

Alex Wong

@Chris Zello
Add a row into a table action will only add row to the excel file being targetted, so it should not be adding to both template file and the new file created, I'm guessing maybe some confusion from previous run that you didn't delete the rows off the template file.

so the way to deal with new excel file that is created by flow and adding row into it is to use JSON to add the rows, as the flow (when editing) does not know how the new excel file look like to pull up what fields/columns are in the table. Here's my test run:

Every steps are basically what we went over in the User Group call, here's what the Add a row into a table looks like:

My template excel has 4 columns: Constituent ID, Donor Name, Total Year Given, and Total Consecutive Year. The table name is DonorGiftTable.

In the File field, after selecting the template file, don't select a Table from the drop down, remove the file name (with the extension). Then use the dynamic content of Name from Create file action.

Because this file does not exist yet, Power Automate will not know anything about this file, including Table and Columns in it. So you will type in the name of your table (in my case, I typed in DonorGiftTable). Then you will use JSON in the Row field:

Notice the JSON used has Column name in double quotes. For Consituent ID and Donor Name, they are also in double quotes as they are string. For Total Year Given and Total Consecutive Year, no quote is needed, just the dynamic fields for the total_years_given and consecutive_years_given. JSON text as follow for you to copy and update:

{
"Constituent ID": "@{items('Apply_to_each')?['lookup_id']}",
"Donor Name": "@{items('Apply_to_each')?['name']}",
"Total Year Given": @{body('Parse_JSON')?['total_years_given']},
"Total Consecutive Year": @{body('Parse_JSON')?['consecutive_years_given']}
}

Additional Note: when you selected the A1 to D2 to create the table, excel template file thinks row 2 is real data, so when you add row, it start adding at row 3. You can deal with that by highlighting the “empty” row 2, and delete that row.

Here's what a run produces (left is new excel file create on the run, right is the template excel file)