Build-On Tutorial:

Expenses

Expenses: Lesson 2

Expense report

Lesson Overview

In this lesson, you will create an expense report that shows all expenses for an event, grouped by category.

Here's what the finished report will look like:

Step 1 - Create report layout

Create a new layout that shows expenses grouped by category.

  1. Using the Manage Layout dialog (File > Manage > Layouts...):
    1. Duplicate the Blank | Report layout.
    2. Move the new layout to the Print folder.
    3. Rename the layout to "Event Expenses | Report".
    4. Change the layout context to show records from the EXPENSE table occurrence.
  1. Open the layout and enter Layout mode so you can begin to modify it.
  2. Change the text object at the top of the layout to display a merge field that shows the expense_EVENT::Event_Name field (Insert menu > Merge Field) on the first line and "Expense Report" on the second line.
  1. Open the Part Setup dialog (Layout > Part Setup...) and create a new Sub-summary part that appears when sorted by the EXPENSE::Category field. When prompted for whether the part should print above or below the records it summarizes, chose the Print Above option.
  1. Create a second Sub-summary part that also uses EXPENSE::Category, but choose the Print Below option this time. These two parts will act as a header and footer for each subset of expenses when they are grouped by their category.
  2. Create a Trailing Grand Summary part. The Part Setup dialog should now appear as shown below.
  1. Use the Position tab of the Inspector to set the Height of the new parts. Note that it often takes trial and error to set part heights to create an attractive report. For this report, use the following heights for each of the parts.
    1. Title Header: 56 pt
    2. Header: 36 pt
    3. Leading Sub-Summary: 66 pt
    4. Body: 37 pt
    5. Trailing Sub-Summary: 11 pt
    6. Trailing Grand Summary: 40 pt
    7. Footer: 36 pt
  1. Use the Rectangle tool in the status toolbar to create rectangles in both the leading Sub-summary part and the Trailing grand summary. There is no pre-defined style for this, so use the Appearance tab of the Inspector to manually set a light-gray fill color.
  1. Open the Manage Database dialog, and create a new summary field named “zz_s_Total_Amount" in the Expense table. Set it to be the Total of the Amount field.
  1. Add fields and objects to the layout as shown below. All fields are from the EXPENSE table:
    1. Category merge field in the leading Sub-summary part
    2. The text “Subtotal: ” and the zz_s_Total_Amount merge field in the leading Sub-summary part
    3. Date field in the Body with “Date” label above in the Sub-summary
    4. Expense_Name field in the Body with “Expense” label above in the Sub-summary
    5. Amount field in the Body with “Amount” label above in the Sub-summary
    6. The text “Grand Total: ” and the zz_s_Total_Amount merge field in the Trailing Sub-summary part
  2. As before, there are no pre-defined styles for these, so use the Default style for everything, and then set the following additional properties manually:
    1. Right-justify and right-align the Amount field, the Amount label, and the subtotal and grand total amount merge fields.
    2. Set the font size to 11 pt for all the fields in the Body part and their labels.
    3. Add a thin, gray line under the field labels, and another below the fields.
  1. Use the Data tab of the Inspector to set the currency formatting for the Amount field and the two merge blocks that contain the zz_s_Total_Amount field (Fixed number of decimals: 2, Currency: $, Use thousands separator turned on).

    Note: To save time, you can select all three of these objects by shift-clicking on them and then setting the currency options for all simultaneously.
  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:
    1. Remove blank space by sliding up based on All objects above
    2. Resize the enclosing part

      Note: This makes it so fields with a lot of content can be as tall as needed to display everything, but fields with less content will shrink dynamically.
  1. Enter Browse mode to save and review your work.

Step 2 - Create expense report script

Your next task is to write a script that will find all the expenses for an event, sort them by category, and generate a PDF from the layout you created earlier. You will develop the core elements of the script in this step. In the following steps, you will add additional options and error trapping.

  1. Open the Script Workspace and duplicate the Template Script.
    1. Name the new script “Report: Event Expenses”.
    2. Move the script to the REPORTS folder.
  2. Add the highlighted script steps below to generate the report.

    NOTE: See below 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

# go to related expense records, in new offscreen report window
Go to Related Record [ Show only related records; From table: “event_EXPENSE”; Using layout: “Event Expenses | Report” (EXPENSE) ; 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 expense category (for subsummary), then date, 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 in user's temporary folder 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” ; Parameters: ]
Close Window [ Current Window ]

Exit Script [ Result: $null ]

  1. For the Go to Related Record step:
    1. Set Get related record from: to the event_EXPENSE table.
    2. For Show record using layout, select the Event Expenses | Report 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 Show Custom Dialog step:
    1. Set the Title to “Error”
    2. Set the Message to “FileMaker error " & $error & ". Unable to generate this report."
    3. Delete the text from Button 2, so that users will only have one button option.
    4. Make sure Button 1 is set to “OK”.
  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. EXPENSE::Category, ascending
    2. EXPENSE::Date, ascending
    3. EXPENSE::Expense_Name, ascending

      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 “EXPENSE::” before each field included in the sort.
  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 3 - 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 Expenses tab of the Event Detail layout.
  2. Duplicate the Add Expense button you created in the previous lesson.
  3. Set the following properties for the new button using the Button Setup dialog.
    1. Set the name of the button to "Create Expense Report”.
    2. Set it to run the Report: Event Expenses script.
    3. Clear out the optional script parameter.
  1. Position the new button next to the Add Expense button.

Step 4 - Update create expense report script

In order to better improve the user experience, add a few more steps to the script you created earlier in this lesson.

You will update the Report: Event Expenses 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 Expenses script.
  2. Add the highlighted script steps below to prompt the user to enter a file name for the PDF.

    NOTE: See below for details about the Set Variable and Show Custom Dialog steps.

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

# prompt user for filename
Set Variable [ $file_name; Value:
   Let ( [ ~event = EVENT::Event_Name ;
   ~timestamp = _TimestampForFilename ];
   ~event & " Expenses " & ~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

# go to related expense records, in new offscreen report window
Go to Related Record [ Show only related records; From table: “event_EXPENSE”; Using layout: “Report | Event Expenses” (EXPENSE) ; New Window]

  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:

/* default filename */

Let ( [
   ~event = EVENT::Event_Name ;
   ~timestamp = _TimestampForFilename
] ;
   ~event & " Expenses " & ~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 to Show input field #1. In the subsequent dialog, choose Variable (instead of the default Field), and enter the variable $file_name. Click OK, then back in the “Show Custom Dialog” Options window, set the Label: to “File Name”.
  1. Next, update the section of the script that saves the report as a PDF. Add/edit the following highlighted script steps.

    NOTE: See below for 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, name the variable “$file_path” and the value to the following:

"file:" & Get ( TemporaryPath ) &

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

  1. Update the Save Records as PDF step:
    1. Set it to run without dialog by toggling the With dialog: setting to Off.
    2. Select the gear icon and select Specify output file.
    3. Add $file_path to output file path list.
    4. Be sure Automatically open file is turned on.
  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.

    Note: In the Show Custom Dialog steps, remove the default text “Cancel” next to the Button 2 label.

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

# 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 [ Text Result: "error" ]
End If

Commit Records/Requests [ With dialog: Off ]

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

# prompt user for filename
Set Variable [ $file_name; Value:
   Let ( [
   ~event = EVENT::Event_Name ;
   ~timestamp = _TimestampForFilename ];
   ~event & " Expenses " & ~timestamp & ".pdf" )]

  1. Save the script and exit the Script Workspace.

Step 5 - Review your work

Your expenses Build-On is now complete! Back in Browse mode, test and troubleshoot your new report.

  1. From the Event Detail layout, create several related expense records in several categories.
  2. When you click the Create Expense Report button, you should be prompted to enter a name for the PDF.
  3. The PDF should then open automatically, displaying the expense report summarized by category.
  4. Your report should show the subtotal for each category and the grand total for all the expenses.