Build-On Tutorial:

Statistics

Statistics: Lesson 1

Statistics

Lesson Overview

In this Build-On, you will create a layout that will show you general statistics about your app’s data. This new layout will give you a visual way to see high-level information about your members and donations. You will create chart objects, along with scripts that refresh the chart data. You will also add global fields for collecting the data represented in the charts.

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

Step 1 - Create the layout

Create the new layout by duplicating the existing Blank | Desktop Detail layout.

  1. 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).
  2. Use the Layout pulldown menu in the status toolbar to switch to the Blank > Blank | Desktop Detail layout.
  3. Duplicate the layout using the Layouts > Duplicate Layout menu item.

    Note: You can also use the Manage Layouts dialog (File > Manage > Layouts...) to duplicate a layout. This dialog is also useful for organizing and reordering your layouts
  4. Using the Layout Setup dialog (Layouts > Layout Setup...):
    1. Rename the layout to “Statistics”.
    2. Make sure that the Show records from: dropdown has the _GLOBAL table occurrence selected. This setting determines the context of the layout.
    3. Set the Menu Set to Minimal. Since the layout will be informational, you won’t want your users doing things like creating or deleting records from this layout. This existing custom menu set will limit what a user can do on this layout.
    4. Confirm by clicking OK and exit Layout mode to save your work.
  1. Open the Manage Layouts dialog (File > Manage > Layouts …).
    1. Create a folder (click on New button> Folder) called “Statistics” and click OK.
    2. Move the Statistics layout to the Statistics folder by dragging it into it.
  1. Select the Statistics layout and click Open to view the layout.
  2. Because this layout has the Minimal custom menu set, you will need to change custom menus before editing. Select Tools > Custom Menus > [Standard FileMaker Menus], then switch to Layout mode and double-click the Title in the header. Change the text to “Membership Statistics”.
  1. Open the Manage Database dialog (File > Manage > Database...).
  2. Activate the Fields tab and select the z_Globals table from the table dropdown.
  3. You will create fields that use global storage in order to temporarily store chart data. Create the following fields, all of type Number, and on the Storage tab in the field Options, turn on Use global storage (one value for all records):
    1. gStats_Members_Active
    2. gStats_Members_Inactive
    3. gStats_Members_Total
    4. gStats_Campaigns_2YearsAgo
    5. gStats_Campaigns_LastYear
    6. gStats_Campaigns_ThisYear
  1. Click OK to close the Manage Database dialog.

Step 2 - Add navigation to existing layouts

You will adapt the Dashboard layout as well as the side navigation layout to allow users to navigate to your new Statistics layout.

  1. Use the Layout pulldown menu in the status toolbar to switch to the Desktop > Dashboard layout and make sure that you are in Layout mode.
  2. Duplicate the Members button. You can Copy and Paste this button or use the keyboard shortcut Command-D (macOS) / Control-D (Windows).
  3. Double-click the new button to open the Button Setup dialog.
    1. Set the label to “Statistics”
    2. Choose the charting Icon, used in the image below.
    3. Click on the Optional Script Parameter and change it to # ( "nav" ; "Statistics" )
  1. Position the buttons so they are similar to the image below.
  1. Use the Layout pulldown menu in the status toolbar to switch to the Desktop > Menu | Card layout.
  2. Double-click on the Donation History segment of the menu. The Button Bar Setup dialog will open.
    1. Add a segment to the Button Bar by clicking on the + icon.
    2. Set its label to “Statistics”.
    3. Set the Action: to Perform Script.
    4. Select the Nav Menu script from the list. You can find it in the Generic Scripts folder.
    5. Add an Optional Script Parameter as follows: # ( "nav" ; "Statistics" )
  1. The layout should now appear as shown below. 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 - Create script to collect Donation data

In this step you will create a script to collect aggregate data about donations. Using a chart, you will show the donations collected for the current year and the past two years. You will use the ExecuteSQL function in order to collect this data without having to switch layouts or find records, since the ExecuteSQL function is not dependent on context. Once you have collected the three annual donation amounts, the script will populate three of the global fields you created previously with that information.

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

  1. Open the Script Workspace (Scripts > Script Workspace...).
  2. Create a new folder in the root of the Script Workspace. Name it “Statistics”. This folder will contain all the scripts related to the statistics context.
  3. Create a new script by duplicating the Template Script in the "Example Scripts" folder (Scripts menu > Duplicate Script). Rename the script “Statistics - Collect Donation Data” (Scripts > Rename Script). Move it into the Statistics folder by dragging it into the folder.
  4. Modify the script by adding the highlighted script steps:

    NOTE: See Step 5 for details on the Set Variable ($sql) script step.

Set Variable [$year ; Value: Year ( Get ( CurrentDate ) ) ]

# Calculate amount of donations over 3 years
Set Variable [$sql; Value: Let (   ~sql_before =      "SELECT Sum ( ~Amount ) " &   "FROM ~Donation "... ] 
          "WHERE Year ( ~Date ) = ?" ;

Set Variable [$amount_ThisYear ; Value: ExecuteSQL ( $sql ; "" ; "" ; $year ) ]
Set Variable [$amount_LastYear ; Value: ExecuteSQL ( $sql ; "" ; "" ; $year - 1 ) ]
Set Variable [$amount_2YearsAgo ; Value: ExecuteSQL ( $sql ; "" ; "" ; $year - 2 ) ]

# Set global fields to amounts
Set Field [ _GLOBAL::gStats_Campaigns_ThisYear ; Round ( $amount_ThisYear ; 0 ) ]
Set Field [ _GLOBAL::gStats_Campaigns_LastYear ; Round ( $amount_LastYear ; 0 ) ]
Set Field [ _GLOBAL::gStats_Campaigns_2YearsAgo ; Round ( $amount_2YearsAgo ; 0 ) ]

Commit Records/Requests [With dialog: Off]

Exit Script [Text Result: $null]

  1. For the Set Variable ($sql) script step set the Value to:

   Let ( [ 
      ~sql_before = 
          "SELECT Sum ( ~Amount ) " & 
          "FROM ~Donation " & 
          "WHERE Year ( ~Date ) = ?" ;

      ~sql_after = Substitute ( ~sql_before ;
             [ "~Amount" ; _FieldRef_getField ( DONATION::Amount ; 1 ) ] ;
             [ "~Donation" ; _FieldRef_getTO ( DONATION::ID ; 1 ) ] ; 
             [ "~Date" ; _FieldRef_getField ( DONATION::Date_Donation ; 1 ) ] 
             ) 
] ;
      ~sql_after 

Step 4 - Create script to collect Member data

In this step you will create a script to collect data about members. Using a chart, you will show the number of active and inactive members. Similar to the previous step, you will use an ExecuteSQL function to collect this data.

  1. Create another new script by duplicating the Template Script. Rename the script “Statistics - Collect Member Data”. Move it into the Statistics folder.
  2. Modify the script so it contains the following script steps:

    NOTE: See step 3 for details on the Set Variable ($sql) step.

#Calculate number of members by status
Set Variable [$sql ; Value: Let ([ ~sql_before= "SELECT Count ( * ) " & “FROM…]

Set Variable [$active ; Value: ExecuteSQL ( $sql ; "" ; "" ; "Active" ) ]
Set Variable [$inactive ; Value: ExecuteSQL ( $sql ; "" ; "" ; "Inactive" ) ]

#Set global fields to number of members
Set Field [_GLOBAL::gStats_Members_Active ; $active ]
Set Field [_GLOBAL::gStats_Members_Inactive ; $inactive ]
Set Field [_GLOBAL::gStats_Members_Total ; $active + $inactive ]

Commit Records/Requests [With dialog: Off]

Exit Script [Text Result: $null]

  1. For the Set Variable ($sql) step set the Value to:

Let ( [
   ~sql_before = "SELECT Count ( * ) " &
          "FROM ~Member " &
          "WHERE ~Status = ?" ;

   ~sql_after = Substitute ( ~sql_before ;
          [ "~Member" ; _FieldRef_getTO ( MEMBER::ID ; 1 ) ] ;
          [ "~Status" ; _FieldRef_getField ( MEMBER::Status ; 1 ) ] )
] ;
          ~sql_after
)

  1. Save the scripts and close the Script Workspace.

Step 5 - Add Member chart to the layout

In this step, you will add the chart for members. The chart will use data that you collected with the script in the previous step.

  1. Enter Layout mode.
  2. Use the Layout pulldown menu in the status toolbar to switch to the Desktop > Statistics > Statistics layout.
  3. Select the Chart tool from the status toolbar.
  1. Draw a chart on the layout. In the Chart pane:
    1. Set the chart Type to Pie.
    2. Set the Category Labels to the following calculation:

      List ("Active" ; "Inactive" )


    3. Set the Slice Data to the following calculation:

      List ( _GLOBAL::gStats_Members_Active ;_GLOBAL::gStats_Members_Inactive )

  1. In the Styles pane of the Chart Setup dialog:
    1. Set the Chart Style to Solid - Flat.
    2. Set the Color Scheme to Blue.
    3. Set the Chart Background to Transparent.
    4. Set the Chart Text to Custom.
    5. Set the Chart Title font size and Legend Text font size to “1”. Setting this to the smallest possible value creates as much space as possible for the graph.
    6. Set the Data Points to “11”.
  1. In the Data Source pane, set the Chart Data to Current Record (delimited data). Click Done when finished to confirm your chart settings.
  1. Back on the layout, select the new Chart object. In the Position tab of the Inspector, adjust the dimensions of the Chart object to a Width of 320 pt and a Height of 280 pt.

    Note: If the units in the Position tab are currently displayed as in (inches) or cm (centimeters), click on a unit label repeatedly to toggle through the units until pt (points) is displayed.
  2. In Styles tab of the Inspector, set the Chart object’s style to chart Minimal.

Step 6 - Add Donations chart to the layout

Next you will add the chart for donations. The chart will use the data that you collected with the script in a previous step.

  1. In Layout mode, duplicate the pie chart you created in the previous step (select the chart on the layout and select Edit menu > Duplicate).
  2. Adjust the dimensions of the new Chart object to a Width of 336 pt and a Height of 200 pt.
  3. Double-click on your new chart. In the Chart Setup dialog, do the following:
    1. Change the chart type to Column.
    2. Specify a calculation for the X-Axis Data. For a better aesthetic, you will show labels for the three years outside of the Chart object (these will be set via script later). To do this, set the X-Axis Data labels to be blank. You can insert a return-delimited list of three blank values with this calculation:
      “¶¶¶”
    3. Specify a calculation for the Y-Axis Data. The calculation is as follows:

List ( If ( IsEmpty ( _GLOBAL::gStats_Campaigns_2YearsAgo ) ; 0 ;
_GLOBAL::gStats_Campaigns_2YearsAgo ) ;
If ( IsEmpty ( _GLOBAL::gStats_Campaigns_LastYear ) ; 0 ;
_GLOBAL::gStats_Campaigns_LastYear ) ;
If ( IsEmpty ( _GLOBAL::gStats_Campaigns_ThisYear ) ; 0 ;
_GLOBAL::gStats_Campaigns_ThisYear ) )

  1. Select the Styles pane in the Chart Setup dialog.
    1. Set the Font Size to “1” for all options in the Chart Text Section: Chart Title, Legend Text, Data Points, Y-Axis Title, Y-Axis Labels, X-Axis Title and X-Axis Labels.
    2. Set the Color to White for the Y-Axis Labels and the X-Axis Title and Labels.
  1. Confirm the chart settings by clicking Done.
  2. Back on the layout, select the new Chart object. In the Styles tab of the Inspector, set the chart object’s style to chart Minimal.
  3. Your layout should look like the image below.

Step 7 - Add fields and objects to the layout

In this step, you will add fields and objects to the Statistics layout to add more information about membership and donations to the layout.

  1. Select the Rectangle tool from the status toolbar and click and drag a rectangle over the Pie Chart to frame it. The size should be with a width of 416 pts and height of 408 pts.
  1. Duplicate the rectangle and place it over the Column Chart.
  2. Select both rectangles and move them to the back of the layout (Arrange > Send to Back). Set the style for both shape to shape White.Border.
  3. Select the Shape tool in the status toolbar. You will use this to draw a circle on top of the Pie Chart that makes it look like a ring instead of a pie.
    1. Click and hold to change the shape to an oval.
    2. Click and drag an oval over the center of your chart Pie Chart with a Width of 120 pt and a Height of 120 pt.
  1. Insert a merge field using the Insert > Merge Field… menu item. Choose the gStats_Members_Total field and place it in the middle of the oval you just created. Change the style to headline Center.Bold.
  2. Activate the Objects pane and select the Fields tab. (If the Objects tab is not open, click the objects pane icon in the status toolbar, then click Objects.) At the bottom of the fields pane under the Drag Preferences section, set Field Placement to Horizontal and the Labels to No labels…

    Drag the following fields, without labels, on to the Body of the layout so that they appear below the Pie Chart.
    1. gStats_Members_Active
    2. gStats_Members_Inactive
  1. Drag the following fields on the Body of the layout so that they appear right below the Column Chart:
    1. gStats_Campaigns_2YearsAgo
    2. gStats_Campaigns_LastYear
    3. gStats_Campaigns_ThisYear
  2. Set the style for all of the fields to txt Minimal.Black.Center.Bold. Set the Width for all of them to 111pt.
  3. Activate the Data tab of the Inspector and turn off Field Entry for both Browse mode and Find mode for all of the fields. Select the amount fields (gStats_Campaigns_2YearsAgo, gStats_Campaigns_LastYear and gStats_Campaigns_This Year) and in the same tab, go to Data Formatting and set the Format to Currency and check System Settings under Separator.
  1. Use the Text tool to add the text “Active / Inactive Members” above the frame of the pie chart. Change the style to label caps.Left 16pt.
  2. Add the text “Members” above the pie chart. Change the style to headline Center.
  1. Duplicate the Active / Inactive Members text and move it above the Column Chart. Change the text to “Donations per Year”.
  2. Duplicate the Members text and move it above the Column Chart. Change the text to “Donations”.
  1. Enter Browse mode to save and review your work. Your charts will not display any data at this point because you have not yet run the scripts to generate them.

Step 8 - Create script to view Member data

Now that users can see the data both with charts and in fields, you will create scripts that navigate from the statistics to the actual data they represent.

  1. Create a new script by duplicating the Template Script. Rename the script “Statistics - Go To Members ( status )”. Move it into the Statistics folder.If you are still on the Statistics layout it uses a Minimal Menu set. In order to access the Scripts Workspace you will need to select Tools > Custom Menus > [Standard FileMaker Menus] and then select Scripts > Script Workspace.
  2. Modify the script so it contains following steps:

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

# parse the parameters
Set Variable [ $parameter_parsing_ok ; Value: #Assign ( Get(ScriptParameter ) ) ]

If [ $parameter_parsing_ok = False ]
   #  parameters could not be parsed, exit this script and report back
   Exit Script [ Text Result: "error" ]
End If

Enter Find Mode [ Pause: Off ]
Go to Layout [ “Member List” (MEMBER) ; Animation: None ]
Set Field [ MEMBER::Status ; $status ]

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

Exit Script [ Text Result: $null ]

Step 9 - Create script to view Donation data

Next, create a similar script that will navigate to donation records.

  1. Duplicate the script you created in the previous step. Rename the script “Statistics - Go To Donations ( year )”. Move it into the Statistics folder.
  2. Modify the script so it contains following script steps.

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

# Parse the parameters
Set Variable [ $parameter_parsing_ok ; Value: #Assign ( Get(ScriptParameter ) ) ]

Go to Layout [“Donation List” (DONATION) ; Animation: None]

Enter Find Mode [Pause: Off]
Set Field [Donation::Date_Donation ; $year ]

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

Exit Script [ Text Result: $null ]

Step 10 - Update the main navigation script

  1. In the General Scripts, open the Nav Menu script.
  2. Add the highlighted code in the script.

Set Error Capture [ Off ]
Freeze Window

# parse the parameters

Set Variable [ $parameter_parsing_ok ; Value: #Assign ( Get(ScriptParameter ) ) ]
If [ $parameter_parsing_ok = False ]
   # parameters could not be parsed, exit this script and report back
   Exit Script [ Text Result: "error" ]
End If

If [ Get(WindowStyle) = 3 // Card Window ]
   # Close Card Window
   Close Window [ Current Window ]
End If

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

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

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

Else If [ $nav = "Statistics" ]
   Perform Script [ Specified: From list ; “Statistics - Collect Donation Data” ; Parameter: ]
   Perform Script [ Specified: From list ; “Statistics - Collect Member Data” ; Parameter: ]
   Go to Layout [“Statistics” (_GLOBAL) ; Animation: None]

End If

Exit Script [ Text Result: $null ]

  1. Save your scripts and close the Script Workspace.

Step 11 - Set buttons to go to related data

In this step you will add button bars to the Statistics layout that will help label your data, as well as give users a way to navigate to the related data. You will use the scripts you wrote in previous steps.

  1. Enter Layout mode and navigate to the Statistics layout.

    NOTE: The Statistics layout uses a Minimal Menu set. In order to access Layout mode you will need to select Tools > Custom Menus > [Standard FileMaker Menus] and then select View > Layout Mode.
  2. Select the Button Bar tool from the status toolbar
  1. Create a button bar below the pie chart. The Button Bar Setup dialog will open.
    1. Remove one segment using the - button so that you only have two segments.
    2. Select the first segment and set the title to “Active”.
    3. Set the Action: to Perform Script.
    4. The Specify Script window will open. Select Statistics - Go to Members (status).
    5. Set the script parameter to: #( "status" ; "active" )
    6. Using the arrow buttons, click the right arrow to Move to the next segment. Set the title to “Inactive”.
    7. Set the Action: to Perform Script and select the Statistics - Go to Members (status).
    8. Set the script parameter to #( "status" ; "inactive" )
    9. Close the Button Bar Setup dialog.
  1. Your layout should look similar to this:
  1. Create another Button Bar below the Column Chart.
    1. Select the first segment. Set the title to the formula: Year ( Get ( CurrentDate ) ) - 2.
    2. Set the Action: to Perform Script and select Statistics - Go To Donations ( year )
    3. Set the script parameter to #( "year" ; Year ( Get ( CurrentDate ) ) - 2 )
    4. Select the second segment. Set the title to the formula: Year ( Get ( CurrentDate ) ) - 1
    5. Set the Action: to Perform Script and select the Statistics - Go To Donations ( year )
    6. Set the script parameter to #( "year" ; Year ( Get ( CurrentDate ) ) - 1 )
    7. Select the third segment. Set the title to the formula: Year ( Get ( CurrentDate ) ).
    8. Set the Action: to Perform Script and select the Statistics - Go To Donations ( year )
    9. Set the script parameter to #( "year" ; Year ( Get ( CurrentDate ) ) ).
  1. Close the Button Bar Setup dialog and select both button bars and in the Styles tab of the Inspector set the style to btnbar Nav.Center
  2. Adjust the widths and positions of the objects on the layout so yours looks similar to the image below and enter Browse mode and save your changes.

Step 12 - Review your work

  1. From the Dashboard or the navigation menu on any layout, select the Statistics button to navigate to your new layout.
  2. You should see a visual representation of the number of active and inactive members, as well as collected donations from 2 years ago, last year, and this year.
  3. You should be able to click on the label for any data point to be able to drill-down into the records that comprise it.