Programming TouchPoint Using Python and SQL¶
Programming TouchPoint Using Python and SQL¶
This is your guide to how you can use Python and SQL to make TouchPoint sing to your own tune.
A great way to learn programming TouchPoint is to use some of our custom recipes for examples of what you can do.
Below are what we call recipes for both Python and SQL Reports. These are step-by-step instructions, complete with the code you can copy and paste into your database. This makes it easy for someone who is not proficient in these programming languages to create these specific reports.
Both SQL and Python reports have the option of being added to either the main Reports menu or to the Blue Toolbar. After running the report, there will be a link at the bottom. It is Add Report to Menu. If the report is suited for the Blue Toolbar, the recipe will contain the code necessary to place it there, should you choose to add it to the menu.
Without that code, the report will appear on the main Reports menu, when you choose the option to add to menu.
Reports that are suited for the Blue Toolbar are those that depend on a set of individuals, such as an organization, a Tag, or Search Builder results. Other reports should appear on the Reports menu, because they are more global in scope.
Writing Your Own Scripts¶
We prefer that you develop your SQL scripts on a local copy of your database before you install them in your production database. Otherwise, we will be seeing error messages every time you make a mistake and it could cause performance problems for other churches if you write an untested script that hogs resources unnecessarily. By the way, there is no danger of damaging anything since you will have read-only permissions through SQL on the production database.
Your database is housed on a Microsoft SQL Server 2016 database. You can get a free Express version of this database system from Microsoft. See the links at https://github.com/bvcms/bvcms/blob/master/ReadMe.md under Developer tools.
We can make arrangements for you to download the nightly backups of your church’s database. You just need to setup your own Rackspace cloud files account and then provide us with the access token from Rackspace. Then we add your database backup to that account every night. From here you will be able to see the database schema.
We charge a one-time setup fee of $50 to script the backups to your account. The Rackspace cloud files account will be very inexpensive, probably less than $5 a month, even less if you keep it old files cleaned out regularly.
If you don’t want to download your own backups. You can download the BVCMS Open Source code from https://github.com/bvcms/bvcms. This will allow you to create a starter database which will have the entire schema for you to see what tables and fields are available.
- BlueToolBar People
- BlueToolBar OrgSearch
- Absents Report
- Attendance Change Detail for Orgs
- Attendance Change for Orgs
- Average Attendance For Organizations
- Average Monthly Attendance For Organizations
- Bad Enrollment Transactions
- Member Type in Org
- Org Attend Counts
- Org Givers
- Org Giving Totals
- Org Percent Giving
- Org Search Member Stats
- User People Tagged For Orgs
- Visitor Attend Percent for Orgs
- Main Menu
- Age Range Giving Units
- Average Attendance Compare
- Average Monthly Attendance for an Org
- Average Annual Attandance By Org
- Bad Extra Values
- Couples With Different Contribution Options
- Deceased This Month
- Family Email
- Family With Duplicate Addresses
- First Gifts in Past 365 Days
- Givers Over Amount Report
- Giving Units (Date Range)
- Giving Units 365
- Kids To Move
- Last Attend Last Gift 365
- Meeting Attend Dates Different
- New Member Giving
- Offsite Trip Organizations
- Organization Giving
- Orgs Limit to Role
- Parent Child Orgs Report
- Picture Directory
- Prayer Requests
- Previous Sub Groups
- Recent Visitors to Division
- Recurring Gifts
- Register Email not Equal to Personal Email
- Registration Sub Groups
- Top 200 Donors with Email
- Top Giving Families
- Total Gifts >= 5000
- Bundle Report
- Bundle Report 2
- Check-In Activity (Chart)
- Faith Path Stage of Life Campaign
- Five Year Giving Report
- Member Status Chart
- Morning Worship Chart
- Move Kids
- New Guest Email Campaign
- New Guest History Report
- Financial Peace University Packet Pickup Project
- Python Charts
- Recent Inactive Today
- Three Year Giving
- Vital Stats Report
This is the main access to TouchPoint from within the Python environment.
The PythonModel is accessed through a single property called
that is automatically instantiated and available
in any Python script within TouchPoint.
These functions are used to query your TouchPoint database through a built-in object called
The documentation for QueryFunctions is divided into the following sections: