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:
- Related records can be created such as log records, phone numbers and attributes.
- 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.
- 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 email@example.com.
The latest version of Propa-Gator.fmp12 is available at: www.modularfilemaker.org
Josh Ormond says
Thanks for posting that file. I was actually in the process of working through a similar script when I saw that post on modularfilemaker.org. I almost feel like sending you a check for the hours you saved me. LOL
I’m glad you posted a detailed run through here to. It’s always nice to see what the developer was envisioning when they release a tool like this. There are always things that you know it does that others may not realize…and, of course, the inverse is true also.
Darren Burgess says
Make check payable to: MightyData, LLC
We do value based pricing – in this case you get to determine value received and set a price accordingly 😉
That said, really glad you will use the module. Let me know if you have any ideas for improvements. It is open source, after all.
FWIW, I designed the script to do just a very limited thing – create a record, set some fields. You may increase its functionality by using wrapper scripts that process error traps or use conditional branches to implement business rules or UI functionality.
Dale Long says
I think I might have found a bug. Perhaps I’m using it incorrectly?
Here’s the situation. I’m trying to use the script for a single find. Probably not necessary for a single find, but I’m just trying to get used to the syntax of the parameter, since this looks like a tremendously useful script with which to be familiar.
First, I found I needed to do away with the List function, as it doesn’t like dealing with a single value. Not a big deal.
Second, I have a number stored in a global. I’m trying to do a find for greater than this number. Noticing the part about using the GetFieldName ( global ) function in the $CR_Data field to clear the global, I decided the best way to phrase the parameter was:
Quote ( “>” & GetFieldName ( Globals::CLIPBOARD ) )
This however, returns “>Globals::CLIPBOARD”
In order to actually get the value held in the global, I have to use
Quote ( “>” & Globals::CLIPBOARD )
Which means I have to laboriously add an extra script step to clear the global (lol).
So, am I doing it wrong, or did I uncover a bug?
By the way, this thing is simply awesome, I have recently been trying to build something similar when I came across ModularFileMaker and found this wonderful script. I will be making ample use of this technique moving forward. Thanks so much for your contribution to the FM community!
Darren Burgess says
First off, I am really glad you are getting value from the script. Thanks for letting me know.
You are right, eliminate the list() function if you are passing just one value. This is documented in the script comments of the CreateRecord script.
What you have uncovered is not really a bug, but a limitation in the technique (LOL). What I would do in your case is null the global after you run the Create Record script. (as you suggest).
I realize that this may be a tremendously huge amount of work. Sorry. 😉
Perhaps I could change the condition that checks for and nulls the global field. Let me think about that and I will post the change here and over at http://www.modularfilemaker.org.
Note that I wanted the script to work such that I would never need to change the structure of parameters that are passed to it (which would involve changing multiple parameters in an installed solution if you wanted to upgrade the script.) So the script can be modified as long as it never changes how the parameters are passed.