GivingForecastData Python Script

View the Code

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.

model.CallScript("GivingForecastData")

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.

See model.CallScript()

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.

See CreateQueryTag().

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.

Note

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. See model.SqlContent(). 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:

RowType

FamilyCnt

RecurringCnt

MonthlyAmt

Member

2746

110

76831.98

NonMember

2896

20

8372.18

Combined

5642

130

85204.15

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.

See q.SqlFirstColumnRowKey().

Line 19 records the current date and time for reference.

Line 21 converts the DynamicData structure data into a JSON representation. See FormatJson().

Line 22 writes the JSON data to a text file called GivingForecastData See WriteContentText().

Lines 24-26 The final step is to print the data to the screen.