Viewing and Searching by Constituency in TRANSACTIONS, GN v3.5
This screencast demonstrates a technique to add a view of current constituencies in TRANSACTIONS. Using this view, you can see the current constituencies for a donor, and perform finds in TRANSACTIONS that involve constituencies.
Finding Donors Who Have Increased or Decreased Their AF Gift Over Last Year
This screencast demonstrates a technique to allow the GENERATIONS user to isolate a set of constituents who have either increased or decreased their annual fund gift over the previous year. This technique could be extended to include Capital, Endowment, or Other gifts (or any such combination). This technique is for GENERATIONS v3.0 or v3.5 and involves creating two new calculation fields in gnCONSTITUENTS.
Using Soft Credits to Offset a Pledge Balance
This technique allows you to pay a pledge with a soft credit. While you’ll want to use this technique carefully, it can be helpful in situations where a donor makes a pledge and then uses a foundation to carry out their commitment.
Paying a Pledge with a Soft Credit
There are times where it’s handy to be able to offset a pledge balance with a soft credit. I can think of at least one important case where this would make sense: if a donor makes a pledge, and then satisfies that commitment by channeling funds through some other organization (e.g. a family foundation). The chronology would look something like this:
- Donor makes a pledge
- Pledge is paid by the donors family foundation
- Donor is soft credited for the payment amount
The problem here is that, depending on how you set up this chain of transactions, the original pledge may still have a balance. Fortunately, there’s an easy solution. If you link the soft credit to the original pledge so that the pledge is the master transaction for the soft credit, in GN v3.5(+?) the pledge balance will be lowered by the amount of the soft credit. If you’re running GN v3.0 then you’ll need to make the minor modification outlined below.
To link the soft credit to the original pledge, navigate to the soft credit, and from the overview layout, click the Link button in the Master Transaction pane. Enter some search criteria to locate the original pledge, and from the resulting list, click the plus button to link the soft credit transaction to the appropriate pledge.
Modifying GN v3.0 So That These Soft Credit Transactions Will Affect the Pledge Balance
Note that if you do not modify GN v3.0 as specified here, linking a soft credit transaction to a pledge transaction will *not* affect the pledge balance at all.
Open GN v3.0 with the admin [Full Access] account and navigate to gnTRANSACTIONS. Go to “Manage Database” and locate the field “PledgeBalance”. Change the calculation for this field from:
Case(TransType="Pldg";
zz_AmtPledge-Sum(_Self_IDTRANS_to_IDMASTERTRANS::zz_AmtGift)
-Sum(_Self_IDTRANS_to_IDMASTERTRANS::zz_AmtPWO);
"") + 0
to
Case(TransType="Pldg";
zz_AmtPledge-Sum(_Self_IDTRANS_to_IDMASTERTRANS::zz_AmtGift)
-Sum(_Self_IDTRANS_to_IDMASTERTRANS::zz_AmtSoftCredit)
-Sum(_Self_IDTRANS_to_IDMASTERTRANS::zz_AmtPWO);
"")
You should be able to copy/paste the text from the second calculation into your calculation dialog box.
Creating Constituent Codes
GENERATIONS 3.5 Address Formats
This tutorial describes how to modify the formatting of addresses in GENERATIONS 3.5.
inRESONANCE Report Framework
This tutorial describes how to use the inRESONANCE report framework. This example describes how to duplicate an existing report in GENERATIONS including the report layout, the report script, and the report button. This tutorial does not describe how to create a sub-summary report in FileMaker.
Counting Donors in Transaction Reports
This page describes how to count the number of distinct donors in a transaction report, rather than the number of transactions. This functionality has been added to GENERATIONS v3.0.3 and later.
This is useful if constituents have given more than once, but you only want to count them once.
The trick is to figure the fractional total per transaction on an individual constituent (i.e. IDCON) basis. In other words, if I gave three gifts, I’d want something that could make each of those gifts count as 1/3 towards the total number of donors.
Here’s an example:
| Donor Name | Amount | Weight |
| Mike | $50 | 1 transaction weighted at 1/2 |
| Mike | $50 | 1 transaction weighted at 1/2 |
| Charlie | $75 | 1 transaction weighted at 1 |
| Kevin | $35 | 1 transaction weighted at 1 |
| Corn | $25 | 1 transaction weighted at 1/3 |
| Corn | $25 | 1 transaction weighted at 1/3 |
| Corn | $25 | 1 transaction weighted at 1/3 |
| Total | $285 | 7 transactions for a total of 4 |
If you total (or sum) the fractional totals, you get 1/2 + 1/2 + 1 + 1 + 1/3 + 1/3 + 1/3 = 4. The number of donors.
You need a new calculation field to gnTRANSACTIONS for the fractional total (call it zz_FractionalTotal to continue our use of zz_ fields to denote “system” fields):
1/GetSummary(SumCount_Records,IDCON)
Note: if the SumCount_Records field doesn’t exist, create it as a new summary field set to:
Count Of zz_Constant
Then, create another summary field in gnTRANSACTIONS to sum the zz_FractionalTotal field. Call it SumTotal_Donors and set it to:
Total Of zz_FractionalTotal
You should then be able to lay the SumTotal_Donors field on the layout in the place you want it to go. Note, because it uses the GetSummary function and has specified the IDCON field as the break part, you must add IDCON to your sort order. Even though the report doesn’t summarize on IDCON, you must add it to the sort for the fractional totals and counts to work properly. You should be able to add this field to any of the summary reports in Transactions, just make sure to add IDCON to the sort order. You may also need to round (via Format>Number) the SumTotal_Donors field to get rid of any rounding issues, should they arise.
This method may be a bit fragile, because of the hidden use of a summary field which isn’t on the layout.
The fields and functionality listed above were added to GENERATIONS v3.0.3 and later. If you have this or later versions of GENERATIONS, you can simply add the fields above to the layout on which you need donor totals.
Modifying the Pay a Payment Feature in Batch Add
This tutorial describes how to change the default behavior for the pay button in Batch Add so that it automatically checks the paid checkbox.
Server Side Complete Update With FMS 13 and later
This tutorial describes change how to set up FileMaker Server 13 to perform the complete update on a repeating schedule. Note that this is for GN 3.5 and later only. This technique will not work with GN3.0 or prior versions.
GENERATIONS 3.X – Creating Letterboxes
This tutorial describes how to create a letterbox for letters in GENERATIONS v3.
GENERATIONS 3.X – Exporting Data for the Mail House
This example file demonstrates how to parse your normally multi-line street field into separate lines. You might also want to check out the downloadable modules Address Exporter v1.0.0 and v2.0.0 available on the Additional Modules page.
GENERATIONS 3.X – Merging Two Saved Found Sets
This screencast demonstrates a technique to merge two saved found sets.
GENERATIONS 3.5 – Bring Back the Primary Constituency!
This screencast demonstrates how to bring back the concept of primary constituency. This is particularly useful for customers who have migrated from GN 3.0 to 3.5. GN v3.5 took away the primary constituency as part of a move towards a more rigorous constituency performance report.
GENERATIONS 3.5 – Building on the Primary Constituency
A simple constituency performance report for GN 3.2 – This screencast builds on the previous tip by demonstrating how to construct a simple constituency performance report based on primary constituency. Note that each gift will be associated with one constituency only (the primary constituency).
Calculating Pledge Balance as of a Cutoff Date
This describes how to customize your GENERATIONS so that you can calculate pledge balances as of a specific cutoff date. This can be handy for comparing your year to date progress with previous fiscal years. This customization may also help with reconciling with your accounting system if you are tracking pledges in your accounting system.
GENERATIONS 3 and 3.5 calculate pledge balances dynamically using the current state of the data. For example, if a donor made a pledge 4 months ago, and paid it in full 2 months ago, the balance on that pledge will be zero. With the standard GENERATIONS, the only way to determine the balance of that pledge as of three months ago is by inspection. You would look at the pledge transaction, inspect the payments for that pledge, and mentally calculate the balance on the pledge as of this three months ago cutoff.
The technique described here adds another pledge balance calculation to GENERATIONS that can dynamically calculate the balance on a pledge as of any arbitrary cutoff date. Using this new calculation, you can build reports that not only compare total giving this year compared to the same interval last year (possible with the standard GENERATIONS), but total pledge balance this year as compared to the total pledge balance during the same time interval last fiscal year. This new pledge balance field can also be handy for computing pledge balances as of any arbitrary date; this can be handy for reconciling your books as of an arbitrary close date.
This customization falls into the category of an advanced modification to GENERATIONS. Please test this with a backup copy of your GENERATIONS before deploying this live. I may have missed something quite critical to the success of this modification. If you find something is in error, please contact inRESONANCE support.
- Create the following fields in gnTRANSACTIONS
zz_gCutoffMonth (number; global storage)
zz_gCutoffDay (number; global storage)
zz_CutoffDate (calculation; date) =
Let( [
cutMonth = zz_gCutoffMonth;
cutDay = zz_gCutoffDay;
FY = If(GetAsNumber(TransDate)<=GetAsNumber(Date(zz_FYEndMonth;zz_FYEndDay;Year(TransDate)));
Year(TransDate);
Year(TransDate)+1);
cutYear = Case( cutMonth < _Prefs._constant::FYMonth or
cutMonth = _Prefs._constant::FYMonth and
cutDay <= _Prefs._constant::FYDay;
FY; FY-1)
];
Case(not IsEmpty(cutMonth) and not IsEmpty(cutDay) and not IsEmpty(cutYear) ; Date(cutMonth;cutDay;cutYear))
)
- In gnTRANSACTIONS, on the relationship graph, add another table occurrence for the TRANSACTIONS table and call it “_SELF_IDTRANS_to_IDMASTERTRANS_cutoff”. Connect this table occurrence to the TRANSACTIONS table occurrence as shown in the diagram below:
- Create another field in gnTRANSACTIONS:
PledgeBalance_cutoff (calculation; number) =
Case(TransType = "PLDG" and IsEmpty(zz_CutoffDate);"?";
TransType="Pldg";
zz_AmtPledge-Sum(_Self_IDTRANS_to_IDMASTERTRANS_cutoff::zz_AmtGift)
-Sum(_Self_IDTRANS_to_IDMASTERTRANS_cutoff::zz_AmtSoftCredit)
-Sum(_Self_IDTRANS_to_IDMASTERTRANS_cutoff::zz_AmtPWO);
"")
- Create a new list view layout in gnTRANSACTIONS by duplicating an existing list view layout and make some minor modifications as shown in the image below:
The two circled items highlight your new global fields (in the header) and your new pledge balance calculation (in the body). I have also added a script here attached to the blue “today” link that sets the new global cutoff values to the month and day associated with today’s date, but that’s not an essential feature.
