Sequence Generator

Intro

Sequence Generator is a portable module that you can easily integrate into a FileMaker solution. It provides an alternative to Auto-Enter Serial numbers. It’s open source, so you can explore it’s inner workings and customize to suit your own specific needs. We’re looking forward to hearing how you use it.

Download

You can download the latest file here: sequence-generator-current.zip Last updated November 12, 2015

How Does It Work?

There is a table of sequences. Each record describes a specific sequence definition. Any time you need a new number from a sequence, you perform a script, passing it the name of the sequence you wish to access. In addition to the basic capabilities of a serial number, it also supports a number of more advanced features. The script and schema are carefully constructed with rigorous error checking so that we can guarantee reliable operation, even under a heavy load. Even with multiple clients simultaneously requesting numbers from the same generator, their results will always be unique and in order. The standard generator script includes automatic record collision retry, so that clients can confidently get a sequence number, even from a busy generator. An alternate basic script runs very fast, but with reduced features and no automatic retry.

Requirements

The easiest way to deploy the solution is to add the entire file to your server or solution bundle. Script results are currently encoded as a Property List, so you need a couple custom functions to conveniently decode the script results. If you use a different preferred NVP serialization, maybe just adjust script results to match your flavor. (Additional details are included in a Read Me script.) For multi-user installs, files must be hosted on FileMaker 13 or later, or on FileMaker Server 13 or later. The design of the solution depends on successful database isolation, which has proven to be buggy in FileMaker 12. Tested clients include FileMaker Pro Advanced 13 and 14, FileMaker Go 14, and PSoS 14. Tested hosts include FileMaker Pro Advanced 13 and 14, FileMaker Server 13 and 14.

Why Use This?

Auto-Enter Serial numbers are built-in and easy to use. Why would I want to use a sequence generator?

• Live Development – traditional serial numbers lock schema when defining field options
• UUID Companion – if you adopt UUIDs for primary keys, you still occasionally need human readable sequence numbers
• Syncing – tools to help support any sort of off-line or multi-site application
• Complex Sequences – variable or rotating prefix or suffix components, etc.
• Many-to-one – decoupling sequence streams from tables

Live Development

FileMaker affords some fantastic capabilities for rapid and even live development. However, certain operations defining database schema can cause locks. The most common and avoidable problem has to do with serial numbers. Once a table level lock is acquired, no serial numbers can be issued until the table definition is saved. This is perfectly sensible, because otherwise you could find yourself with overlapping serial numbers for different records. Using UUIDs is a great way to avoid this lock. When a table has no serial numbers, you can be much more aggressive with live development.

UUID Companion

While computers seem to be keen on long hexadecimal strings, end users typically are not. Almost every database solution needs to present some sort of human readable record identifiers: Customer Account#, Invoice #, Asset Control #, etc. Even though using a sequence generator script is a little slower than using schema based serial numbers, you probably don’t need them on most of your tables. Many tables work just fine with only a machine readable UUID. When a table has both a UUID based primary key and a human readable field, the recommendation is that relationships and other programming functions would almost always utilize the UUID based primary key.

Syncing

Serial numbers can be a real chore when synchronizing and merging data from multiple sites. Again UUIDs making syncing much more sane, but they cause problems for humans. It is very reasonable to assign distinct pools of numbers to each location or off-line device. Uniqueness might come from a prefix, or it could come from isolated numeric ranges. “Commissioning” is a good term to explain the process of assigning these unique ranges to client nodes. While this project doesn’t currently have commissioning built-in, it could be a good place to start or to borrow ideas.

Complex Sequences

Even though sequence generation is typically a simple counting machine, there are times when it is nice to inject other types of behavior. For example, a firm might like to label all their credit memos with a sequence defined as “CMyy-nnnnn”. The yy part is the last two digits of the current year, and the nnnnn is a padded 5 digit counter. Automatically rotating the year part helps humans to quickly and intuitively age the record. The provided implementation looks for and substitutes a 2 digit year for %y, so you would define your prefix as “CM%y-“.

Many to One

Most typical tables, if they need a human readable identification number, only need one type of number. But there might be situations where you want to have a choice of two or more sequences inside the same table, such as an Accounts Receivable table that stores both Sales Invoices and Credit Memos. Similarly, the same sequence could be shared among multiple tables. For example, there might be business reason to partition West Division and East Division activities into two tables that feed from a common Publication ID generator.

Testing and Performance

Various sequence generators have been exercised in a testing lab, with over 1 million script calls. Early editions of this solution have also been deployed successfully in production. We have never detected an uncaught error or duplicate sequence number (*except files shared from FM12). That said, depending on your conditions, collisions while taking a number can be quite common. The default retry timeout is 4 seconds. Depending on what’s acceptable in your environment, you should adjust this timeout and also be prepared to handle an actual timeout error code when they are reported. The script attempts to get in and out of a sequence record as quickly as possible. During that focused time period, the client holds a sequence record open, and all the other clients must wait and retry. In testing, we have observed that a single client who is on a high latency connection, takes significantly longer to get out of a record once they have opened it. This makes sense, and is a factor related to the round trip time of packets over the WAN. Meanwhile, other clients that might be adjacent to the server on a low latency connection, may experience slowdowns because the first client gets in their way. This would be a good example of when timeout adjustments are necessary. Thanks to the reliability characteristics of TCP, the sequence generator seems to remain durable, even in harsh environments. During one test, two clients were tasked with requesting 10,000 numbers each. One client was placed on a weak WiFi network that was experiencing 3.5% packet loss. While performance did slow and stutter, the sequence numbers issued were still reliable. Only in situations where the files periodically close from a dropped connection should you have a problem. Even then, the sequence generator should remain in a consistent state because all database operations are transactional. A few performance results for a FMS hosted sequence generator:

• 137/s – single localhost client running a sequence
• 114/s – single 1000T client running a sequence
• 114/s – single 100T client running a sequence
• 47/s – single 802.11n client running a sequence
• 42/s – single 802.11n GO client (iPhone 6) running a sequence
• 162/s – two wired clients, both running same sequence with retry, combined performance
• 74/s – three clients running same sequence, two wired + iPhone, combined performance

Multi-client and Perform Script on Server testing:

• Testing with 4 clients all running same sequence via PSoS, no problems detected
• Testing with 8 clients, a mix running local and PSoS, two distinct sequences, no problems detected
• In all, over 416,000 PSoS tasks serviced sequentially with no problems detected

A few notes regarding Perform Script on Server:

A LAN client running a loop that repeatedly runs a PSoS step to hit the server for a sequence number is slower than that same client running the that same script locally, communicating with the server only as it relates to record access. This is because the script’s record access is very simple and there is very low network latency. Compared to PSoS, which has to ramp up various resources to handle a script call. This performance balance would almost certainly tip in places where you have clients on high latency connections or if you setup a loop to retrieve/assign multiple sequence numbers with a single PSoS invocation. As far as scaling and performance with PSoS on FMS 14, the outlook is very good! When multiple clients are all making simultaneous PSoS calls, you can watch fmsased’s thread count and CPU core utilization increase. This is good! It means that you can purchase a machine with more cores when expecting high PSoS concurrency. Also notable, each PSoS invocation creates 1 or more temp/cache files (probably 1 per database opened). This is consistent with how desktop clients maintain a record cache. Even though this project only deals with small data, the number of disk i/o operations does add up. I’d highly recommend using high speed solid state storage for your temporary files (typically your server’s boot volume).

People

Contributors

• Perren Smith – developed initial sequence generator and performed isolation testing
• Chris Irvine – retry mechanism, substitutions, and performance analysis

Special Thanks

• Marc Berning – testing and feedback

About the Author

Chris Irvine is a Senior Technology Consultant at Threeprong.com LLC, certified in FileMaker 12 and 13. Threeprong provides process efficiency consulting, custom development on multiple platforms, FileMaker performance scaling, and IT solutions for businesses across many industries.