Build-On Tutorial:

Agenda

Agenda: Lesson 1

Event Schedule Report

Lesson Overview

The objective for this lesson is to create a print-ready report that displays the list of sessions for an event. To accomplish this, you will build a new layout and script to generate a PDF, and then add a button to the Event Detail screen so users may easily run the report.

Here is a sneak peek of what you will build:

Step 1 - Create report layout

The first step is to create a report layout for displaying sessions. Create the new layout by duplicating the existing Blank | Report layout.

  1. Open the Manage Layouts dialog (File > Manage > Layouts...).
  2. Select the Blank >Blank | Report layout from the list and Duplicate it.
  3. With the new layout selected, click the Edit button (or double-click the layout name) to open the Layout Setup dialog, and make the following changes:
    1. Set the Layout Name to “Report | Event Schedule”.
    2. Set the Show records from: dropdown to the SESSION table occurrence. This setting determines the context of the layout.
  1. Back in the Manage Layouts dialog, move the Report |Event Schedule layout into the Print folder.

Step 2 - Create and resize layout parts

Next, you will specify the new parts that will appear on the layout using the Part Setup dialog and resizethem using the Inspector tool.

  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 Print >Report | Event Schedule layout.
  3. You will need to add two additional sub-summary parts to this layout in order to properly display your data. To start, open the Part Setup dialog (Layouts > Part Setup...).
  1. Add a leading sub-summary part by clicking Create... In the subsequent Part Definition dialog:
    1. Select Sub-summary when sorted by.
    2. Leave the table occurrence pop-up menu set to Current Table (“SESSION”) and select Date from the field list.
    3. Click OK, and then choose Print Above so that the sub-summary will appear above the records that it summarizes.
  1. Add a trailing sub-summary by repeating the last step, but this time select the Print Below option.
  2. You will now see the new sub-summary parts listed in the Part Setup dialog. Close it now to return to the layout.
  1. Next, use the Inspector to set the height of each layout part. Open the Inspector using the View > Inspector menu item (you can also access it using keyboard shortcut Command-I(macOS) / Control-I (Windows)).

    Then activate the Position tab, which is used for arranging and resizing objects.
  1. Use the Position tab of the Inspector to set the Height of each layout part as shown below. In practice, it often takes trial and error to set part heights to create an attractive report.

    To select a layout part for resizing, click on the part handle at left (as shown below). If the part handles are too small to easily select, you can toggle their orientation by clicking on any part handle while holding down the Command (macOS) / Control (Windows) key.

    Note: If the units in the Inspector 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.
    1. Title Header: 56pt
    2. Header: 36pt
    3. Leading Sub-summary: 42pt
    4. Body: 110pt
    5. Trailing Sub-summary: 10pt
    6. Footer: 36pt
  1. Enter Browse mode (View > Browse Mode, or Command-B (macOS) / Control-B(Windows)) to save and review your work.
  2. Note: the blank report layout has been configured with fixed page margins of 36 pt, or 0.5”, on each side. This means that objects placed at the edge of the layout will lie 0.5" from the edge of the page. You may later decide to change the page margin settings for your report layout by entering Layout mode, opening the Layout Setup dialog (Layouts > Layout Setup...), and setting options on the Printing tab.

Step 3 - Add fields and objects to the layout

In this step you will add fields, text, and other objects to the Report | Event Schedule layout and specify styles for them using the Inspector tool.

  1. Return to Layout mode on the Report | Event Schedule layout.
  2. Change the text object at the top of the layout to display a merge field that shows the event name on the first line and “Schedule” on the second line.

    To start, double-click on the “Report Title” text object to begin editing it, and remove the existing text.
  3. With the cursor still in the text object, select Insert > Merge Field... 
    1. Change the value in the table occurrence pop-up menu to session_EVENT.
    2. Select the Event_Name field. The merge field text <<session_EVENT::Event_Name>> will appear.
    3. With the cursor still in the text object, add a new line followed by the text “Schedule”.
  1. Use the Rectangle tool in the status toolbar to create a long light-gray rectangle in the leading sub-summary part.
    1. Use the Inspector to set its Width to 540 pt and Height to 32 pt.
    2. Use the Appearance tab of the Inspector to manually set a light-gray fill color.
    3. Position the rectangle as shown below.
  1. Add the session date to the leading sub-summary part.
    1. First, click your pointer anywhere in the Sub-summary by Date (Leading) part. Note that the part an object is created in determines its default style.
    2. Choose Insert > Merge Field...
    3. In the table drop-down, select Current Table (“SESSION”).
    4. In the field list, select Date.
    5. After the <<Date>> merge field has been inserted, use the Inspector to set its Width to 300 and its Height to 20. Then set its position as shown below.
  1. Activate the Data tab of the Inspector.
  1. In the Data Formatting section of the Data tab, with the <<Date>> merge field still selected:
    1. Select the Date format button (calendar icon)
    2. In the Format menu, choose Thursday, December 25, 2014.
  1. Next you will add fields to the Body part. To begin, locate the Fields tab. (If the Fields tab is not open, click the Objects paneicon in the status toolbar, then click Fields.)

    At the bottom of the Fields tab, under Drag Preferences, set Field Placement to the horizontal setting, and set Labels to the no labels setting.
  1. In the Fields tab, drag and drop the Time_Start, Session_Name, and Location fields into the Body part (you can select multiple fields to drag using command-click or CTRL-click).
  1. Select the fields you just added, and use the Inspector to modify them as follows:
    1. Set the Height to 102 pt.
    2. In the Appearance tab, set the font size to 11 pt.
    3. Resize and arrange the fields so they look similar to the image below.
  1. Use the Line tool in the status toolbar to draw a thin gray line below the fields.
    1. Set its Width to 468 pt.
    2. Set its Top position to 242 pt.
  1. Select all of the objects in the Body part (the three fields, and the thin gray line), and use the Position tab of the Inspector to format them to Remove blank space by sliding up and Resize the enclosing part.

    Note: This makes it so fields with lots of content can be as tall as needed to display everything, but fields with less content will shrink dynamically.
  1. Select the Time_Start field. In the Data Formatting section of the Data tab:
    1. Select the Time format button (clock icon).
    2. In the Format menu, choose hhmm.
    3. In the Separator area, choose 12 hour.
    4. In the Leading character area, choose Zero for both hours and minutes/seconds.
  1. Note the merge variable <<$$PAGE_COUNT>> in the Footer part. You will set this variable in the script you will create in the next step.

Step 4 - Create a script to run the report

Your next task is to write a script that will find all the sessions for an event, sort them by date and time, and generate a PDF from the layout you created in steps 1-3. You will develop the core elements of the script in this step. You will flesh it out with additional options and error trapping in a future step.

You will create a button that triggers this script in step 5.

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 by choosing the Scripts > Script Workspace... menu item or using the keyboard shortcut Shift-Command-S (macOS) / Shift-Control-S (Windows).
  2. Duplicate the Template Script.
    1. Name the new script “Report: Event Schedule”.
    2. Move the script to the REPORTS folder.
    Note: Any time you create a new script or copy an existing one, be sure to amend the comments at the top to fit your current script.
  1. Add the highlighted script steps below to generate the report:

    NOTE: See steps 4-9 for details about the Go To Related Record, Show Custom Dialog, Sort Records, and Print Setup steps, respectively.

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Enter Browse Mode [ Pause: Off ]
Freeze Window

Commit Records/Requests [ With dialog: Off ]

# verify sessions exist for this event
If [ IsEmpty ( event_SESSION::ID ) ]
   Show Custom Dialog [ “Alert" ; "There are currently no sessions for this event.” ]
   Exit Script [ Result: $null ]
End If

# go to related session records, in new offscreen report window
Go to Related Record [ Show only related records ; From table: “event_SESSION”; Using layout: “Report | Event Schedule” (SESSION) ; New window ]
Set Variable [ $error ; Value: Get ( LastError ) ]
If [ $error ]
  Close Window [ Current Window ]
  Show Custom Dialog [ “Error" ; "FileMaker error " & $error & ". Unable to generate this report.” ]
  Exit Script [ Text Result: $null ]
End If

# sort by session date (for subsummary), then start time, name
Sort Records [ Restore ; With dialog: Off ]

# prepare report display
Print Setup [ Restore ; With dialog: Off ]
Perform Script [ Specified: From list ; “Report: Set Page Count” ; Parameter: ]

# create report and open it
Save Records as PDF [ With dialog: On ; Create directories: Off]

# cleanup and close report window
Perform Script [ Specified: From list ; “Report: Clear Page Count” ; Parameter: ]
Close Window [ Current Window ]

Exit Script [ Text Result: $null ]

  1. For the first Show Custom Dialog step (see line 21 in the image for Step 3 above):
    1. Set the Title to “Alert”
    2. Set the Message to “There are currently no sessions for this event.”
    3. Set the Default Button text to “OK”.
    4. Delete the text from Button 2, so users will only have one button option.
  1. For the Go to Related Record step:
    1. Set Get related record from: to the event_SESSION table.
    2. For Show record using layout, select the Report | Event Schedule layout.
    3. Select Show only related records and select Match current record only.
    4. For Result Options: select Show in new window. In the subsequent dialog, update the settings to match the screenshot below:
  1. For the second Show Custom Dialog step (see line 30 in the image for Step 3 above):
    1. Set the Title to “Error”
    2. Set the Message to “FileMaker error " & $error & ". Unable to generate this report."
    3. Set the DefaultButton text to “OK”.
    4. Delete the text from Button 2, so users will only have one button option.
  1. For the Sort Records step, click the gear icon next to the script step, select Specify sort order, and add the following fields to the Sort Order:
    1. SESSION::Date
    2. SESSION::Time_Start  
    3. SESSION::Session_Name
    Note: The Sort Order in the Sort Records dialog might look slightly different because the active layout determines the default context for the Sort step. So you may or may not see “SESSION::” before each field included in the sort.
  2. Set all of the fields above to sort Ascending.
  1. For the Print Setup step, select Specify page setup. In the Page Setup dialog, you can leave the default settings or change as needed.
  1. Save the script and exit the Script Workspace.

Step 5 - Button

The next step is to create a button on the Event Detail layout to run your script.

  1. Enter Layout mode and navigate to the Sessions tab of the Event Detail layout. 
  2. Duplicate the Add Session button already on that layout by selecting the button, then on the menu bar go to Edit > Duplicate.
  3. Double-click on the new button to open the Button Setup dialog, and make the following changes:
    1. Set the name of the button to "Create Event Schedule”
    2. Set it to run the Report: Event Schedule script
    3. Clear out the optional script parameter
  1. Position the new button next to the Add Session button and increase the width of the button to better fit the longer button text.
  1. Enter Browse mode to save and review your work.

Step 6 - Update event schedule script

In order to better improve the user experience, add a few more steps to the script you created in Step 4.

You will update the Report: Event Schedule script by adding steps that prompt the user to enter a name for the PDF file and suggest a default name that includes the current timestamp. The PDF is saved to the user's temporary folder and automatically opened. From there, the user can save it to a known location. You will also add some error trapping to ensure the script is called from the proper context.

  1. Open the Script workspace and open the Report: Event Schedule script.
  2. Add the script steps below to prompt the user to enter a file name for the PDF.

    NOTE: See Step 3 and Step 4 for details about the Set Variable and Show Custom Dialog steps.

# prompt user for filename
Set Variable [ $file_name ; Value: /* default filename */ Let ( [ ~event = EVENT::Event_Name ; ~timestamp = _TimestampForFilename ] ; ~event & " Schedule " & ~timestamp & ".pdf" ) ]
Loop
   # repeat dialog until user has entered a filename or canceled
   Show Custom Dialog [ "Enter File Name” ; "Please enter a name for the PDF.” ; $file_name ]
   Exit Loop If [ not IsEmpty ( $file_name ) or Get ( LastMessageChoice ) = 2 ]
End Loop
If [ Get ( LastMessageChoice ) = 2 ]
   # user canceled
   Exit Script [ Text Result: $null ]
End If

  1. For the Set Variable step, set the Name: to $file_name, and add the following calculation as the Value: by clicking the Specify button.

    Note that _TimestampForFilename is a custom function with the following calculation that has already been added to your file that returns a formatted version of the current timestamp that is safe to use in a file name. You can view this custom function via File > Manage > Custom Functions...

/* default filename */

Let ( [          
      ~event = EVENT::Event_Name ;          
      ~timestamp = _TimestampForFilename
] ;          
      ~event & " Sessions " & ~timestamp & ".pdf"
)

  1. In the Show Custom Dialog step:
    1. Set the Title to “Enter File Name”.
    2. Set the Message to, “Please enter a name for the PDF.”
    3. Set the Default Button to “Continue”.
    4. Set Button 2 to “Cancel”.
    5. On the Input Fields tab, select Show input for field #1. In the subsequent dialog, select Variable at the bottom and add the $file_name variable. Back on the Input Fields tab, add “File Name” as the label.
  1. Next, update the section of the script that saves the report as a PDF. Add/edit the following highlighted script steps:

    NOTE: Steps 6 and 7 provide further details about the Set Variable and Save Records as PDF steps.

# prepare report display
Print Setup [ Restore ; With dialog: Off ]
Perform Script [ Specified: From list ; “Report: Set Page Count” ; Parameter: ]

# create report in user's temporary folder and open it

Set Variable [ $file_path; Value: “file:” & Get ( TemporaryPath ) & $file_name & If ( Right ( $file_name ; 4 ) ≠ ".pdf" ; ".pdf" ; "" ) ]

Save Records as PDF [ With dialog: Off ; “$file_path” ; Automatically open ; Records being browsed ; Create directories: Off ]

# cleanup and close report window
Perform Script [ Specified: From list ; “Report: Clear Page Count” ; Parameter: ]
Close Window [ Current Window ]

  1. For the Set Variable step, set the name to $file_path and the value to:

“file:” & Get ( TemporaryPath ) &

$file_name & If ( Right ( $file_name ; 4 ) ≠ ".pdf" ; ".pdf" ; "" )

  1. Update the Save Records as PDF step:
    1. Select the gear icon and select Specify output file.
    2. Add $file_path to the output file path list.
    3. Be sure Automatically open file is checked.
  1. Finally, add the highlighted script steps below to provide some error trapping at the beginning of the script to ensure it is called from the proper context. Each Show Custom Dialog step should be configured to display only the Default Button with label “OK”.

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Enter Browse Mode [ Pause: Off ]
Freeze Window

Commit Records/Requests [ With dialog: Off ]

# context requirements
If [ Get ( LayoutTableName ) ≠ "EVENT" ]
   # required table context
   Show Custom Dialog [ “Alert” ; "This script can only be run from an Event screen.” ]
   Exit Script [ Text Result: "error" ]
Else If [ Get ( FoundCount ) = 0 ]
   # require record showing
   Show Custom Dialog [ “Alert" ; "This script can only be run with an Event record displayed.” ]
   Exit Script [ Result: "error" ]
End If

  1. Save the script and exit the Script Workspace.

Step 7 - Review your work

Your Event Schedule Report Build-On is now complete! Back in Browse mode, test your new report.

  1. From the Sessions tab of the Event Detail layout, run the report. Make sure you test it with several different event records.
  2. When you click the Create Event Schedule button, you should be prompted to enter a name for the PDF.
  3. When you are prompted to name the PDF, test what happens when you continue and when you cancel.
  4. When you continue, the PDF should open automatically, displaying the event schedule with sessions sorted chronologically.
  5. When the sessions for an event span multiple days, they should be broken out by day.