Working with random numbers in FileMaker

When you want random numbers, you can use FileMakers function random. It is a very simple function. It does not require any parameter and returns a number between 0 and 1. Of course, often the random numbers you are looking for do not belong to this narrow interval. You have to transform the value to match your target range.

random.interval

I wrote a little custom function to accomplish this transformation. The function expects two parameters, the lower (min) and the upper value (max) of the target interval.

random.interval( _min; _max )

Random * ( _max - _min ) + _min

This is very simple. But often you are looking for discrete values, like throwing some dice.

random.discrete

The next custom function will take care of that. As you can see, I do not only floor the random value. Because of the discrete nature of the return value, I adjust the interval limit accordingly.
This call will return the random values of a single die: random.discrete( 1; 6 ).

random.discrete( _min; _max )

Floor( Random * ( _max - _min + 1 ) ) + _min

Random is not Random

Be careful, though, with random numbers. In any real life situations random numbers are not evenly distributed. More often they follow a distribution called Normal distribution or Gauss distribution. The graph of the probability density function is bell-shaped. It has a peak at the mean, and is known as the Gaussian function or Bell curve.

Unfortunately, the FileMaker function Random returns only uniformly distributed random numbers. But mathematics offers a solution. There are actual multiple ways to convert uniformly distributed numbers to normal distributed numbers.

random.repeat

One way to emulate normal distributed random numbers is to use multiple dice, or in our case, use the function Random multiple times to return one value. Using it 12 x will create a pretty good approximation – if the uniformly distributed random numbers are independent from each other. Unfortunately, this is not the case with many random numbers generated by simple computers. Often only few numbers will truly follow this rule.

Nevertheless, I like to share a custom function that you can use to test this approach. The function expects three parameter, the low (min) and high limit (max) of the interval and a number how often (repeat) uniformly random are generated for a single normal distributed value.

random.repeat( _min; _max; _repeat )

If( $random.repeat;
	Random + If( _repeat > 1; random.repeat( _min; _max; _repeat - 1 ); 0 );
// Else
	Let( [
		$random.repeat = True;
		_value = Random + If( _repeat > 1; random.repeat( _min; _max; _repeat - 1 ); 0 );
		$random.repeat = False
	];
		( _value / _repeat ) * ( 1 + _max - _min ) + _min
	)
)

random.gauss

Okay, calling function Random 12 times is not really giving us normal distributed random numbers. But there are other ways to do a proper approximation. The Polar method requires two uniformly distributed random values, but with each calculation this method will return two independent normal distributed random numbers. Put two values in, get two values out.

Okay, I have to admit, this is not always true. Sometimes, the input values are not good enough to calculate a proper approximation. In this case, new uniformly distributed random values are used.

random.gauss( _mean; _variance; _limit )

If( IsEmpty( $$random.gauss_value );
	Let( [
		_u = 2 * Random - 1;
		_v = 2 * Random - 1;
		 _s = _u ^ 2 + _v ^ 2
	];
		If( 0 < _s and _s < 1;
			Let( [
				_t = Sqrt( -2 * Ln( _s ) / _s );
				$$random.gauss_value = _u * _t;
				_value = _v * _t
			];
				If( ( -_limit ≤ _value ) and ( _value ≤ _limit );
					_value * _variance + _mean;
				// Else
					random.gauss( _mean; _variance; _limit )
				)
			);
		// Else
			random.gauss( _mean; _variance; _limit )
		)
	);
// Else
	Let( [
		_value = $$random.gauss_value;
		$$random.gauss_value = ""
	];
		If( ( -_limit ≤ _value ) and ( _value ≤ _limit );
			_value * _variance + _mean;
		// Else
			random.gauss( _mean; _variance; _limit )
		)
	)
)

The function random.gauss expects three parameters. The first value is mean, the expected value where the bell curve has its peak.

The variance (or standard deviation) is the second parameter. About 68% of values drawn from a normal distribution are within one standard deviation away from the mean. About 95% of the values are within two standard deviations and about 99.7% lie within three standard deviations.

Limit is the third parameter for the function random.gauss. It limits the number of deviations used, when returning a random value. If the number will fall outside this limit, a new value is calculated. Because 99.7% of all generated values will fall inside three standard deviations, 3 is a good value for limit.

The function random.gauss takes care of the factor, that two proper normal distributed random values are calculated. It saves the second value until the function is called again, saving some calculation time.