Build-On Tutorial:

Lapsed Donor report

Lapsed Donor report: Lesson 1

Lapsed Donors Report

Lesson Overview

In this lesson, you will create a “Lapsed Donors” report of members who have not donated in the past year. The report will include last donation and interaction information, as well as the member's phone number. You will also add a button to create an interaction record from the list.

Here’s a sneak peek of what you will build:

Step 1 - Create Fields

Create two additional calculation fields in the Members table to track the last donation date and the last interaction date.

  1. Open the Manage Database dialog (File > Manage > Database...).

    Note: You can also press the keyboard shortcut Command-Shift-D (macOS) / Control-Shift-D (Windows).
  2. Activate the Fields tab and select the Member table from the table drop down.
  3. Create a new calculation field:
    1. Enter "Date_Last_Donation" as the field name.
    2. Select Calculation as the Type: and then click Create.
    3. In the Specify Calculation dialog, set Calculation result is to Date.
    4. Enter the formula: Max ( member_DONATION::Date_Donation )
  1. Create a second calculation field named Date_Last_Interaction with the following formula: Max ( member_INTERACTION::Date_Interaction )

    (In the Specify Calculation dialog, set Calculation result is to Date)
  1. Exit the Manage Database dialog.

Step 2 - Create the new report layout

You will create a new member list layout to display the new fields and member phone numbers. You will do that by duplicating and editing the existing Member List layout.

  1. Open the Manage Layouts dialog (File > Manage > Layouts...).

    Note: You can also press the keyboard shortcut Command-Shift-L (macOS) / Control-Shift-L (Windows).
  2. Select the Member List layout and duplicate it by clicking the Duplicate button or by pressing Command-D (macOS) / Control-D (Windows).
  3. Double-click the “Member List Copy” layout. In the Layout Setup dialog, rename it to “Member Report”.
  1. Click Open in the Manage Layouts window. Your new layout will open in a new window.
  2. Enter Layout mode using the Edit Layout button in the status toolbar.

    Note: You can also enter Layout mode using the View > Layout Mode menu item, or the keyboard shortcut Command-L (macOS) / Control-L (Windows).
  3. In the Header part, select the title (“Members”) and resize it to accommodate more text, then double click the title and change it to “Call List – Lapsed Donors”.
  4. Delete all fields and labels from the body, except the Member_Name field and the “Name” label. Also, delete all of the buttons in the Footer.

    Attention: be careful not to remove the large transparent button in the Body background.See the objects to be deleted:
  1. After you delete the objects selected above, your layout should now look like this:
  1. Activate the Fields tab by using the menu View > Objects > Fields Tab.
  2. Select the following Drag Preferences in the lower left corner:
  1. In the drop down at the top of the field list, ensure Current Table (“MEMBER”) is selected. Click the Phone field and drag it next to the Member_Name field.
  2. Select both Date_Last_Donation and Date_Last_Interaction and drag them next to the Phone field.
  3. Open the Inspector using the View > Inspectors > Inspector menu item. You can also access it using keyboard shortcut Command-I (macOS) / Control-I (Windows).
  4. Activate the Styles tab of the Inspector.
  1. Select the "Phone" label, and set it to use the label List.Left style.
  2. Change the "Date_Last_Donation" label to "Last Donation" by double clicking the label to edit.
  3. Similarly, change the "Date_Last_Interaction" label to "Last Interaction".
  4. Set both labels to use the style label List.Right.
  5. Apply the following styles to the following fields:
    1. Date_Last_Donation - style: txt Minimal.Black.Right
    2. Date_Last_Interaction - style: txt Minimal.Black.Right
    3. Phone - style: txt Minimal.Black.Left
  6. Activate the Data tab of the Inspector.
  1. Adjust the Data Formatting for the two date fields to format as “mm/dd/yy”, represented by “12/25/14”.
  1. Select all the fields in the Body, and in the Behavior section of the Data tab, un-check field entry in Browse Mode. This will prevent users from modifying data while on this report.
  2. Adjust the position, alignment, and size of fields and labels until the result looks similar to this:
  1. Use the Exit Layout button to return to Browse mode and review your progress.

    Note: You can also enter Browse mode using the View > Browse Mode menu item, or the keyboard shortcut Command-B (macOS) / Control-B (Windows).

Step 3 - Add Interaction button

In this step, you will copy the “Add Interaction” button from an existing layout to allow creating interactions right from the Lapsed Donor report.

  1. Enter Layout mode and navigate to the Desktop > Member > Member Detail layout.
  2. Go to the Interactions tab.
  3. Copy the Add Interaction button by selecting it and choosing Edit > Copy from the menu, or by using the keyboard shortcut Command-C (macOS) / Control-C (Windows).
  4. Navigate back to the Desktop > Member > Member Report layout.
  5. Paste the button by using the menu Edit > Paste or by pressing Command-V (macOS) / Control-V (Windows).
  6. Drag the button to the right of the Date_Last_Interaction field, in the Body layout part. You might need to shrink the width of the button so it will fit.
  7. Apply the btn White style to the button.
  8. On the Position tab of the Inspector, set the Autosizing options to anchor top and right, instead of the default bottom and left.
  9. The button will work with the existing script and card window layout from the your file, so there are no other changes you need to make to the button. The result should look similar to this:
  1. Enter Browse mode and save your changes when prompted.

Step 4 - Create a script to find lapsed donors

You will now create a script to find members who have not donated in the past year and to display them using the new Member Report layout.

Note: If you have not worked with scripts before, you will find it helpful to review the Creating scripts section of the FileMaker Pro Advanced Help system before starting this task.

  1. Open the Script Workspace (Scripts > Script Workspace...).
  2. Duplicate the Template Script (Scripts menu > Duplicate Script). Rename the script “Member - Lapsed Donors“ (Scripts menu > Rename Script) and move it into the Member folder.

    Note: Any time you copy the template script, be sure to amend the comments at the top to fit your current script.
  3. Modify the script so that it performs a find for members based on Date_Last_Donation. The new steps you need to add are highlighted below.

    NOTE: You will find details about the settings for the Set Field dialog in the next step.

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Freeze Window

#Call List - Lapsed members
#Donated previously but not in the past year
Go to Layout [ “Member Report” (MEMBER); Animation: None ]
Enter Find Mode [ Pause: Off ]

Set Field [MEMBER::Date_Last_Donation ; Let (
[
   date_field = Get( CurrentDate ) ;
   m = Month ( date_field ) ;
   d = Day ( date_field ) ;
   y = Year ( date_field )
];
   "<" & Date ( m ; d ; y - 1 )
)]

Set Error Capture [ On ]
Perform Find [ ]
Set Error Capture [ Off ]

Exit Script [ Result: $null ]

  1. In the Set Field script step, insert following formula into the Calculated result:

Let ( [
   date_field = Get( CurrentDate ) ;
   m = Month ( date_field ) ;
   d = Day ( date_field ) ;
   y = Year ( date_field )
];
   "<" & Date ( m ; d ; y - 1 )
)

  1. Finally, modify the main navigation script, Nav Menu, located in the General Scripts folder, to include the new report. The new steps you need to add are highlighted below.

If [ $nav = "Campaign" ]
   #Campaign List
   Go to Layout [ “Campaign List” (CAMPAIGN) ; Animation: None ]
   Show All Records
   Perform Script [ “Campaign - Go to List” ]

Else If [ $nav = "Member" ] #Member List
   Go to Layout [ “Member List” (MEMBER) ; Animation: None]
   Show All Records
   Perform Script [ “Member - Go to List” ]

Else If [ $nav = "Donation History" ] #Donation History
   Go to Layout [ “Donation List” (DONATION) ; Animation: None ]
   Show All Records
   Perform Script [ “Donation Go to List” ]

Else If [ $nav = "Call List" ]
   #Call List - Lapsed Donors
   Perform Script [ “Member - Lapsed Donors” ]

End If

Exit Script [ Result: $null ]

  1. Save your script and close the Script Workspace.

Step 5 - Create report workflow

The final step is to add navigation buttons to the dashboard and the navigation menu.

  1. Enter Layout mode and navigate to the Desktop > Dashboard layout.
  2. Select the Donation History button and duplicate it by dragging it while holding Option key (macOS) / Control (Windows). Alternatively, you can copy and paste it.
  3. Position the new button next to the Donation History button.
  4. Double-click the button to edit it.
    1. Change the label to “Lapsed Donors
    2. Select the “Phone” icon as shown.
    3. Change the Optional Script Parameter to: # ( "nav" ; "Call List" )
  1. Exit Layout mode by going to Browse mode. The dashboard should look like this:
  1. Next, update the Navigation Card Window. Using the layout drop-down in Layout mode, navigate to the Desktop > Menu | Card layout.
  2. Double-click any of the existing links to bring up the Button Bar Setup dialog. Navigate to the last segment in the button bar and then click the plus button to add a new segment.
  3. In the Button Bar Setup dialog:
    1. Set the name of the segment to “Lapsed Donors".
    2. Set the Action: for the new segment to Perform Script.
    3. Select the Nav Menu script.d.Set the Optional Script Parameter to: #( "nav" ; "Call List" )
  1. Close the Button Bar Setup dialog and exit Layout Mode to save changes. The layout should now appear as shown below, and you have completed this lesson!

Step 6 - Review your work

  1. From Dashboard or the navigation menu on any layout, you should have the option of running a Lapsed Donors report.
  2. When you run the Lapsed Donor report, you should only see donors that have donated before, but not within, the last year.
  3. The Lapsed Donor report should list key information about members, including their phone number, and allow you to easily create a new interaction. You should also be able to navigate to the Member Detail layout by clicking on the row.