Print PDF

Have you ever wanted to generate sample data for one of your solutions that was random in certain ways, but within a defined range or scope? For example, from an existing table of customers…

• choose 20 at random
• create between 4 and 8 invoices for each of them
• dated anywhere between 1 September and 31 December 2013
• choose products at random
• from the subset of products where price is between $30 and $50
• create between 5 and 25 line items per invoice
• with a quantity for each line item between 1 and 24

Wouldn’t it be nice to wave a magic wand to solve challenges like this? Well, I have some good news: Jesse Antunes and Geoff Coffey made that magic wand available, in the form of a custom function, back in 2007 on the SixFriedRice blog (Creating Random Numbers in FileMaker)… but I confess that I didn’t really grasp how useful it could be till Beverly Voth mentioned it in her article on Aggregates in Filtered Portals.

So here we have a custom function that produces random values within a specified range.Talk about brilliant simplicity. We’ll get to the above challenge in just a sec, but first let’s use the CF to solve a simpler task:

• generate dates where the month is between January and March
• the day is between the 10th and the 20th
• and the year is between 2014 and 2020