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, assuming an invoicing solution with existing tables of customers and products…
- choose 20 customers 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
If we do a Replace Field Contents on the date field in a found set of records using the above expression, every record in the found set will end up with a date meeting the stated criteria. (To be clear, the expression will evaluate once per record… they’re not all going to be set to 1/13/2018.)
Here’s a slightly more ambitious use of the technique, to create sample data (in a demo file published here earlier this year: Fast Grid Displays on a WAN)
So now we’re ready to think about the original challenge, and we have a demo file, generating-sample-data…
…which contains a primitive invoicing system, architected thus…
…and a popup window to allow you to enter your range of parameters like so:
When the proceed button is clicked…
- the entries are subjected to a number of validation tests; assuming the tests are passed…
- a variable list, $listCusID, is populated with 20 customer ids, chosen at random (we know there will be exactly 20 because that number was specified as both the “from” and the “to” parameter)
- a variable list, $listPrdID, is populated with IDs for products having a price between $30 and $50
- next we “walk” (loop through) $listCusID, creating between 4 and 8 invoices for each customer
- as the invoices are created, their IDs are built up into a variable list, $listInvID
- finally, we walk $listInvID, creating between 5 and 25 line items per invoice
- choosing products at random from $listPrdID
- and setting the line item quantity randomly to a number between 1 and 24
At any rate, this CF has quickly become an essential part of my FileMaker tool kit. Perhaps it will become part of yours as well.