Build-On Tutorial:

Attachments

Attachments: Lesson 1

Create and Edit Attachment Records

Lesson Overview

The objectives for this lesson are to create a table, layouts, and scripts so that a user can add attachments (PDFs, images, and other files) to sessions.

Here is a sneak peek of what you will build:

Step 1 - Create a new table

Create the new Attachment table by duplicating the existing Template table.

  1. Open the Manage Database window by using the File > Manage > Database… menu item.

    Note: You can also open the Manage Database window by entering Layout mode and clicking the Manage button or by using the keyboard shortcut Shift-Command-D (macOS) / Shift-Control-D (Windows).
  2. Choose the Tables tab to view the list of existing tables.
  1. Duplicate the Template table by selecting it in the list, clicking the Copy button, and then clicking the Paste button.

    Note: You can also use the standard copy and paste keyboard shortcuts.
  2. The new table will be named Template 2 by default. Rename the Table to “Attachment" using the Table Name field and then clicking the Change button.

Step 2 - Add fields to the table

Next, you will add fields to the table.

  1. You should still be in the Manage Database window after completing Step 1. If not, re-open the Manage Database window.
  2. Still in the Tables tab in the Manage Database dialog, double-click on the Attachment table to switch to the Fields tab and view the fields in that table.
  3. Add the following new fields to the Attachment table:
    1. Attachment (container)
    2. Attachment_Description (text)
    3. ID_Session (number)
    4. Attachment_Name (text)
  4. After creating the Attachment_Name field, select it in the list of fields and click the Options button.
    1. In the Auto-Enter tab, click the Calculated value box. In the resulting Specify Calculation dialog, enter the calculation: GetContainerAttribute ( Attachment ; "filename" ) (see the image below)
    2. Click the OK button to dismiss the Specify Calculation dialog.
    3. Uncheck the Do not replace existing value of field (if any) checkbox.
    4. Click OK to save the field options.
  1. The list of fields should look as follows.

Step 3 - Create session and attachment relationships

The ID_Session field in the Attachment table is a foreign key that will be used to relate attachments back to a session. There is a one-to-many relationship from Session to Attachment. 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 Attachment was automatically added to the graph when you created the new table in Steps 1 and 2.
  2. Select and duplicate the Attachment table occurrence using Command-D (macOS) / Control-D (Windows).
  3. Double-click the new table occurrence and rename it to "session_ATTACHMENT".
  1. Double-click the original Attachment table occurrence and rename it to "ATTACHMENT". 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 ATTACHMENT table occurrence to the same blue used elsewhere and move it to the bottom of the graph.
  1. Move the session_ATTACHMENT table occurrence down the graph near the SESSION table occurrence and create a relationship between SESSION and session_ATTACHMENT:
    1. Click on the upper-right corner of the SESSION table occurrence until it expands to display its list of fields.
    2. Click on the ID field in SESSION and drag over to the ID_Session field in session_ATTACHMENT.
  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 a Session record is deleted, any related Attachment records will also be deleted.
  1. Use the Add a table button at the bottom of the graph and select the Session table to create a new table occurrence of the Session table, and name it attachment_SESSION.
  1. Create a relationship between ATTACHMENT::ID_Session and attachment_SESSION::ID. Do not set any cascading deletes in this relationship. Position and style the new table occurrence as shown below.
  1. Close the Manage Database dialog to save your work.

Step 4 - Rename and and organize utility layout

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. The Event Management Template contains a special Developer layout folder for organizing these layouts, and each layout is named <Table Name> Utility.

Use the Manage Layouts dialog (File > Manage > Layouts…) to rename and organize the newly created ATTACHMENT layout for this purpose.

  1. Locate the new ATTACHMENT layout at the bottom of the list. Then open the Layout Setup dialog by double-clicking on the ATTACHMENT layout name, or selecting it and clicking the Edit button. Rename it to “Attachment Utility”.
  1. Back in the Manage Layouts dialog, drag the Attachment Utility layout into the Developer folder, just above the Attendee Utility layout. Ensure that Include in layout menus is not checked.

Step 5 - View session attachments

Add a new tab and portal to the Session Detail layout that shows related attachments.

  1. Using the Manage Layouts dialog, duplicate the Desktop > Session > Session Detail layout and rename it Session Detail - BK <current date> . It’s always a good idea to make a dated backup of a layout before you edit it in case you need to revert back to it.

    Note: It can be helpful to keep your backup layouts organized and separated from your working layouts by creating a layout folder named “Backups”.
  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. Use the Layout pulldown menu in the status toolbar to navigate to the Desktop > Session > Session Detail layout.
  4. Open the Objects Pane using View > Objects > Object Tab menu and select the tab control object, and note the x's on the layout object that denote it is locked. Currently there are two panels in the tab control: Overview and Notes.
  1. Use the Arrange > Unlock menu to unlock the tab control.
  2. Open the Tab Control Setup dialog (double-click on the tab control object, or select it and choose Format > Tab Control Setup...) and create a new tab panel named "Attachments".
  1. Each tab panel has a blue line under the tab name to indicate the active tab. To add this to the new Attachments tab:
    1. Click the Attachments 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 that says 'Use this for tab underline' (see below image).
    3. Select the blue horizontal line and duplicate it (Edit > Duplicate) and drag the duplicated line under the Attachments tab name.
  1. Use the Portal tool from the status toolbar and draw a rectangle inside the new Attachments tab.
  1. In the subsequent Portal Setup dialog:
    1. Set the Show records from: option to session_ATTACHMENT.
    2. Check Sort portal records and set to sort by Attachment_Name.
    3. Check Allow vertical scrolling and set Show scroll bar: to When scrolling
    4. Set Number of rows: to 3.
    5. Check Use alternate row state.
  1. Next, in the Add Fields to Portal dialog, select the Attachment, Attachment_Name and Attachment_Description fields and move them to Included fields. Click OK to complete the portal setup.
  1. Open the Inspector using the View > 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. Select the portal object, and set its Size to 736 pt wide by 326 pt tall. Then arrange and resize the portal and fields so your layout looks similar to the image below.

    Note: If the units 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. Select the portal object again. In the Autosizing area of the Inspector, select the top, bottom, left, and right anchors so the portal will stretch both vertically and horizontally to fill the space available as the window is resized.
  1. Select the Attachment_Name and Attachment_Description fields (use Shift+click to select multiple objects) and set their Autosizing options to anchor top, left, and right so that they stretch horizontally as the window is resized.
  2. Activate the Styles tab of the Inspector.
  1. Apply the following styles:
    1. Leave the portal itself as the Default style
    2. Attachment - style: container Minimal
    3. Attachment_Name - style: txt Minimal.Black.Left
    4. Attachment_Description - style: txt Minimal.Gray.Left
  2. Activate the Data tab of the Inspector.
  1. Make the following modifications to the fields in the portal:
    1. Select each field in the portal, and in the Field entry area in the Inspector, uncheck Browse Mode.
    2. Select the Attachment_Name field, and specify the Placeholder text “Attachment Name”.
  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 Attachment 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 6 - Add field to display the attachment count

Add a field to calculate the number of attachments for each session, and display the value above the Attachments portal.

  1. Open the Manage Database dialog, and navigate to the Fields tab. Select the Session table, then create a new calculation field named "Count_Attachments" with the following formula: Count ( session_ATTACHMENT::ID )
    1. Set the Calculation result to Number.
    2. Uncheck the Do not evaluate if all referenced fields are empty option.

      NOTE: Delete the "Count_Attachments" field from your layout if it was automatically added.
  1. Exit the Manage Database dialog. Enter Layout mode on the Session Detail layout and activate the Attachments tab.
  2. Select Insert > Merge Field… to add the Count_Attachments field to the layout above the attachments portal.

    Note: You can also use the Text tool to add a merge field by simply typing “<<Count_Attachments>>”.
  3. Make several formatting changes to the merge field:
    1. Double-click into it and add the text "Attachments: “ before the merge field, so the complete text reads “Attachments: <<Count_Attachments>>”.
    2. Set the style to label Left.
    3. Position it above the portal, as shown below.
  1. Enter Browse mode to save and review your work.

Step 7 - Create card layout for creating and editing attachments

The portal you created in Step 5 is perfect for viewing a session’s attachments. For creating and editing attachments, however, you will make a new card window layout.

  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 (or double-click on the layout name) to open the Layout Setup dialog, and make the following changes:
    1. Set the Layout Name to "Attachment | Card"
    2. Set the Show records from: to ATTACHMENT
    3. Set the Menu Set: to 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. Use the dropdown arrow next to the New button at the bottom of the Manage Layouts dialog to create a new folder named Attachment. Move it into the Desktop folder at the bottom of the list, and then place the new layout into it, as shown below.
  1. Select the Attachment | Card layout in the list, and click the Open button to open it. Then enter Layout mode.
  2. You will need to increase the size of the layout in order to fit all the content you are going to add. Select the Body layout part handle, and using the Position tab of the Inspector, resize it to 648 pt tall. Then select the buttons at the bottom of the layout and set their Top position to 652 pt. Note that there are four buttons: two stacked on top of another two. Move them all.
  1. Edit the merge field at the top of the layout to say “<<$$CARD_ACTION>> Attachment”.

    The $$ syntax indicates that $$CARD_ACTION is a global variable. This 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.
  2. Change the “Field Label” text to “Name”.
  3. Double-click on the g_Sample_Field to open the Specify Field dialog. In the table drop-down list, select Current Table (“ ATTACHMENT”). Then choose the Attachment_Name field.
  4. Duplicate both the field label and field and move them below the Name field. Change the second label to “Description”, and modify the second field to display the Attachment_Description field.
  5. Using the Fields tab, drag and drop the Attachment field to the body of the layout. (If the Fields tab is not open, click the Objects pane icon in the status toolbar, then click Fields.)
  6. Resize and position the objects as you see in the screenshot below.
  1. Leave the Cancel and Create buttons as-is; you will learn about these in a future lesson.
  2. Enter Browse mode to save and review your work.

Step 8 - Add a script to open the card window

There are two use cases for the new attachment card layout: creating a new attachment, and editing an existing one. 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 that 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 “ATTACHMENT". Exclude the folder from the scripts menu by unchecking the box at left. (If the checkboxes are not displayed, click the Scripts menu management toggle button at the top of the screen.)
  1. Duplicate the Template Script. Rename the script to "Open Attachment 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. Modify the script so that 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 Attachment | Card layout as a card window.

    See Steps 5 and 6 for further instructions on the Perform Script and New Window steps, respectively.

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Enter Browse Mode [ Pause: 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: “Attachment" ; Using layout: “Attachment | Card” (ATTACHMENT) ]


Exit Script [ Text Result: $null ]

  1. For the Perform Script step, double-click on the script step to open the Specify Script dialog. Then choose the Close Window script.
  2. For the New Window script step, double-click on the script step or click the gear icon at right to open the New Window Options dialog:
    1. Set Window Style to Card.
    2. Set Window Name to “Attachment”.
    3. Set Layout to Attachment | Card.
    4. Under Window Options, uncheck Close.
  1. Save and close the script.

Step 9 - Add a script to create a new attachment

Next, you will create a script for adding a new attachment record. In a later step, you will attach this script to a button on the Attachments tab of the Session 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, and create a new attachment record related to the specified session.

  1. Create another new script by duplicating the Template Script.
    1. Name the script “Add New Attachment ( id_session )”
    2. Move it into the ATTACHMENT folder.
  2. The script name uses a naming convention to indicate it expects a parameter named “id_session”. Amend the “Parameters” comment in the script to indicate this.

    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 key-value pairs into script variables.

# Parameters: Specify using # function:
    $id_session (required)

  1. Modify the script by adding the highlighted steps below.

    NOTE: See steps 4 and 5 for further instructions on the Show Custom Dialog and Set Field steps, respectively.

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 [ Result: "error" ]
Else If [ IsEmpty ( $id_session ) ]
    # 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 Attachment Card” ; Parameter: ]

# create record
New Record/Request
Set Field [ ATTACHMENT::ID_Session ; $id_session ]
Commit Records/Requests [ With dialog: Off ]


Exit Script [ Text Result: $null ]

  1. For the Show Custom Dialog script step, double-click on the script step or click the gear icon to the right of the script step to open the Show Custom Dialog Options dialog.
    1. Set the Title to the formula: “Error
    2. Set the Message to the formula: "Invalid parameter(s) passed to script: " & Get ( ScriptName )
    3. Set the Default Button to the formula: “OK
    4. Delete the information in Button 2. Only the default button is needed.
  1. For the Set Field script step, click the gear icon to the right of the script step to open a popover with additional options. Choose ATTACHMENT::ID_Session as the target field, and set the calculated result to $id_session.
  1. Save and close the script.

Step 10 - Add a script to view an existing attachment

The final script you need is one for handling editing an existing attachment. This script is similar in many ways to the one you wrote for adding a new attachment. It begins by using the #Assign custom function to parse any script parameters and test to make sure they are are valid. As in the other script, it then proceeds to open the card window and set the $$CARD_ACTION variable. Instead of creating a new record, however, this script then finds an existing attachment.

  1. Open the Script Workspace and duplicate the Template Script.
    1. Name the new script "Find Attachment by ID ( id_attachment )”
    2. Move it into the ATTACHMENTS folder
  2. Modify the Parameters comment:

# Parameters:     Specify using # function:
    $id_attachment (required)

  1. Modify the script by adding the highlighted steps below:

    NOTE: See Steps 4 and 5 for further instructions on the Enter Find Mode and Show Custom Dialog steps, respectively.

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_attachment ) ]
    # 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 Attachment Card” ; Parameter: ]

# find record
Enter Find Mode [ Pause: Off ]
Set Field [ ATTACHMENT::ID ; "==" & $id_attachment ]
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 attachment could not be found." ; "FileMaker error: " & $error & “." ) & " Unable to perform this action.” ]
    Exit Script [ Text Result: "error" ]
End If


    Exit Script [ Text Result: $null ]

  1. For the Enter Find Mode script step, make sure that Pause is set to Off (toggle this setting by clicking the text Off or On). Otherwise, the script will pause at this step and wait for user action.
  2. For both of the Show Custom Dialog script steps, there should only be one button labeled “OK".

    In the second instance of this step (see line number 43 in the image for Step 3 above), the custom dialog Message is calculated based on the particular error generated by the Perform Find script step that precedes it:
  1. Save and close the script.

Step 11 - Add buttons

With all of the scripts now in place, your final task is to make buttons to trigger them. On the Attachments tab of the Session Detail layout, you will create buttons for adding and editing attachments. On the card window, you will review the existing buttons, though no modifications to them are necessary until the next lesson.

  1. Enter Layout mode and navigate to the Attachment tab on the Session Detail layout.
  2. Use the Button tool in the status toolbar to draw a button below the attachments portal.
  1. In the resulting Button Setup dialog:
    1. Select the Display only a label button, and give it the name "Add Attachment”.
    2. In the Action: drop-down, choose Perform Script, and in the resulting Specify Script dialog, choose the Add New Attachment ( id_session ) script.
    3. In the Optional Script Parameter: area, set the parameter to: # ( "id_session" ; SESSION::ID )
  1. Close the Button Setup dialog.
  2. Using the Inspector, give the button left and bottom anchors, and set the size and position as shown below.
  1. For editing an attachment, you will place an invisible button over the entire portal row. Use the Button tool to draw a button over the entire first portal row. In the Button Setup dialog:
    1. Select the Display only a label button, but leave the name blank.
    2. Set the Action to perform the script: Find Attachment by ID ( id_attachment )
    3. For the script parameter, specify the formula: # ( "id_attachment" ; session_ATTACHMENT::ID )
  1. For now, your button will look similar to this image.
  1. Use the Inspector to finish configuring the button:
    1. Give it the style btn Row.Hover. Note that this style makes the button invisible (including while in Layout mode, though you can still select it and see its boundaries when it is the selected object).
    2. Set the button dimensions to 734 pt wide and 105 pt tall. Then position the button as needed so that it is situated entirely within the portal row.
    3. Set top, left, and right anchors in the Autosizing options for the button.
    4. In the Arrange & Align section of the Position tab, click the Send to back button to place the button behind the fields in the portal row.
  1. Your completed button should look similar to the image below.
  1. Enter Browse mode to save and review your work.

Step 12 - Review your work

  1. In Browse mode, navigate to the Attachments tab for a session and click the Add Attachment button. A New Attachment card window should display. Right-click on the Attachment field to insert a picture or file and then add a description for the attachment. Click the Create button and the attachment should appear in the portal.
  2. Add another attachment and verify the count is incremented.
  3. Click on an attachment record in the portal to show the Edit Attachment card window. Try editing the attachment details or use the Delete button to delete the attachment.