Sharing my Power Automate Flows: Power Automate flow to create gift report based on date range from MS Forms (a Pivot Table workaround)

Nicole Holt

This is a solution that replaces a pivot report from database view to report on in-kind gifts received during a given time period. The reporting for this flow reflects the fact that our fund tracking is different for in-kind goods than for monetary gifts: we use custom fields for in-kind gifts because of a particularity in our data structure. Others may be able to replace the custom field item with fund information.

What you'll need:

• Blackbaud NXT Power Automate connector
• Microsoft Forms
• Microsoft Excel
• Microsoft Teams

Skill level: intermediate

Starting at a form that has been posted to a Teams group, staff without RENXT privileges can pull a giving report based on date parameters:

MS Forms document with inputs for start date, end date, and submit button

This report is presented as an Excel file (MonthlyReportTemplate.xlsx) with 3 tables: the gifts pulled through “List Gifts” action in Power Automate, a table that uses “sumif” formulas to replicate a pivot table, and an audit table to ensure that the number of gifts expected are the number of gifts present.

The flow overwrites the Excel template file with gifts made during the date frame specified in the form and saves as a new document to a Sharepoint folder. When the new document has been saved, a message will be posted to the Teams channel tagging the form submitter advising that the report is complete.

ReportingWorflowMap.pdf

GiftReportingMSFormstoMSExcel_20220317161337.zip