GivingForecastData Python Script¶
Suggestion: Right-click the link and use the “Open link in a new window” selection, then arrange the windows so you can view them side by side.
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.
Save the script. It runs the next morning between 4:30 and 5:30 AM. Of course, you can always manually run this script whenever you want.
Explanation of the Code¶
Line 1 allows only those with the role Finance to run this script.
Line 3 creates a QueryTag called GivingForecast-Members.
A QueryTag is a specialized tag, and this one contains people
where at least one of the Primary Adults in the family is a member of the Church.
The Code gives the same results as SearchBuilder.
IncludeDeceased is required to make the family counts reconcile with other reports.
Lines 6-14 create a QueryTag called GivingForecast-NonMembers.
This code is a bit more complicated because it defines people
where either a family adult has attended at least once
in the past 365 days or someone in the family has given in the past 365 days.
The code further limits the results to those families
where no Primary Adult is a member of the church.
You can use SearchBuilder to build the Code to use in the CreateQueryTag function. Once the query is returning the correct results, you can use the Code link in SearchBuilder to view the code that is used to produce your results. Just copy that code displayed and paste over the code to define Members and NonMembers.
Line 16 loads the SQL script called GivingForecast.
The SQL script uses the two QueryTags created above to define members and non-members.
Then produces three different lines of data as seen, for example, below:
See the explanation in GivingForecast SQL Script.
Line 18 fetches the three lines of data delivered by the GivingForecast SQL.
The data variable holds these figures in a DynamicData structure where the three rows
are identified with the keys Member, NonMember, and Combined.
Then the column values FamilyCnt, RecurringCnt, and MonthlyAmt.
This way, data.Member.MonthlyAmt can access the appropriate number in the table.
Line 19 records the current date and time for reference.
Line 21 converts the DynamicData structure data into a JSON representation.
Line 22 writes the JSON data to a text file called GivingForecastData
Lines 24-26 The final step is to print the data to the screen.