Create a Sharepoint List from a Gift or Constituent Query

Kristina Pollard

End Result: Send letters that have already been acknowledged but are over a daily summary threshold. (1 transaction totaling $10k but applied to multiple funds in the same number of transactions)

Good afternoon all, I am trying to create a flow that will house a daily summary of giving by a single household or organization in a SharePoint list that I can then use to create a mail merge of letters from our CEO. These gifts have already been acknowledged through the standard acknowledgement process but the CEO sends out another letter for gifts over $10k on a monthly basis. I would like a list that will automatically write a new line to a sharepoint file every time the total daily giving for a single Raiser's Edge ID meets the threshold.

We do not use gift splits in our process as the reporting on them is too cumbersome. We enter a split to separate funds as individual transactions using all the same payment information into a batch.

Does anyone have ideas on how to make this work? I have written so many different versions to this, but am VERY new to power automate, et al and get stuck on so many of the individual actions. I also keep trying to repurpose existing flows, but that has yet to work either. Any help would be greatly appreciated. Thank you all and happy holidays.

Dan Snyder

@Kristina Pollard I have a couple of questions and some possible solutions.

First, if a donor gave $5K on the 5th of the month and another $5K on the 20th, would they be getting this letter or only if they give the total in a single day? If you are just looking for the total in a month, then a constituent list that looks at those who have given $10K+ in the previous month would seem to work for a scheduled flow. If you are looking for daily it gets a little tricky because you may be using the gift the date was added to RE as opposed to the gift date. I suspect there is a way to do this, but might need someone smarter than me to answer that. Maybe @Nicole Holt @Austen Brown @Alex Wong @Ben Regier @Glen Hutson @Ashley Moose @Hallie Guiseppe can help?

The other question option is more on the gift entry side. Could you send the initial standard acknowledgement using the Not Receipted/Receipted option to track their being sent and then only leave those gifts that are or total $10K+ as not acknowledged? We have moved to that for situations where more than one letter is needed (Trustees and large gifts mostly).

Great question though and look forward to seeing what others come up with.

Alex Wong

@Kristina Pollard

What Dan ask is valid and needs to be considered. What is the exact criteria for the additional acknowlegement? Gift Type? (cash pay-cash, or cash / pledge) single gift of $10K+ or multiple gifts over certain duration sum up to be $10K+. What about Soft Credited gift?

Once you defined who exactly are that needs to be added to the SharePoint List, then it's pretty easy to gether info from SKY API and add to the SharePoint list (provided you already create the SharePoint List with columns you need the CEO to see).

As the SharePoint List is “static”, have you consider if the list needs to be updated or added to. For example: if John Smith qualifies to be added to the list and did. at a later date, he gave more money, should the data in the list be updated OR should a new entry be added? (this will depends on your business process for this list)