Propa-Gator: Modular Record Creation

A few weeks back I posted my first contribution to Todd Geist’s new collaborative effort to create a library of shared FileMaker modules. This script was originally inspired by the need to create reusable code for my customer projects at MightyData. My goal with the Propa-Gator module was to increase my coding efficiency by creating a portable script that would allow the creation of records and setting of any number of fields in the new record with any desired value.

In addition, I wanted the script to be able to use global field inputs as the field values in the new record and to nullify those globals on completion. Finally, I wanted to ensure that the script was completely context free and immune to field renaming.

The result was a script that I found to be a very useful in a variety of contexts:

1. Related records can be created such as log records, phone numbers and attributes.
2. Flat records can be normalized.  For example, if you have a table with two fields representing phone number 1 and phone number 2, you can loop through the found set of records and call the CreateRecord script twice, creating related phone number records in a phoneNumber table. I found this to be extremely useful when importing excel spreadsheets supplied by customers.
3. Create record from global fields. Below is a screen shot from the examples provided with the Propa-Gator file.  In this example, the user can enter values in two global fields. The second field uses an OnObjectExit trigger to launch the CreateRecord script with the globals used as data inputs. The script has the smarts to identify that the inputs were from global fields and will nullify those globals.

Scripted Find Requests

A common FileMaker technique is to build find requests using something like the following sequence of script steps:

Enter Find Mode []
Set Field [someTable::DateField1 ; ">=01/01/2010"]
New Records/Request
Set Field [someTable::StatusField1 ; "Complete"]

The CreateRecord script can be used in this case to create the find requests, as in example 3 that is included with Propa-Gator.

Enter Find Mode []
# Creates and sets values for first find request
Perform Script [CreateRecord] 
# Creates and set values for second find request
Perform Script [CreateRecord] 
# Delete the first request (it is blank)
Go to Record/Request/Page [First]
Delete Record/Request [No dialog]

Implementing CreateRecord Script

Implementing the CreateRecord script in your solutions is as easy as copying four scripts, three of which are for script trigger suppression and are not required. After you copy the scripts, take a look at the examples to explore ways of implementing the CreateRecord script in your solution. The most difficult part of this process will be properly implementing the script parameter that is passed. While the parameter syntax is clearly documented in the comments of the CreateRecord script, here is the example parameter with a bit of explanation. The parameter, when parsed in the script, creates three local variables: $CR_LayoutName, $CR_TargetFields, and $CR_Data.

"$CR_LayoutName = " & Quote ("DEV_Child")  & ";¶" & 
"$CR_TargetFields= " & 
Quote (
  List ( 
    GetFieldName ( Child::id_Parent ) ;
    GetFieldName ( Child::ChildData1 ) ;
    GetFieldName ( Child::ChildData2 )
  )
) & ";¶" & 

"$CR_Data =  " & 
Quote (
  List ( 
    Parent::id ;
    "SomeData" ;
    Get (CurrentDate) 
  )
)

The first line of the parameter establishes the layout name. When the script parses the script parameter, it will create a local variable $CR_LayoutName and set it to the value specified. The script will navigate to this layout and create the new record in the context of that layout.

"$CR_LayoutName = " & Quote("DEV_Child")  & ";¶" &

The next variable in the parameter, $CR_TargetFields, is a list of the fields in the new record that will be set by the script. Note that the list is wrapped in quotes by the Quote() function and each field name is wrapped by the GetFieldName () function. Use of GetFieldName () is critical to ensuring that changes to field names do not break the script parameter. In this example, the script will create the record and set the three fields in $CR_TargetFields list to the values specified in $CR_Data.

"$CR_TargetFields= " & 
Quote ( 
  List ( 
    GetFieldName ( Child::id_Parent ) ;
    GetFieldName ( Child::ChildData1 ) ;
    GetFieldName ( Child::ChildData2 )
  )
)

The final variable in the parameter, $CR_Data, is a list of data values. In this case, the three fields will each be set to the data value that occupies the same place in the list. Child::id_Parent will be set to the value of Parent::id, Child::ChildDate1 will be set to “SomeData” and Child::ChildData2 will be set to today’s date:

"$CR_Data =  " & 
Quote ( 
  List ( 
    Parent::id ;
    "SomeData" ;
    Get (CurrentDate)
  )
)

There are a few tricks to keep in mind regarding the $CR_Data variable. First, the data values can be any field accessible from the original context of the script (in this case the Parent record), or any literal or calculated value. If the source of the data value is a global input field as in the screen shot above, then the global field name should be wrapped with GetFieldName (). Doing so tells the script that source data came from a global field, and that the global field should be nullified as the script executes. Here is what that should look like in the parameter:

"$CR_Data =  " & 
Quote ( 
  List ( 
    Parent::id ;
    GetFieldName ( Globals::GlobalInput1 ) ;
    GetFieldName ( Globals::GlobalInput2 )
  )
)

The script can also handle data field values that have paragraph returns. For example, you may want to create a record and insert a list of values in a field. In this case, you need to substitute the sequence of characters “~CR~” to represent the paragraph return. The script then substitutes a real paragraph return when it encounters the string in a data value. In this example the third field will be set to a return-delimited list containing the values “FirstLine” and “SecondLine”:

"$CR_Data =  " & 
Quote ( 
  List ( 
    Parent::id ;
    "SomeData in single line" ;
    "FirstLine" & "~CR~" & "SecondLine"  )
  )
)

I should also mention that the script traps for a few errors. While most potential errors can be addressed by correctly passing the parameter of the script, I thought it would be prudent to trap for certain errors and return the error in the Exit Script[] step. In case of any trapped error, the script will exit and the new record will not be created. Errors from the following script steps are trapped:

• Go to Layout[]: Generally caused by passing an invalid layout name, or perhaps security privileges that don’t allow access to the specified layout. (This is the only part of the CreateRecord script that is not immune to renaming).
• New Record step:  An error may occur here when security privileges prevent new record creation.
• Set Field By Name: Again, an error here could be caused by security privileges or perhaps a field name that does not exist in the context of the created record.

I hope you enjoy using the CreateRecord script in your solutions.  Since the work over at www.modularfilemaker.org is open source by nature, I invite you to pick apart and make suggestions in the comments below for improving the script. Or submit a revised file to darren_burgess@mightydata.com.

The latest version of Propa-Gator.fmp12 is available at: www.modularfilemaker.org