Level: Any, Version: FM 8 or later

Logarithms I Have Known And Loved

They say you never forget your first time, especially if it’s your only time; maybe that’s why my first (and, so far, only) logarithm stands out so vividly in my mind. It was a quiet Thursday in October when the call came. A colleague was building a cat breeding database and wanted advice on how to solve a problem.

If a given cat is assigned “position 1” in his or her family tree, the cat’s ancestors can be assigned tree positions like so:


Additionally, each generation can be numbered as follows:

What my colleague wanted was a method to translate a given tree position into its corresponding generation number. So why not simply write a Case statement like this?

tree_position < 2 ; 0 ;
tree_position < 4 ; 1 ;
tree_position < 8 ; 2 ;
tree_position < 16 ; 3 ;
tree_position < 32 ; 4 ;

Well, in this instance it would probably be fine, provided you knew the maximum generation number you would ever care about. Just write a long enough Case statement and get on with your life.

But a solution like this leaves a bad taste in a FileMaker developer’s mouth for two reasons:

a) No matter how many generations you code for in the Case statement, at some point you may need to accommodate a higher number than the previously defined maximum. (In other words, this approach doesn’t scale well.) Of course, as long as you recognize this at the time, you can revise your code accordingly… but you don’t always have that luxury.

b) The above approach is not elegant. Long-winded, monotonous code is sometimes a necessary evil, of course, but one that should be avoided whenever possible.

At any rate, my colleague was looking for a simple formula that would yield the correct generation number for any tree position. Clearly powers of 2 would be involved, as indicated by the tree position values in the Case statement, which form a doubling sequence that runs 2, 4, 8, 16, etc. Expressed as powers of 2, these values can be written 2^1, 2^2, 2^3, 2^4, etc.

But could this information help solve the problem? We had some tree position values corresponding to base 2 exponents of generation numbers. What we needed was some sort of “reverse exponent” to convert tree position values into generation numbers. Fortunately such a thing exists, and it has a name: logarithm.

In case you need a brief refresher on logarithms, they “undo” exponents in much the same way that division undoes multiplication. Exponents are written like this:

Logarithms are expressed this way:

In plain English, y represents the power we need to raise some number to if we want to produce x. So if our base is 2, and we use 8 for our x value, then y is 3 (since 2^3 = 8).

In our case, since we were dealing with powers of 2, a base 2 logarithm seemed to be the right tool, and in FileMaker base 2 logs are calculated using the Lg() function. So we plugged in some tree positions as x values and this was the result:

Clearly we were not only on the right track, but within spitting distance of the finish line. All that remained was to come up with a way to discard the decimal portion of the result, and we decided to use the Floor() function for that, though we could have used Int() or Truncate() instead.

Here’s our final formula:

Floor ( Lg ( tree_position ) )

…and here’s how it looks in the data viewer:

Leave a Reply

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