Build-On Tutorial:

Expenses

Expenses: Lesson 1

Add and edit expenses

Lesson Overview

The objective of this build-on is to add functionality for tracking expenses related to an event. Over the course of two lessons, you will develop all of the tables, fields, relationships, and interface elements necessary to do this.

In this lesson, you will create the table, field, interface, and workflow elements necessary for viewing, adding, modifying, and deleting expenses.

Step 1 - Add Expense table and fields

Create a new table and fields for tracking expenses.

  1. Open the Manage Database dialog (File > Manage > Database...) and activate the Tables tab.
  2. Duplicate the Template table by selecting it, then clicking Copy, then Paste, at the bottom of the dialog.
  1. Rename the new table to Expense.
  2. Double-click the Expense table to see the list of fields it contains.
  3. Add the following fields to the Expense table:
    1. ID_Event (number)
    2. Amount (number)
    3. Category (text)
    4. Date (date)
    5. Expense_Name (text)
    6. Expense_Notes (text)

Step 2 - Create event and expense relationships

The ID_Event field in the Expense table is a foreign key that will be used to relate expenses back to an event. There is a one-to-many relationship from Event to Expense. You will now set up the relationships graph to reflect this.

  1. Switch to the Relationships tab of the Manage Database dialog. Note that a new table occurrence named Expense was automatically added to the graph when you created the new table in step 1.
  2. Select and duplicate the Expense table occurrence using Command-D (macOS) / Control-D (Windows).
  3. Double-click the new table occurrence and rename it to "event_EXPENSE".
  1. Double-click the original Expense table occurrence and rename it to "EXPENSE". This is done for consistency with the graph organization techniques used in the file.
  2. Use the color tool at the bottom of the graph to change the color of the EXPENSE table occurrence to the same blue used elsewhere and move it to the bottom of the graph.
  1. Back at the top of the graph, create a relationship between EVENT and event_EXPENSE by clicking on the ID field in EVENT and dragging over to the ID_Event field in event_EXPENSE.

    Note: The small, square button in the top, right corner of every table occurrence toggles the display between just showing the table occurrence name, showing the name and fields involved in relationships, and showing all fields.
  1. Double-click the line that connects the two table occurrences to open the Edit Relationship dialog. Click the option on the right that reads Delete related records in this table when a record is deleted in the other table.

    This is called a "cascading delete" and means that if an Event record is deleted, any related Expense records will also be deleted.
  1. Use the Add a table button at the bottom of the graph to create a new table occurrence of the Event table, and name it expense_EVENT. Position it near the bottom of the graph, next to EXPENSE. You will use this in the next lesson as part of creating an expense report for an event.
  1. Create a relationship between EXPENSE::ID_Event and expense_EVENT::ID. Position and style the new table occurrence as shown below.
  1. Close the Manage Database dialog to save your work.

Step 3 - View event expenses

Add and format a portal to the Event Detail layout to show related expenses.

  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 navigate to the Desktop > Event > Event Detail layout.
  3. Select the Tab Control object from the Object tab, and note the x's on the object that denote it is locked. Currently there are four panels in the tab control: Overview, Attendees, Sessions, and Notes.

    Note: If the Objects tab is not open, click the objects pane icon in the status toolbar, then click Objects.
  1. Use the Arrange > Unlock menu to unlock the tab control.
  2. Open the Tab Control Setup dialog (Format > Tab Control Setup…, or double-click the tab control) to create a new tab panel named “Expenses". Type “Expenses” into the Tab Name, click Create, then OK to exit out of the dialog.
  1. Each tab panel has a blue line under the tab name to indicate the active tab. To add this to the new Expenses tab:
    1. Click the Expenses tab to make sure it is the active tab.
    2. Scroll to the right and notice an object off the visible right edge of the layout.
    3. Copy the blue horizontal line and drag it under the Expenses tab name.
  2. Use the Portal tool from the status toolbar to add a large portal to the Expenses tab. (It should be the same size and position as the portal on the Sessions tab.)
  1. In the Portal Setup dialog:
    1. Set the Show records from: option to event_EXPENSE
    2. Select Sort portal records, then in the subsequent dialog move both the Date and the Expense_Name into the Sort Order area. Sort by Date in Descending order, then by Expense_Name in Ascending order.
    3. Back on the Portal Setup window, check Allow Vertical Scrolling then set Show scroll bar: to When scrolling 
    4. Set Number of rows: to 6
    5. Check Use alternate row state.
  1. In the Add Fields to Portal dialog, select the Date, Expense_Name, Category, and Amount fields and move them to Included fields. Click OK to complete the portal setup.
  1. Open the Inspector using the View > Inspectors > Inspector menu item (you can also access it using keyboard shortcut Command-I (macOS) / Control-I (Windows)).
  2. Activate the Position tab of the Inspector.
  1. Set the Autosizing option for the portal to anchor top, bottom, right and left so the portal will stretch both vertically and horizontally to fill the space available as the window is resized.
  1. Activate the Styles tab of the Inspector.
  1. Apply the following styles:
    1. Leave the portal itself as the Default style
    2. Date - style: txt Minimal.Black.Left
    3. Expense_Name - style: txt Minimal.Black.Left
    4. Category - style: txt Minimal.Black.Left
    5. Amount - style: txt Minimal.Black.Right
  2. Activate the Data tab of the Inspector.
  1. For each field in the portal, do the following:
    1. Turn off field entry in Browse mode (users will edit expenses in another window, not on this portal).
    2. Specify a human-friendly value (i.e., "Expense Name" instead of "Expense_Name") as placeholder text.
  1. Select the Amount field, and use the Data Formatting section of the Inspector to specify Currency formatting, two decimal places, and a comma as the thousands separator.
  1. Adjust the widths and positions of the fields in the portal so yours looks similar to the image below.
  1. Use the Exit Layout button to return to Browse mode and review your progress. Keep in mind there is no data yet in the Expense table.

    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 4 - Add field to display total expenses

Create a field that will total up all of an event's expenses, then display it on the Expenses tab.

  1. In the Manage Database dialog, create a calculation field in the Event table named Total_Expenses with the following formula: If ( IsEmpty ( event_EXPENSE::ID ) ; 0 ; Sum ( event_EXPENSE::Amount ) ) 

    Ensure the Calculation result is Number.

    NOTE: Delete this field from the layout if it is automatically added to it.
  1. Exit the Manage Database dialog. Enter Layout mode and activate the Expenses tab.
  2. Select Insert > Merge Field... to add the Total_Expenses field to the layout. Note: Merge fields are text objects that show field data. Users cannot click into them or modify them as they can with regular field objects.
  3. Make several formatting changes to the merge field:
    1. Double-click into it and add the text "Total Expenses: " before the field name
    2. Set the style to label Left
    3. Set it use currency formatting (similar to the Amount field)
    4. Position it above the portal, as shown below

Step 5 - Card window for creating and editing expenses

The portal you created in Step 3 is perfect for viewing an event's expenses. For creating and editing expenses, however, you will make a new card window.

  1. Open the Manage Layouts dialog (File > Manage > Layouts...).
  2. Locate the Blank > Blank | Desktop - Card layout and Duplicate it.
  3. With the new layout selected, click the Edit button to open the Layout Setup dialog, and make the following changes:
    1. Set the Layout Name to "Expense | Card"
    2. Set Show records from: EXPENSE 
    3. Set Menu Set: Minimal

      Note: The Event Management Template file includes a custom menu set called "Minimal." There are cases when you do not want users to be able to use every menu function available to them, such as when they are in a card window for a specific step. The Minimal menu set restricts users to working only with the layout and found set they are presented with. Use this when you want to tightly control what a user is able to do.
  1. Back in the Manage Layouts dialog, click the dropdown arrow next to the New button at the bottom of the dialog to create a new folder named Expense. Move it into the Desktop folder, and then place the new layout into it, as shown below.

    Note: When you first created the Expense table in Step 1, FileMaker automatically created a layout named Expense that should now appear at the bottom of the Manage Layouts dialog. It is a common practice to create special developer-only layouts for each table in a FileMaker app. Such layouts often contain all fields in the table and are helpful for developers to use in reviewing system data. They can also be used for scripted processes that find, create, or delete records. Your file contains a special Developer layout folder for organizing these layouts, and each layout is named <Table Name> Utility. Now would be a good time for you to rename that default layout to Expense Utility and move it into the Developer folder.
  1. Click the Open button to open the Expense | Card layout, and enter Layout mode.
  2. Edit the merge field at the top of the layout to say "<<$$CARD_ACTION>> Expense".

    The global variable will contain either "New" or "Edit" and will be set by scripts you create later in this lesson. This way, you can use the same layout both for creating new records and for editing an existing one.
  3. Double-click the sample field to bring up the Specify Field dialog. In the drop-down change the table occurrence to Current Table (“EXPENSE”) and select the Expense_Name field. Change the text label to “Expense Name*”. 

    Duplicate the label and the field and update them to create the fields and labels below (listed as “Field - Label”):
    1. Date - Date*
    2. Amount - Amount*
    3. Category - Category*
    4. Expense_Notes - Notes  

      The asterisks by some labels will denote to the user that these fields are required. You will add validation to require values in these fields later.

      Use the Data tab of the Inspector to set the Control style for the Date field to Drop-down Calendar. Select Include icon to show and hide calendar.  

      Apply currency formatting to the Amount field.  

      Resize and arrange the fields as necessary to look similar to the image below:
  1. Enter Browse mode to save and review your work.

Step 6 - Script for opening card window

There are two use cases for the new expense card layout: creating a new expense, and editing an existing expense. Because these processes will be defined in separate scripts you will write later in this lesson, it will be helpful to create a subscript to handle opening the card window and specifying the layout so this logic is only defined once.

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…). Because this layout uses a minimal custom menu set, you will not see these options in Browse mode. Either open the Script Workspace while you are in Layout mode, or change your menus by selecting the Tools > Custom Menus > [Standard FileMaker Menus] menu item.
  2. Create a new subfolder within the DESKTOP UI folder named "EXPENSE".
  1. Duplicate the Template Script in the EXAMPLE SCRIPTS folder. Rename the script to "Open Expense Card" and move it into the folder you just created.

    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.
  2. Add the highlighted script steps below. These will modify the script so it checks to make sure the current window is not a card window (you cannot open a card window on top of an existing one) and then opens the Expense | Card layout as a card window.

    Note: in the "New Window” Options window of the New Window step, make sure to uncheck the Close option.

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

# close existing card window (if applicable)
If [Get ( WindowStyle ) = 3 ]
    Perform Script [Specified from list; "Close Window" ; Parameter: ]
End If

# open card window
New Window [ Style: Card; Name: "Expenses" ; Using layout "Expense | Card" (EXPENSE] ]


Exit Script [ Text Result ; $null ]

  1. The final script should look like this:
  1. Save and close the script.

Step 7 - Script for adding an expense

Next, you will create a script for adding a new expense record. In a later step, you will attach this script to a button on the Expenses tab of the Event Detail layout.

The script begins by using the #Assign custom function to parse any script parameters and test to make sure they are are valid. It then proceeds to open the card window, set the $$CARD_ACTION variable, create a new record, and put the cursor into the Expense_Name field so the user can immediately begin typing.

Note: The #( ) and #Assign ( ) custom functions are used to pass and parse parameters. The #( ) custom function is generally used to define a script parameter and accepts a key-value pair; the #Assign ( ) custom function is called in the script and turns the key-value pair into a script variable.

  1. Create another new script by duplicating the Template Script.
    1. Name the script “Add New Expense ( id_event )”
    2. Move it into the EXPENSE folder.
  2. Modify the script by adding the highlighted steps below.

    Note: In the Show Custom Dialog script step, remove the default text “Cancel” next to the Button 2 label. This will leave users with only one button to choose from: “OK”.

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

# load parameter(s)
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" ]
Else If [ IsEmpty ( $id_event ) ]
   # invalid parameter(s) passed
   Show Custom Dialog [ "Error" ; "Invalid parameter(s) passed to script: " & Get (ScriptName) ]
   Exit Script [ Text Result: "error" ]
End If

# set card action
Set Variable [ $$CARD_ACTION ; Value: "New" ]

# open card window
Perform Script [ Specified From list ; "Open Expense Card"; Parameter: ]

# create record
New Record/Request
Set Field [ EXPENSE::ID_Event ; $id_event ]
Commit Records/Requests [ With dialog: Off ]

# leave the cursor in the expense name field
Go to Field [ EXPENSE::Expense_Name ]

Exit Script [ Text Result ; $null ]

  1. The final script should look like this:
  1. Save and close the script

Step 8 - Script for validating and saving expense

For the purpose of this lesson, imagine that when a user is done adding or editing an expense, their entry needs to pass certain validation rules before they can proceed. This will provide you with a good example of how to define and implement your own business rules in your apps. The validation rules you will enforce are:

   - Expense_Name, Category, Date, and Amount are required fields
   - The system should not allow duplicates based on these four fields.

  1. Open the Manage Database dialog and activate the Relationships tab.
  2. Duplicate the EXPENSE table occurrence using Command-D (macOS) / Control-D (Windows).
    1. Name the new table occurrence "expense_SELF_Duplicate".
    2. Change the color to gray.
    3. Resize and move it to be aligned with the other table occurrences in the second column.
  1. Create a relationship between EXPENSE and expense_SELF_Duplicate based on matching Amount, Category, Date, and Expense_Name; and non-matching ID.

    For example, select Amount on the left, Amount on the right, choose the “=“ sign as the operator, then click Add. Repeat these steps for each matching pair. For a non-matching pair, select “” as the operator.

    This relationship will find all expenses with matching data. By including the non-matching ID, though, a record will not find itself as a duplicate.
  1. The Relationships graph should look like this:
  1. Open the Script Workspace and duplicate the Template Script again
    1. Name the new script "Validate and Save Expense".
    2. Move it into the EXPENSE script folder.
  2. Modify the script by adding the highlighted steps below.

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

# context requirements
If [ Get ( LayoutTableName ) ≠ "EXPENSE" ]
   # required table context
   Show Custom Dialog [“Alert"; "This script can only be run from an Expense screen."]
   Exit Script [ Result: "error" ]

Else If [ Get ( FoundCount ) = 0 ]
   # require record showing
   Show Custom Dialog [“Alert"; "This script can only be run with an Expense record displayed."]
   Exit Script [ Result: "error" ]
End If

Commit Records/Requests [ With dialog: Off ]

# validate data
If [ IsEmpty ( EXPENSE::Expense_Name ) ]
   Set Variable [ $error_msg; Value: "Expense Name is required." ]
Else If [ IsEmpty ( EXPENSE::Date ) ]
   Set Variable [ $error_msg; Value: "Date is required." ]
Else If [ IsEmpty ( EXPENSE::Amount ) ]
   Set Variable [ $error_msg; Value: "Amount is required." ]
Else If [ Filter ( EXPENSE::Amount ; "0123456789.-" ) ≠ EXPENSE::Amount ]
   Set Variable [ $error_msg; Value: "Amount must be a numeric value." ]
Else If [ IsEmpty ( EXPENSE::Category ) ]
   Set Variable [ $error_msg; Value: "Category is required." ]
Else If [ not IsEmpty ( expense_SELF_Duplicate::ID ) ]
   Set Variable [ $error_msg; Value: "This appears to be a duplicate expense. Either correct it or " & If ( $$CARD_ACTION = "new" ; "choose Cancel." ; "delete it." ) ]
End If

# handle validation
If [ not IsEmpty ( $error_msg ) ]
   # if validation error, display message and keep card window open
   Show Custom Dialog ["Alert"; $error_msg]
Else
   # otherwise, close card window
   If [ Get ( WindowStyle ) = 3 ]
      Perform Script [ Specified: From list; “Close Window” ; Parameter: ]
   End If
End If

Exit Script [ Text Result ; $null ]

  1. Save and close the script.

Step 9 - Create script for finding an expense

The final script you need is one that handles editing an expense. This script is similar in many ways to the one you wrote for adding expense. It begins by using #Assign custom function to parse any script parameters and test to make sure they are are valid. It then opens the card window and sets the $$CARD_ACTION variable. Instead of creating a new record, however, this script then finds the existing expense record. It also includes steps to handle errors, in case the record cannot be found.

  1. Open the Script Workspace and duplicate the Template Script.
    1. Name the new script "Find Expense by ID (id_expense)”.
    2. Move it into the EXPENSE script folder.
  2. Modify the script by adding the highlighted steps below.

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

# load parameter(s)
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" ]
Else If [ IsEmpty ( $id_expense ) ]
   # invalid parameter(s) passed
   Show Custom Dialog ["Error"; "Invalid parameter(s) passed to script: " & Get ( ScriptName ) ]
   Exit Script [ Text Result: "error" ]
End If

#set card action
Set Variable [ $$CARD_ACTION; Value: "Edit" ]

# open card window
Perform Script [ Specified: From list; “Open Expense Card”; Parameter: ]

# find record
Enter Find Mode [ Pause: Off ]
Set Field [ EXPENSE::ID; "==" & $id_expense ]
Set Error Capture [ On ]
Perform Find [ ]
Set Variable [ $error; Value: Get ( LastError ) ]
Set Error Capture [ Off ]

If [ $error ]
   Perform Script [ Specified: From list; “Close Window”; Parameter: ]
   Show Custom Dialog ["Error"; If ( $error = 401 ; "The expense could not be found." ; "FileMaker error: " & $error & "." ) & " Unable to perform this action."]
   Exit Script [ Text Result: "error" ]
End If

Exit Script [ Text Result: $null ]

Step 10 - Adding buttons

With all of the scripts now in place, your final task is to make buttons to trigger them. On the Expense tab of the Event Detail layout, you will create buttons for adding and editing expenses. On the card window, you will modify the existing buttons to call the appropriate actions for validating and saving an expense record.

  1. Enter Layout mode and navigate to the Expenses tab of the Event Detail layout.
  2. Use the Button tool in the status toolbar to create a button below the expense portal.
  3. In the Button Setup dialog, set the following properties for the button:
    1. Set the button name to "Add Expense”.
    2. Set the Action: to Perform Script.
    3. Select the Add New Expense (id_event) script.
    4. For the script parameter, specify the formula: # ( "id_event" ; EVENT::ID )
  1. Set the left and bottom anchors in the Autoresizing options for the button, using the Position tab of the Inspector.
  1. For editing an expense, you will place an invisible button over the entire portal row.
    1. Use the Button tool to place a button over the entire first portal row.
    2. Set left, right, and top anchors for the object.
  1. In the Button Setup dialog, set the following properties for the button:
    1. Leave the button name empty
    2. Set the Action: to Perform Script
    3. Select the Find Expense by ID (id_expense) script
    4. For the script parameter, specify the formula: # ( "id_expense" ; event_EXPENSE::ID )
  2. Change the style of the button to btn Row.Hover.
  3. In the Objects panel, click and drag the button to position it below the fields on the portal. This places it behind those fields on the layout, within the portal.
  1. In Layout mode, navigate to the Expense | Card layout.

    Note: At the bottom of this layout, there are two sets of stacked buttons. If you temporarily move or hide the Cancel and Create buttons, you will see Delete and Close buttons underneath them (see below). Hide conditions have been specified for these objects so the first pair displays when the global variable $$CARD_ACTION is "New" and the second pair when it is not. The Cancel and Delete buttons are specified to call the Delete Record ( msg ) script. These do not require any modification.
  1. Go into the Button Setup dialog for both the Create and Close buttons and change them so that each triggers the Validate and Save Expense script. If you moved the buttons, adjust them so that they are layered again.
  2. Enter Browse mode so you can save and review your work.

Step 11 - Review your work

With all of the schema, interface, and scripting now in place, you can test the new expense features.

  1. When you go to the Event Detail layout, you should see a tab that lists the expenses for the event and a button for adding a new expense.
  2. When you click the Add Expense button, you should see the Expense | Card layout and see a blank, new expense record. The header should display "New Expense" and the buttons at the bottom should be Cancel and Create.
  3. If you click the Cancel button, you should get no confirmation dialog, and there should be no new record in the portal.
  4. If you click the Create button, you should get a data validation error message and not be allowed to close the window. Enter data into each field, one at a time, trying to close the window after each one. You should get a different validation error message each time. Once you have entered data into each required field, click Create and verify that you are able to save the new record.
  5. When you return back to the Event Detail record for that event, you should see the new expense record in the portal.
  6. If you click on the portal row, the card window should reappear and display the data for the expense you created previously. The header should display Edit Expense and the buttons at the bottom should be Delete and Close.
  7. Try creating another new expense record. This time enter all the same information as you did on the original record, then click Create. Verify that you get a validation error saying that this appears to be a duplicate record.