Pulling in Constituent First Gift Date on Gifts Export?

Clarissa Rice Hampton 2

Hello!

We run a Daily Gifts Report for the Advancement team that includes a lot of information, but one of the fields that has been requested to export is the First Gift Date. To accomplish this, I created a Constituent type Query and Constituent type Export that pulls in as the needed gift and constituent information. However, this creates a bit of a mess when a constituent gives more than one gift in a day because the multiple gifts are all exported to the same row- since it's exporting on the Constituent object, not the Gift object. It also requires us to set the number of gifts to export, resulting in a number of blank fields that our team has to scroll past to see other information.

Has anyone had success running a Gift type Export that includes the constituent's first gift date? This is an ongoing issue for our team, and I'm starting to lose sleep over the number of workarounds I've built to be able to include the First Gift Date field. Help!

Alex Wong

@Clarissa Rice Hampton
You cannot include First Gift (constituent summary type of info) in a gift query/export. This you already know and there's no “RE-way” to handle this.

To handle your situation, there are many methods, ranging from fully automatic to completely manual. I don't know what workarounds you have tried, but since you made this post, it is likely the completely manual route. To make a “solution” out of this problem of yours, it depends on skills you have (or willingness to learn new skill) and tools that is available to you (or your company/IT's wilingness to pay for new tool)

• Are you on RE NXT or regular RE?
  • If RE NXT, do you have experience with SKY API?
• Do you have RE:Queue module?
• Do you have Power BI or other reporting/visualization tools and experience (i.e. Tableau)
• Do you know Excel forumla like XLOOKUP (better) and/or VLOOKUP (old formula that has been replaced)?
• Do you have experience with automation tool such as Power Automate?

The more manual route would be:

• Setup:
  • Save a consitutnet Query (gift option > soft credit to both full amount)
    • filter on gift date added = yesterday (assuming your daily gift report is reporting on gift added yesterday), or gift date added = today (if you are doing daily gift report at the end of the day after all gift entry is done)
  • use above query in Constituent Export
    • output Constituent ID and First Gift's info you need
  • Save a gift query filter on gift date added same as the constituent query above
    • either output directly from gift query (do this only if you are good in querying and know when duplicate rows will happen and handle appropreiately)
    • OR use the gift query in gift export
    • output all fields you want but you must include Constituent ID (use for lookup)
  • Create an excel file
    • copy the constituent export (Constituent ID and First Gift Info), give it a good name for the worksheet (i.e. Constituent)
    • copy the gift export, give it a good name (Daily Gift)
    • In the Gift worksheet, add column(s) at the end for all First Gift Info (i.e. First Gift Date, First Gift Type, First Gift Amount), use XLOOKUP formula to lookup by the Constituent ID for the first gift info from the Constituent worksheet
• Everyday:
  • export the saved constituent and gift worksheet
  • clear the content of the constituent worksheet in the excel and paste into the worksheet the new constituent export data (do not use right-click delete, just highlight all cells and use keyboard Del key, right-click delete will mess up formula)
  • clear the content of the gift worksheet EXCEPT for the First Gift info column(s) that has the XLOOKUP formula
  • paste the new gift exported data (they will need to be exactly the same columns which will be true if you don't mess with the RE query/export, and extend or delete rows from the XLOOKUP columns as you will have less or more gift rows depending on daily entry.
  • Copy the whole Gift table and paste to a new worksheet using paste value, so no formula remains, this new worksheet/excel file is what you will send out.

If you interested in more automated method, please confirm the questions about skillset and tools, and I can help you formulate a better plan.

Clarissa Rice Hampton 2

@Alex Wong Thank you, thank you for the manual instructions. They successfully simplify the process, making it a much more manageable daily process for our gifts processor.

I have the most basic experience using Power Automate, but am very interested in learning more and developing my skills to help automate more of our daily tasks. Here are the answers to your questions:

• Are you on RE NXT or regular RE? Yes, we are using RE NXT
  • If RE NXT, do you have experience with SKY API? Yes! Our organization has automated processes through Omatic and I'm currently integrating iWave into RE NXT.
• Do you have RE:Queue module? I'm not sure. I don't see it in my view, but perhaps the admin has restricted my access. Where would I find it?
• Do you have Power BI or other reporting/visualization tools and experience (i.e. Tableau) Limited, but I'm eager to learn
• Do you know Excel forumla like XLOOKUP (better) and/or VLOOKUP (old formula that has been replaced)? Yes, but I've found it's usually faster to just sort by the identifier then, copy/paste. Maybe I'm lazy?
• Do you have experience with automation tool such as Power Automate? Very limited! I copied a couple of templates to create the most basic daily gifts report that include constituent name, hyperlinked Constituent ID, gift amount, and date delivered in an email to my inbox. I also copied a template to merge tax letters directly from RE, but like the report, they are too simple to actually replace the existing manual processes.
  

Alex Wong

@Clarissa Rice Hampton
RE:Queue is a paid module that exists in RE database view > Admin > Queue. It allows the scheduling of export from Query, Export Report, Admin, which if you schedule the export into the FTP folder, then you can automate getting the files from the FTP folder.

sort by RE ID then copy and paste data “works” when you are careful. You will quickly run into issue when there are more rows in one table than the other. (multiple gifts for same constituent). XLOOKUP will ignore all of that and always ensure the right data is retrieved.

if you have done some of the power automate and tried the template, then you can move on to the next stage of more advance automation and reporting.

Automation for a daily gift report that has first gift info (not most efficient, but good enough if you don't have hundreds or thoursand of gift per day).

• use daily gift report template, there are many out in community and template showcase
• Use Query API to run the saved constituent query (that has the first gift info) mentioned in previous post
• for each gift in your daily gift list
  • use Filter Array to filter on the constituent id of the gift from the constituent list you got from Query API
  • add the first gift info from the filter array action into the array of gift details
• create html table from the gift array to add to the daily gift email
• or create an excel file from the gift array and attach to the email

here's some links that will help:

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

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

https://community.blackbaud.com/forums/viewtopic/426/65782

You can also join the monthly Power Auotmate user group and Power BI user group:

https://community.blackbaud.com/events/category/66

Mark Guncheon 2

@Clarissa Rice Hampton Can you place the first gift info as the final fields in your export? I would think then that the blank columns for those with only “one” first gift would not matter.

If you could send a sample of what info your team needs in a report, that might help.