The Replace Field Contents Command - Why It Is So Cool

The Replace Field Contents command/script step is a very powerful tool for updating field values across records in your found set.

The above version of the dialog, is the one that you will see when using the script step. The dialog appears slightly different when running it via the command from the Records menu. There are three possible options you can use:

Replace with Current Contents

This refers to whatever the value is in the field you are replacing on the current record you are on.

This is quite useful, most particularly when you are running the replace manually using the command. Imagine you are on a layout and wish to set "Hello" into records in your found set. The easiest way, is to put "Hello" into the field on your current record, then run the command.

You can see the dialog will show you what records in the found set will be set to. There is another way to get the word "Hello" into the field using a calculation, but this is the fastest way when using the menu command.

Some things to note:

• The # of records that will be updated is displayed in the first sentence.
• The command works on a found set of records, if you have no found set, the command is unavailable.
• When using the command, it is required your cursor be in the field you wish to update. For the script step option, this is not required, because the script step allows you to specify the field being replaced.

Replace with Serial Numbers

Use this option to set sequential numbers into each record in your found set. The first record in the found set is assigned the initial value. Note that it does not matter what record of the found set you are on initially, numbers will always begin from the first record.

If the field you are updating has the auto-enter serial number option enabled, you are given the ability to update the next serial number for that.

Some things to note:

• Both the next serial number, AND the increment value will be updated within the fields auto-entry option, providing the update option is checked.
• If checked, the next serial value is value of last record in the found set serial number, plus the new increment value set in the command you just run.

Replace with Calculated Result

Perhaps the most powerful option,this allows you to define a calculation which will then be applied to each record in the found set. The calculation you define is evaluated independently for each record.

For example, take a table with 2 fields - A and B. The table contains 3 records. Lets say now that on our found set of all records, we wish to replace the contents of field A. The calculation used is "B + 10":

As you can see, the calculation is applied to each record individually.

Some things to note:

• Referencing related values within the calculation works on a per-record basis.
• As mentioned in the "current contents" section - the other way to put the word "Hello" into the field on each record, would be to make the calculation be the string "Hello".

After the replace, a cautionary table about record locking.

If the replace runs successfully, you will see nothing afterwards - the fact that you see no dialog means it all worked. However, if something went wrong, you may see the following kind of message:

This dialog - as informative as it tries to be - only tells you that one or more of the records in your found set, were unable to be updated. There are two reasons why this can happen. The first is that another user of the database is modifying one of the records in your found set. Because of this, they have the lock on that record, and so you are unable to update the record.

Important Note about Errors

FileMaker unfortunately does not tell you which record(s) failed. This is a shame, especially if your replace is run using the script step. If you are updating hundreds of records with important data, then having some fail could cause significant issues later on. Not knowing which ones failed means you either have to re-run the replace at a later time, or bite the bullet and hope nothing bad crops up :) In some cases, the replace field contents step might not be the best method of record updating, and you may instead opt for a looping script, that tries to obtain record lock on each record before attempting to update the field contents.

Even more perilous, is running the replace field contents script step, with the "perform without dialog" option checked. While this will suppress the initial parameters dialog, it will NOT suppress the error dialog if any records were not be updated. To suppress this dialog, you must prefix the replace field contents step, with the Set Error Capture step.

If you suppress the error dialog, you may still need to check for errors in your script. You can do this by evaluating the Get(LastError) function. If the replace failed to update record(s), the error code is 201.

Replacing Related Field Contents

The Replace Field Contents step is perhaps more used, and easier to understand, when it is used on a field which is on the found set table. What you may not know, is that the command can work on related fields as well.

If running the replace via command, the rule is the same - place your mouse cursor in a related field on the layout, and run the command.

The replace will update EVERY related record of EVERY record in the found set!

This concept is critical, you may end up replacing more related records than you had anticipated. Often, you may wish to only update all related records of a single record. To do this, you must make the found set your current record only. You can do this by using the following three commands/script steps in order:

1. Show All Records
2. Omit Record
3. Show Omitted Only

The power of the replace field contents step to update related record values - as well as records in the found set - is very useful and can save time in scripting, but it must be used with great care because it suffers from the same record updating issues as previously mentioned - if not more so !

With all it's potential pitfalls, it can greatly reduce the number of script steps you need to use to achieve certain tasks.

As a final example, consider a parent table "Customer" , and child table "Contacts". For various reasons, the customer name is needed to be stored on each of the Contact records. The name can intially be set onto the child records using an auto-enter calculation on the contacts "customer name" field. But what if the customers name changes after the contact record is created? The auto-enter calculation will not pick up the change. Instead, the change must be forcibly updated on each of the child contact records.

Assuming you are currently on the customer record, you can achieve this many ways, here are 4:

#1: A long way, but potentially safer way

Set Variable [ $CustomerName ; Customers::Name ]
Set Variable [ $CustomerID ; Customers::Customer ID ]
Go to Layout [ Contacts ]
Enter Find Mode []
Set Field [ Contacts::CustomerID ; $CustomerID ]
Set Error Capture [ ON ]
Perform Find
Set Error Capture [ OFF ]
Loop
Set Field [ Contacts::Customer Name ; $CustomerName ]
Go to Record/Request/Page [ Next ; Exit After Last ]
End Loop
Go to Layout [ Original Layout ]

#2: Another long way

Set Variable [ $CustomerName ; Customers::Name ]
Set Variable [ $CustomerID ; Customers::Customer ID ]
Go to Layout [ Contacts ]
Enter Find Mode []
Set Field [ Contacts::CustomerID ; $CustomerID ]
Set Error Capture [ ON ]
Perform Find
Set Error Capture [ OFF ]
Replace Field Contents [ No Dialog ; Customer Name ; $CustomerName ]
Go to Layout [ original layout ]

#3: A shorter way (but essentially the same way)

Set Variable [ $CustomerName ; Customers::Name ]
If [ not isEmpty ( Contacts::Contact ID ) ]
  Go to Related Records [ Contacts ; using layout "Contacts" ]
  Replace Field Contents [ No Dialog ; Customer Name ; $CustomerName ]
  Go to Layout [ Original Layout ]
End If

#4: Short, sweet, possibly nasty (setting thru relationship)

Replace Field Contents [ No Dialog ; Contacts::Customer Name ; Customer Name ]

Final Thoughts:

Hopefully this article has shone some light on the replace field contents step, and what can and cannot be achieved with using it. While it is a very powerful step, with that comes a responsibility to ensure that it is used correctly and without error.

Looking at the various methods in the code above, #4 is definitely the method that I use the most, even though I have rated it as one of the most dangerous. In fact, it is no more or less dangerous than methods 2,3 and 4 - given that all of these steps use the replace step. So if you are going to use any of those 3 methods, you may as well go with the easiest one to write !

I rated method 1 as the safest. It is not shown in the code, but that method - of doing each record update explicitly - allows you the ability to check for record locking before doing your field update. This may be considered overkill for most simple solutions, but depending on how critical it is that records are updated with the correct field values, this may be the safest approach.

Be careful out there !