Level: Intermediate, Version: FM 12 or later

Generating Sample Data

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.

12-31-2013 2-24-17 PM

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

11-30-2013 12-14-50 PM

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)

11-30-2013 4-19-20 PM

So now we’re ready to think about the original challenge, and we have a demo file, generating-sample-data

11-30-2013 3-57-48 PM

…which contains a primitive invoicing system, architected thus…

11-30-2013 4-01-47 PM

…and a popup window to allow you to enter your range of parameters like so:

11-30-2013 4-12-54 PM

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.