Auto-Enter Gotchas!
By Ilyse Kazar (kazar@datatude.net)
BONUS FILE: CHARTING.FP3, SCATTER.FP3
PLATFORM: Macintosh & Windows
This article demonstrates some of the problems that can occur with the auto-enter feature, as well as workarounds, when duplicating records.
You are working very late to catch up on some tasks, and are entering student attendance into your FileMaker database. The clock passes midnight and, in the next Attendance record you create, the auto-entered date rolls over to the new day. You notice this and change it to yesterday's date. In addition, the class you are entering was canceled, so you change the auto-entered data "Present" to "Canceled."
You are down to entering the last 19 students, all of whom are in the same class as the record you just entered. You have been working for many hours, so to save time and get out of there already you start duplicating records rather than using the New Record command. You plow right through the remaining records, choosing Duplicate Record and simply changing the student's name on each one.
Just when you think your work is finally done and you are about to close the database, you glance at the last record you entered and realize that the date has changed back to today's date, and that the student is marked "Present." Browsing back through those last 20 records, you see that all but the first record created after midnight, which you manually changed, still has yesterday's date and the attendance marked as "Canceled."
What happened? You've been caught by an "Auto-Enter Gotcha"!
What's a Gotcha? It's not a bug. It is actually a "feature" -- the way a particular command in FileMaker will behave, sometimes with unexpected results, under certain circumstances. (And, to spare any of our international readers from making an unfruitful trip to the dictionary: "Gotcha!" is what the cowboy shouts when he finally ropes the bull, as in, "I've got you!")
Auto-Enter Options and the Duplicate Record Command
Depending on the type of Auto-Enter option selected in a field's definition, FileMaker will handle duplication of user-modified values differently. Most of the auto-enter options will take precedence over user modifications when a record is duplicated.
The only Auto-Enter options that will not override a user modified value are, surprisingly, "Calculated value" and "Looked-up value." One would think that a calculated value would always be calculated, and that a looked-up value would always be looked up when a new record is created. But, if a user modifies values that are auto-entered by lookup or by calculation, the Duplicate Record command will not alter the user's modification.
Therefore, if a file you create has any auto-entered options defined other than "by calculation" or "by lookup," how do you prevent user-modified values from being overridden when a record is duplicated?
The answer is to set the "Available menu commands" in the users' password privileges to "Edit Only" or "None", and to provide a scripted button that controls how records are duplicated. (Of course, once you enter this restricted-menu territory, it will be up to you to provide scripted buttons for any needed menu command, not just Duplicate Record.)
Using the above scenario, let's say that the file has the following fields defined:
Field Name Type Options
Date Date Creation Date
Class Text
Student Name Text
Attendance Text Auto-Enter Data,
"Present"
As noted above, if the Date and Attendance values are modified by the user, the fields will revert to the auto-enter options upon record duplication. Therefore, to truly duplicate a record, you will need to store the values from these fields in globals, and populate the fields on a new record using the global values.
So, you will need to define a global field of the same type as each auto-entered field whose user-modified value you wish to duplicate. In this example, the prefix "g" in the field name indicates a global field:
Additional fields:
Field Name Type Options
gDate Global Date
gAttendance Global Text
Now it is time to write the "Duplicate" script. You might try writing your script like this:
Allow User Abort [Off]
Enter Browse Mode []
Set Field [gDate, Date]
Set Field [gAttendance, Attendance]
Duplicate Record/Request
Set Field [Date, gDate]
Set Field [Attendance, gAttendance]
Well, this script would work, but another "gotcha" just gotcha! If the auto-entered "Present" in Attendance had been user-modified to "Absent," you have successfully duplicated this value. But what happened to that nice 4-digit year in the original Date field?
If the Date had been "2/1/1999" the Set Field step just removed the century, and your duplicated record now has "2/1/99" in the Date field. Worse yet, if the date in the original record were "2/1/1906," the Set Field step would change it to "2/1/06" which FileMaker would then interpret as "2/1/2006" if the date were edited at all.
To copy a date safely from one field to another without losing the century, you can use the Paste Result script step. (Remember that with any "Paste" script step the target field for the paste must be on the current layout.) Rather than using Set Field to transfer the value of Date into gDate, and then from gDate into Date in the duplicated record, you would include the gDate field somewhere on the layout (you can "hide" it by formatting it as a 1-pixel-wide field) and write the script like this:
Allow User Abort [Off]
Enter Browse Mode []
Paste Result [Select, gDate,
Month(Date) &"/" & Day(Date) &"/" &
Year(Date)]
Set Field [gAttendance, Attendance]
Duplicate Record/Request
Paste Result [Select, Date,
Month(gDate) &"/" & Day(gDate) &"/" &
Year(gDate)]
Set Field [Attendance, gAttendance]
The "Year" function in FileMaker Pro will return a 4-digit year, so this Paste Result formula will, unlike the Set Field step, return a good result into your Date field. (A simple Copy and Paste, rather than Paste Result formula, would also retain the 4-digit year, but this would needlessly wipe out any data the user might be storing on the clipboard.)
For a demo of these issues, see the
"AUTOENTR.FP3" Bonus file and click on the Demo button.
Make Your Job Easier With Field-Naming Conventions
In another scenario, let's say you are developing an order-entry system for a Custom Book Bag manufacturer. There 47 fields for the various specifications of a book bag, and the specs will be printed out on a Work Order and sent to the manufacturing floor.
Suppose that for 18 of these specification fields (e.g., "Clasp Type", "Strap Length" and 16 other fields) the attributes will normally be a particular value. So for these fields you define auto-enter "Data" options and enter "Buckle" for Clasp Type, "30" inches for Strap Length, etc. If the Clasp Type is something other than "Buckle", or if the Strap Length is longer or shorter than "30" inches, the user will modify the auto-entered value. So far so good.
But book bags are sometimes returned by customers requesting that adjustments be made. When the Work Order for this adjustment is printed, you wish it to show only those attributes that need changing. So you will need to write an "Adjustment Order" script that creates a new record and then removes the auto-entered values normally entered for a new Order.
How will you remember, when writing this "Adjustment Order" script, which 18 of the 47 book bag attributes are auto-entered and need to be cleared? You will have a much easier time if you include some indication of auto-enter options in the field names. For example, let's say you use the suffix ".ad" to indicate "auto-entered data". Then in ScriptMaker's Specify Field dialog, you will see a list of field names something like:
Current File
OrderID
CustomerID
EntryDate
ShippingDate
Width
Height
Depth
ClaspType.ad
StrapLength.ad
Color
PenPockets
(etc.)
Because the field names indicate which fields auto-enter a value, you can tell at a glance which fields you need to clear using a Clear [FieldName] or Set Field [FieldName, ""] step.
And, thinking back to the Duplicate Record auto-enter "gotcha," if you indicate the various types of auto-enter options using different suffixes such as ".ac" for auto-enter by calculation, ".ad" for auto-entered data, ".al" for auto-entered by lookup, etc., your scripting will be easier and your programming perhaps more solid.
More Gotchas Coming Soon...
Look for a discussion of working with Auto-Entered fields when Importing Records and Web Enabling a File, in the next issue of ISO Magazine.
## END ##