Missing Constituent Fundraiser Field

Ryan Powell

I'm creating a gift log in Power BI and would like to include both the fundraiser assigned to the constituent along with the fundraiser that lands on the gift. I've been able to create a gift fundraiser column but haven't been able to source the Constituent assigned solicitor(s) field . Any suggestions?

Matt Thacker

Hey @Ryan Powell, unfortunately API endpoint for getting an assigned solicitor is a single call endpoint, so the constituent ID needs to be passed into the API call as a parameter to get the desired information. The API call is built into the connector (I've taken a snippet from the source code below) but it's commented out. To use it, you'd need to go into the .PQ file, uncomment that line out, and then write a custom function in Power Query that will make the necessary API calls with the IDs that you pass in. Essentially, write an iterator that will go row by row of the constituents table and make the separate API call.

Ryan Powell

@Matt Thacker thanks for this great info. Being a bit a newbie to BI it is a bit foreign to me but I'll get there:)

Alex Wong

@Ryan Powell
Another way to do this is to use Power Automate for this. (I'm not sure your business use-case so hard to say what's easier/better).

Asssuming you just want to create a gift log, not creating visualize of the data, then an excel will do. You can manually create an excel file that has all the columns you need in a table (i.e. Gift Date, Gift Fundraiser, Donor Assigned Solicitor, etc). Then in PowerAutomate, you can either use the Gift List to criteria on all the gifts that you want to log, or use the webhook API to “log” all gifts that get created at creation time. How you want to do this depends on your business use-case.

Once you got a list of gift you want to log, Call the Get Gift using the Blackbaud Gift connector (if you are using the Get Gift List, you won't need to do this as the gift data is returned already), as the “fundraiser” is already part of the data that is returned (also take care of multiple as there can be multiple fundraiser on a gift with different amount). As for the assigned solicitor, you can use action List constituent fundraisers action in the Blackbaud Constituent connector (again, be aware that a constituent can have more than one assigned solictor).

Once you made all the needed “calls” just use the Add a row into a table action in Excel connector to add each piece of data into the excel table.

Now that you have data sourced into a single cloud location, if you still want to do Power BI visualization, you can use Power Query in Power BI to pull the data from the excel in SharePoint.

Ryan Powell

@Alex Wong thank you for this great info! For now, since our solicitor list is fairly static I just added an excel doc to my model. I'll work through the steps provided but may have some follow up questions?. Thanks again for the guidance!

Lawrence Kinkopf

@Matt Thacker Hi Matt! Is this solution still valid? I am working on a donor retention dashboard in Power BI, and I want to segment retention based on if constituents have an assigned fundraiser or not.

Alex Wong

@Lawrence Kinkopf @Ryan Powell
You can now do this much easierly via Query API.

You can create a Power Automate (or any other method of automation) that run a Constituent Dynamic Query on demand. Filter on Relationship > Assigned Solicitors > Specific Record is not blank. Output will be Constituent System Record ID, Assigned Solicitor Specific Record (this is the constituent system record id of the assigned solicitor). and any other assigned solicitor's information (i.e. date from, date to, campaign, fund, appeal, solicitor type, etc). This will produce a list of ALL assigned solicitor relationship in your database, without the need to call the SKY API endpoint one per constituent.

This PA flow can be triggered by HTTP web call, and return a JSON of the data, which can be directly consumed by Power BI using the Web connector, and receive a full list of assigned solicitor as a table to use in Power BI modeling.

I plan on creating a template to share this method of PA working PBI soon.

Alex Wong

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

Template for doing Query API power automate flow to get data on assigned fundraiser/solictor