by John Mark Osborne, <jmo@best.com> http://www.filemakerpros.com/
Repeating Fields
Repeating Fields are Not Dead
BONUS: DOLLAR.FP3
PLATFORM: Mac/Win
I have always been dismayed at the convoluted calculation required for converting numbers to text (e.g. $456 --> Four hundred Fifty Six Dollars). No matter how you write it, the formula is pages long and involves tons of nested conditionals. Please... someone write a plug-in and end this misery. For now, the calculation in this article will help soften the blow. While it is still fairly complicated, it does remove most of the Case or If statements found in most solutions, which can slow down calculations. It also uses repeating fields to store the text values for numbers, which shrinks the length significantly.
Repeating Fields to the Rescue
This brings me to the emphasis of this article. Even if you don't use this calculation in a solution, it does demonstrate innovative uses of repeating fields. Ever since FileMaker Pro 3.0 was released, users have wondered why repeating fields are still around. The most obvious reason is to avoid problems when converting FileMaker Pro 2.x and earlier solutions. Yes, some people are still using FileMaker Pro 2.x. Less obvious are the great uses for repeating fields that are still around. Before or after reading this article, I would also recommend reviewing issue 18 of the magazine for an article on using repeating fields to perform AND searches on portals. There is also a bonus files included with this article, from Scriptology "FileMaker Pro Demystified," that covers how to create progress bars using repeating fields.
That's Scriptology, not Mythology. ISO has finally completed this long anticipated book and CD ROM. It just got shipped off to the printer and should be ready for shipment a few weeks after this article is released. If you are one of the patient people who have already ordered Scriptology, thanks for your support. This innovative book and CD ROM combination is on its way. For anyone who hasn't ordered, you can find more information at the Scriptology web site, http://www.scriptology.com/
In a nutshell, Scriptology takes anyone who is familiar with FileMaker Pro to the next level. That includes beginners who have read the FileMaker manual, intermediate and advanced users. Everyone can get something out of this book and CD ROM combination. There are over 200 technique and 12 reference files on the CD ROM, including an unlocked contact manager and invoicing system. Technique files are FileMaker Pro files that cover scripting, calculation and relationship solutions that help make your solutions really sing. Reference files are FileMaker Pro files that have frequently accessed data like script steps, calculations, error numbers, etc. Reference files are much more that just references. For instance, the calculation reference file includes the ability to test functions to determine how they work, rather than creating a temporary database to test the function manually.
Anyhow, I am digressing from the focus of the article but, as you can tell, I am very excited about this product. It took a year and a half to create. It is the most complete book on FileMaker Pro ever written.
An Old Friend
The point of this article is to teach you how to use an old friend in new ways, and not necessarily how to convert numbers to text. As you are reading this article consider how you might use repeating fields in your solutions to reduce the number of fields cluttering Define Fields or create a feature that is not possible or cumbersome with regular fields.
Here is the entire calculation for converting numbers to text:
GetRepetition(Zero to Twenty, Int(Number) / 100000000) & GetRepetition(Hundred to Billion, (Int(Number / 100000000) > 0) * 2) & GetRepetition(Twenty to Hundred, Right(Int(Number / 10000000), 1)) & GetRepetition(Zero to Twenty, (Left(Right(Number, 8) / 10000000, 1) = 1) & Int(Right(Int(Number), 7) / 1000000)) & GetRepetition(Hundred to Billion, (Length(Int(Number)) > 6) * 4) & GetRepetition(Zero to Twenty, Left(Right(Int(Number), 6) / 100000, 1)) & GetRepetition(Hundred to Billion, (Int(Right(Int(Number), 6) / 100000) > 0) * 2) & GetRepetition(Twenty to Hundred, Right(Int(Number / 10000), 1)) & GetRepetition(Zero to Twenty, (Left(Right(Int(Number), 5) / 10000, 1) = 1) & Left(Right(Int(Number), 4) / 1000,1)) & GetRepetition(Hundred to Billion, ((Right(Int(Number), 6) -999.99 / 1) > 0) * 3) & GetRepetition(Zero to Twenty, Right(Int(Number), 3) / 100) & GetRepetition(Hundred to Billion, ((Right(Int(Number), 3) - 99.99/ 1) > 0) * 2) & GetRepetition(Twenty to Hundred, Right(Int(Number / 10), 1)) & GetRepetition(Zero to Twenty, (Left(Right(Number, 2) / 10, 1) = 1) & Right(Int(Number), 1)) & Case(Int(Number) = 1, " dollar and", " dollars and") & GetRepetition(Twenty to Hundred, Middle(Round(Number - Int(Number), 2), 2, 1)) & GetRepetition(Zero to Twenty, (Middle(Round(Number - Int(Number), 2), 2, 1) = 1) & Right(Round(Number - Int(Number), 2) * 100, 1)) & Case(Round(Number - Int(Number), 2) * 100 = 1, " cent", Case(Round(Number - Int(Number), 2) = 0, " zero cents", " cents"))
Compared to previous calculations that accomplish the same task, this is a fraction of the size. Still, this calculation is fairly daunting, but if you break it out into pieces, it is actually quite easy to understand. When you look in the bonus file, the pieces are separated by returns so as to make it as easy as possible to read. Let's grab the first part of the calculation which determines if a value from one to twenty should be returned:
GetRepetition(Zero to Twenty, Int(Number) / 100000000)
The Int function takes the number that was entered and grabs just the integer portion of it. For instance:
$135, 438, 911.26
becomes
135438911
The Int function is used throughout the entire calculation, so become comfortable with what it does. The purpose is to isolate just the whole dollars in order to make it easier to convert it to text. For instance, let's say you enter a number with a decimal but the next time you enter the same a number without a decimal. Using the right, left, middle, length or dividing this number will produce different results. All the Int function does is guarantee the length of a number, let's say in the hundred millions, will be the same every time.
Next, the first part of the calculation divides by 100,000,000. As you read through the rest of the calculation, you will see many divisions by different numbers. This number is used because we are trying to figure out whether the number entered is in the hundred millions or not, thus the reason why the number is divided by one hundred million. When the example above is divided, it becomes:
1.35438911
If a number less than 100,000,000 were entered, a number less than 1 would be returned. Only whole numbers are used with the GetRepetition function, so a number like 0.23546 will be seen as 0 and a number like 1.35438911 will be seen as a 1. Which brings us to the reason the GetRepetition function is used. If a number 0 is returned, the GetRepetition function will return a null value or blank. If a 1 is returned, the first repetition will be grabbed which contains the value of "one". If a 2 is returned, the second repetition will be grabbed and so on.
The rest of the calculation is basically the same as the first part. That's because converting numbers to words involves the same words over and over. For instance, the number:
983479
is converted to:
nine hundred eighty three thousand four hundred seventy nine
The word "nine" is used twice in the conversion. Noting this, you can store all the words in repeating fields and reference them when you need them with the GetRepetition function.
As you go through the rest of the calculation, notice that each line varies slightly depending on whether it is trying to determine millions, thousands, etc. Your best bet for dissecting this calculation is to print out the calculation and plug in values. In addition, try copying a portion of the calculation and paste it into a temporary calculation just to see how that one piece works.
## END ##