GivingTypesData Python Script¶

See also

Automate this Script¶

To get this script to run every morning automatically, you can put it into the MorningBatch Python script. Open the existing or create a new Python script called MorningBatch. Add the following line of code.

model.CallScript("GivingTypesData")

Save the script. It will run the next morning between 4:30 AM and 5:30 AM. Of course, you can always manually run this script whenever you want.

See also

model.CallScript()

Explanation of the Code¶

The goal of this code is to create ContributionTags that categorize contributions into various Type categories such as: Online, Recurring, Mobile, Offering plate, Mission Trips, Bank Drafts, Stock, and Non-Tax Deductible. These categories should not overlap; a Priority scheme is used to decide the single category for a contribution that may fit into more than one Type. This way, the total of Types should reconcile with the sum of contributions. The code further divides each category into three different date ranges and three different fund sets. The final report contains three tables for the three date ranges. Each row in a table represents a summary of a different Type of contributions with three columns for different fund sets. These Contribution Tags become a snapshot, produced at least once a day during the morning batch, allowing the final report to render swiftly.

#Roles=Finance

from System import DateTime

Line 1 limits only those users with the role Finance to run this script.

Line 3 allows the code to use .NET DateTime functionality

Initialize¶

# Initialize global data
MaxDate = DateTime.Today
NameOfChurch = model.Setting("NameOfChurch")
GivingTypesData = model.DynamicData() 
GivingTypesData.Created = DateTime.Now
ValidationReportList = []
Priority = 0

Base = model.DynamicData()
Base.MinDate = MaxDate.AddDays(-365).ToString("d")
Base.MaxDate = MaxDate
if NameOfChurch == "Mount Pisgah UMC":
    pass # negative amounts are OK
else:
    Base.FromAmount = 0 # no negative amounts

This code initializes global variables:

MaxDate

The the last date for any contribution to be reported on. The current date is used.

NameOfChurch

Used later to execute particular, church specific lines of code.

See also

Setting()

GivingTypesData

A DynamicData object which will contain all of the SearchParameters and store them in a file with the run date.

ValidationReportList

An array (list) of lines of HTML used to display a validation report at the end of the script.

Priority

Priority tracks the creation order as the various SearchParameters build ContributionTags.

Base

A DynamicData object which will hold the common SearchParameters for every Contribution Type, including the date range.

See also

model.DynamicData()

Lines 13-15 set the date range used for the Base to the Last 365 days. This range is inclusive of any contribution in the other date ranges since they are shorter.

Lines 16-19 sets the Base.FromAmount to 0 to exclude negative contributions. If your church needs negative contributions because of historical data that used negative contributions for reversals, then change the name of the church to your church (found in the Settings on the Admin menu).

CreateDateRangeTags¶

def CreateDateRangeTags():
    SearchParameters = model.DynamicData()
    SearchParameters.NonTaxDed = -1 # both tax and non tax
    SearchParameters.MaxDate = MaxDate.ToString("d")

    SearchParameters.MinDate = DateTime(MaxDate.Year, 1, 1).ToString("d")
    model.CreateContributionTag('GtrYtd', SearchParameters)

    SearchParameters.MinDate = MaxDate.AddDays(-30).ToString("d")
    model.CreateContributionTag('Gtr30', SearchParameters)

    SearchParameters.MinDate = MaxDate.AddDays(-365).ToString("d")
    model.CreateContributionTag('Gtr365', SearchParameters)

Lines 21-33 define a function called CreateDateRangeTags. A DynamicData instance called SearchParameters is created and defines the date ranges. The search parameter NonTaxDed = -1 allows all contributions to be returned for each date range. This is required since the default is Tax-Deductible.

The function creates the three date range ContributionTags, modifying the MinDate for each range. GtrYtd (year to date), Gtr30 (last 30 days), and Gtr365 (last 365 days). A call to CreateContributionTag passes the name of the tag and SearchParameters as arguments.

See also

model.CreateContributionTag()

NextPriority¶

def NextPriority():
    global Priority 
    Priority += 1
    return Priority

Lines 35-38 define a function called NextPriority which increments a global variable named Priority in the function call. Priority prevents a single contribution from falling into more than one contribution Type category on the final report. Lower numbers take priority over higher numbers within a fund set column.

Line 36 allows the global variable Priority to be assigned to within the function. Normally in Python functions all variables are local to the function and although global variables can be read and used within a function, they cannot be assigned to.

CreateTag¶

def CreateTag(fundSet, typeCategory, searchParameters):
    TagName = 'Gt{}-{}'.format(fundSet, typeCategory)
    NamePriority = '{}-{}'.format(TagName, Priority)
    dd = model.DynamicData()
    dd.parms = searchParameters
    dd.link = '/ContributionsJsonSearch/{}/{}'.format('GivingTypesData', NamePriority)
    GivingTypesData.AddValue(NamePriority, dd)
    ValidationReportList.append('<h4><a href="{}" target="validate">{}</a></h4>'.format(dd.link, NamePriority))
    ValidationReportList.append('<pre>{}</pre>'.format(model.CreateContributionTag(TagName, searchParameters)))

Lines 40-48 define a function called CreateTag which creates a ContributionTag controlled by the searchParameters passed into the function.

Line 41 constructs TagName using the prefix Gt, the fundSet, and the typeCategory. The fundSet is one of: AllFunds, MainFund, and Other defining the three main columns of the report. Line 42 constructs NamePriority from TagName and Priority uniquely identifying a unique set of SearchParameters.

When adding to the same tag with a different search, the TagName stays the same, but the NamePriority is unique because Priority is unique for each search section. So a single TypeCategory can contain more than one Priority when multiple searches are used to construct and then add to the RowType tag.

The last two lines of the function save information to the ValidationReportList which is displayed on-screen when a manual run is complete.

CreateTags¶

def CreateTags(fundSet):

Lines 50-173 define a function called CreateTags. Plural because this function creates all of the tags needed for a given fundSet which defines a column in the report. The singular function CreateTag is called in multipe sections.

Before we move on to the various sections, the following will help you understand what what we mean by section in this function.

Within the CreateTags function, there are multiple sections separated by blank lines. Each section will either create a new tag or add to an existing tag. A section starts with an assignment to RowType which is associated with a row in the final report. Then the SearchParameters for the RowType are built. Finally, the section ends with a call to CreateTag.

Some sections are preceded with the comment, # church specific. If this comment is present, then there may not be any results for this RowType for your church. As such, the section can be deleted or modified for your church.

The order of execution of the various sections is significant. Some contributions can fit into the SearchParameters for different sections. However, it is important, in the final report, that a given contribution appear in only one row. The lower priority numbers take precedence over higher numbers for a given column’s FundSet. For example, if a contribution matches the SearchParameters in both priority 103 and 104 for two different RowTypes, then in the final report, the gift will only show up in Priority 103 for the first RowType and not in 104 the latter RowType. This way, the sum of the individual lines will match the Total line. You should take care to decide which RowType is more important to associate with a contribution and place the section for the more important RowType ahead of another possible RowType. The row order for dispay on the final report is controlled independantly from this priority ordering scheme.

    RowType = 'Mobile' 
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.Source = 1
    CreateTag(fundSet, RowType, SearchParameters)

Mobile

Lines 51-55 create a tag called Mobile which includes contributions coming from the TouchPoint Mobile app (SearchParameters.Source = 1).

Note Line 52 which is used at the begining of each search section. The call to model.DynamicData(Base) creates a new set of search parameters using Base as the starting point.

Note the call to NextPriority on Line 53. Each search section calls this function to increment the Priority which is what makes it unique to each search.

MissionTrip

    RowType = 'MissionTrip'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.ContributionDesc = '%MissionTrip: org%'
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'MissionTrip'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.AddToTag = 1
    SearchParameters.BundleTypes = ['Mission Trip Transaction']
    CreateTag(fundSet, RowType, SearchParameters)

Lines 57-61 create a tag called MissionTrip. It uses the contribution description starting with MissionTrip: org%. The % at the end matches anything that follows. Touchpoint’s built-in Mission Trip support does this so that this will work for any church.

Lines 63-69 add to the tag called MissionTrip. Note the AddToTag on Line 67. It uses a church specific BundleType called Mission Trip Transaction, which requires you to create this BundleType for entering Mission Trip gifts. This section of code is only needed if you enter Mission Trip support gifts without using TouchPoint’s support for Mission Trip Giving exclusively.

Online

    RowType = 'Online'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.Source = 1
    SearchParameters.TransactionDesc = 'Online Giving'
    CreateTag(fundSet, RowType, SearchParameters)

    RowType = 'Online'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.AddToTag = 1
    SearchParameters.Priority = NextPriority()
    SearchParameters.Source = 0
    SearchParameters.BundleTypes = ['Online']
    SearchParameters.TransactionDesc = '<>Recurring Giving'
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'Online'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.AddToTag = 1
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Manual Credit Cards','Payroll Deductions']
    CreateTag(fundSet, RowType, SearchParameters)

Lines 71-76 create a tag called Online. These come from the TouchPoint Mobile App (Source = 1) and have a Transaction Description of Online Giving. However, The Mobile tag already contains these.

Lines 78-85 add to the tag called Online. These do not come from the Touchpoint Mobile App (Source = 0). And The BundleType is Online. When a gift occurs online, Online Bundles are created automatically by Touchpoint. But those gifts with a TransactionDesc of Recurring Giving are excluded as they need to be categorized separately.

See also

model.CreateContributionTag() about using the <> prefix for the TransactionDesc search parameter.

Lines 87-93 add to the tag called Online with a church specific search. This search finds contributions in either of the two bundle types Manual Credit Cards or Payroll Deductions. For this code to produce results, you are required to create new BundleTypes and enter the appropriate contributions into those bundle types.

Recurring

    RowType = 'Recurring'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.TransactionDesc = ['Recurring Giving']
    CreateTag(fundSet, RowType, SearchParameters)

Lines 95-99 create a tag called Recurring. All recurring gifts will have a Transaction table row with Recurring Giving in the TransactionDesc column.

SundayMainWorship

    RowType = 'SundayMainWorship'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Generic Envelopes','Loose Checks and Cash','Preprinted Envelopes']
    CreateTag(fundSet, RowType, SearchParameters)

Lines 101-105 create a tag called SundayMainWorship. The three standard BundleTypes used for offering plate donations are Generic Envelopes, Loose Checks and Cash, and Preprinted Envelopes

Church Specific BundleType Tags

The following sections through Line 155 are all church specific. All of these sections work by using a custom BundleType which is used to identify the contribution RowTypes for this report. You can either create these new BundleTypes and start posting contributions to that Bundle or you can delete these sections for your church.

    # church specific
    RowType = 'SundayMainWorship'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.AddToTag = 1
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Sunday Main Worship']
    CreateTag(fundSet, RowType, SearchParameters)

Lines 108-113 add to the tag called SundayMainWorship with a church specific search. And requires a BundleType called Sunday Main Worship.

    # church specific
    RowType = 'MailedChecksAndCash'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Mailed Checks & Cash']
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'BankDrafts'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Bank Draft','Bank Drafts']
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'SecureGive'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['SecureGive']
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'Stock'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Stock']
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'Students'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Students']
    CreateTag(fundSet, RowType, SearchParameters)

    # church specific
    RowType = 'Hispanic'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.BundleTypes = ['Hispanic']
    CreateTag(fundSet, RowType, SearchParameters)

The different sections above all require creating BundleTypes of the name referred to in the SearchParameters.BundleTypes assignment. The exception is Line 126 which contains two possibilities, create a BundleType for just one of these.

NonTaxDeductible

    RowType = 'NonTaxDeductible'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.NonTaxDed = 1
    CreateTag(fundSet, RowType, SearchParameters)

Lines 157-161 create a tag called NonTaxDeductible. The SearchParameters.NonTaxDed = 1 does the trick here.

The Unknown Tag

    RowType = 'Unknown'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = NextPriority()
    SearchParameters.NonTaxDed = -1
    SearchParameters.ContributionTags = "<>Gt{}-%".format(fundSet)
    CreateTag(fundSet, RowType, SearchParameters)

Lines 163-168 create a tag called Unknown. This search should not contain any contributions and is meant to catch anything that slips through the cracks. The search does this with the line SearchParameters.ContributionTags = "<>Gt{}-%".format(fundSet). In other words, it finds contributions that do not already have a contribution tag for the appropriate fundSet.

See also

model.CreateContributionTag() for the description of the ContributionTags search parameter.

If the tag is not empty, then those contributions have not been considered in the previous sections and you need to look closely at them to figure where they should go and what search parameters will work. Or you may need to create one or more new tags to hold these in logical groupings.

The Total Tag

    RowType = 'Total'
    SearchParameters = model.DynamicData(Base)
    SearchParameters.Priority = Priority / 100 * 100 + 99 # 199, 299, 399, etc.
    SearchParameters.NonTaxDed = -1 # either tax or non-tax
    CreateTag(fundSet, RowType, SearchParameters)

Lines 170-174 create a tag called Total. This tag should contain the total of all the previous tags since it does not have any search parameters except for the FundIds for the particular column.

This ends the explanation of the CreateTags function.

Build the Three Columns for the Report¶

The next lines of code define a function called CreateFundSetColumns which makes three calls to the CreateTags function, one call for each FundSet/column. Generally speaking, the first column is intended to be for giving to any of the church’s Funds. The second column is intended to be for the single default and main giving fund, such as General Tithe, or whatever you call it at your church. Then the third column is all the funds other than the main giving fund.

This function has two paths of execution: one for a specific church, and one for a generic churches which may fit for your church. You may need to customize one of these two paths for your church based on your fund structure.

You will notice that there are three different sets of Priority numbers starting with either: 100, 200, or 300. These numbers correspond to the three FundSets AllFunds, MainFund, and Other which also correspond to the three columns of the report. The first column’s amounts for AllFunds should add to the sum of the second two columns.

def CreateFundSetColumns():
    global Priority
    nc = NameOfChurch
    if NameOfChurch == 'Mount Pisgah UMC':
        Priority = 100
        allFunds = model.CustomStatementsFundIdList('Mount Pisgah Church') # all funds for church
        Base.FundIds = allFunds
        CreateTags('AllFunds')

        Priority=200
        Base.FundIds = '180' # Main Giving Fund
        CreateTags('MainFund')

        # Fund Others, all funds except the main giving fund
        Priority=300
        Base.FundIds = allFunds.replace('180,', '') #remove fund 180
        CreateTags('Other')
    else:
        Priority = 100 # fundSet1
        # All funds included in first fundSet
        CreateTags('AllFunds')

        # Fund 1 (General Tithe), this highlights the main giving fund in a fundSet
        Priority=200 # fundSet2
        Base.FundIds = '1'
        CreateTags('MainFund')

        # Fund Others, all funds except the main giving fund in a fundSet
        Priority=300 # fundSet3
        Base.FundIds = '<>1'
        CreateTags('Other')

Lines 176-192 This code path works for the named church only after it has been modified to fit your church. The FundIds are what are necessary.

See also

If you are using custom Statements and CustomFundSets at your church:

Custom Contribution Statements
/CustomProgramming/TextScripts/CustomFundSets
model.CustomStatementsFundIdList()

Line 177 allows the global variable Priority to be assigned to within the function. Normally in Python functions all variables are local to the function and although global variables can be read and used within a function, they cannot be assigned to.

Lines 193-206 This section works for the Generic funds which may or may not work for your church but is a good starting point.

Wrapping it All Up¶

Now that all the global data is set and all the functions are defined, the real work starts here. All that remains to be done is to call the previously defined functions, and then display the validation report.

model.DeleteContributionTags('Gt%')

CreateDateRangeTags()
CreateFundSetColumns()

json = model.FormatJson(GivingTypesData)
model.WriteContentText('GivingTypesData', json) 

html = '\n'.join(ValidationReportList)
print html

Line 208 removes the existing set of ContributionTags to make way for this new set. The tags to be removed start with Gt using the % wild card to match anything to the right of Gt.

See also

model.DeleteContributionTags()

Lines 210-211 call the two main functions, one to create the date range tags and the other to create all the tags for the three fund sets.

Lines 213-214 save the GivingTypesData DynamicData object to a text file called GivingTypesData. This file contains a record of all the SearchParameters that were used to create the ContributionTags. The links in the displayed validation report use this file on the screen for each column/row/priority combination.

Lines 216-217 The last two lines display the validation report on the screen after joining the entire list of lines in ValidationReportList into a single string called html.

The last thing is to view the Validation Report which should display on the screen if you have manually run this script. Each heading is a link that save the contributions found by each set of SearchParameters to an Excel file. This should be helpful when customizing and validating your report.