by John Mark Osborne, <jmo@best.com>, http://www.best.com/~jmo/
Update Serial Numbers
BONUS: UPDATE.FP3
PLATFORM: Mac/Win
You've just created the most incredible FileMaker solution ever. You sell your product to everyone who owns a computer and become rich beyond your wildest dreams. Then one day a customer reports a serious bug in your product. No problem, you just need to send your customers an update.
Where This Gets Sticky
The only problem is that there is no updater technology that can be used with FileMaker files. That means you'll need to send each customer the entire solution and have them import their data.
This brings up another problem. Some of your files rely on a serial number field. It is the match for several relationships so the serial numbers need to stay the same from the original file to the update. If you choose to perform the auto enter functions during import, then the next serial number value in Define Fields will be the total number of records you just imported plus one. If your customer has deleted any records, then this serial number will equal the total number of records plus one. If you choose not to perform the auto enter functions then the next serial number value will be one. Both approaches could potentially cause duplicate serial numbers which in turn will cause problems with relationships.
Updating the next serial number value in Define Fields needs to be accomplished with a method that is transparent to your customers. Actually, what really needs to happen is for Claris to add a script command that allows you to set the next serial number value to the contents of a field. Until then, the following technique should suffice for small to medium size files.
How It's Accomplished
The basic idea behind this solution is to use the Replace feature to update the next serial number value. The Replace feature has an option for updating the serial number in entry options. When this choice is selected, the next serial number value is automatically updated as the Replace is done. By placing the Replace command in a looping script, it is possible to have it run enough times to update the next value in Define Fields to the maximum serial number imported plus one.
The first step is to create a self-join relationship. The match field in this relationship will be a calculation field named Constant with a formula of "1". This enables every record to be related to every other record in the database. The purpose of this relationship is to determine the maximum serial number. The exact formula will be shown later in this article.
The Update Serial Number technique is divided into several sub-scripts which are controlled by the following master script:
Freeze Window
Perform Script [Sub-scripts, "Start Time"]
Perform Script [Sub-scripts, "Check Current Serial Number"]
Perform Script [Sub-scripts, "Set Counter"]
Perform Script [Sub-scripts, "Loop"]
Delete Record/Request [No dialog]
Find All
Perform Script [Sub-scripts, "End Time"]
The purpose of the Freeze Window script command is to speed up the looping process of the script by suppressing the screen from drawing all of the action. It's surprising how much faster solutions of this nature perform without having to draw every little thing as many times as the loop runs through.
The first script that is called sets a global time field to the current time. This script along with the End Time script records the elapsed time. The elapsed time is important when deciding whether this solution will work for you or not. That's because the biggest issue with this technique is speed. It works well for small to medium size databases but may not be acceptable for larger databases.
The next script, Check Current Serial Number, determines whether the next serial number value is 1 or not. You won't need this test when you implement the solution in your own files. The purpose it serves in the bonus file is to make sure the database is setup properly in order to simulate the update process your customer would go through. The bonus file included with this article doesn't go through the importing stage of an update so you need to simulate it as if an import was just performed. That means resetting the next value for the serial number to one. If this is not done then the demo file will warn you.
New Record/Request
If ["Serial Number = 1"]
Find All
Omit
Find Omitted
Else
Delete Record/Request [No dialog]
Perform Script [Sub-scripts, "End Time"]
Show Message ["Warning Message"]
Halt Script
End If
The Set Counter and Loop scripts are the keys to this solution. The Set Counter script sets the Counter field to the maximum serial number that was imported. The Counter field determines how many times the loop script should cycle.
Set Field ["Counter", "Max(Update Serial Number::Serial Number)"]
Once the Set Counter script runs, the Loop script performs the same set of commands as many times as specified by the Counter field. The script command that performs the task of updating the next serial number is Replace. The Replace command runs on a single record as many times as the Counter field specifies. For instance, if the largest serial number is 56 then the loop will run 56 times. Since only one record is being replaced, the next value in Define Fields is incremented by one each time the loop cycles.
Loop
Replace [No dialog, "Serial Number", "Serial numbers"]
Set Field ["Counter", "Counter - 1"]
Exit Loop If ["Counter = 1"]
End Loop
Alternative Methods and Considerations
One thought that went through my head when creating this solution is whether there was a better method for accomplishing the same task. One technique that I tried was using the New Record/Request script command instead of Replace. Each time a new record is created, the next value in Define Fields is incremented. The speed was about the same as using Replace. The reason it is not demonstrated in the bonus file is because it leaves unused blocks in your database that need to be removed when the database is closed.
An alternative technique that did make it into the bonus file determines how many records there are and then divides that number by 100. Instead of running the loop on 1 record, the loop is run on 100 records at a time. This can speed up the process by as much as 3 to 5 times but it will only work with larger databases. See the bonus file for details.
You might be thinking that the new FileMaker Pro 4.0 Status(CurrentRecordID) could help. Unfortunately, record IDs are based on creation order and cannot be transferred from one database to another.
Concluding Thoughts
If your customers have many records in their databases then you might consider the following changes to this solution. With the previous methods, the auto enter feature was not used during import. This makes it necessary to run the looping script as many times as the maximum serial number. If the auto enter option is selected during import then the next value for the serial number will be the same as the number of records you just imported. This number will be less than the maximum serial number if some records have been deleted. All you need to do is create a new record and subtract the serial number from that record from the maximum serial number in the database. This will give you a value that you can use in a loop. This value should be very small depending on how many records have been deleted. Thus, the looping Replace script should run much faster.
### END ###