A Handy Way to Format Your Calculation

Have you ever created a FileMaker calculation that was a little complicated but it made sense at the time? And a few months later you look at that calculation again and you can’t figure it out?

I have to admit, my memory is pathetic. If I don’t write stuff down or put it in an email, it’s gone by the next day. Same with constructing calculations: if I don’t document them or format them in such a way to make them easier to read, I often can’t understand what the calc does or what I had intended it to do.

For years now, the clever people over at Aptworks Consulting have provided a tool on their website that will format any valid FileMaker calculation even ones that use custom functions.

Follow these instructions to format your calculations:

1. To be sure you are using a valid calculation, save the calc first, without formatting it. FileMaker will verify the calc to make sure you haven’t forgotten a parenthesis or semi-colon, or have used an incorrect field name.
2. Once FileMaker verifies it, edit the calc again, select the entire calculation and copy it to the clipboard.
3. Visit the Aptworks online Calculation Formatter and paste your calculation in place of the one that appears there by default. Click the Submit button.
4. Now select the formatted calc and copy to the clipboard again.
5. Go back to your FileMaker file.
6. Before pasting the formatted calc, comment out the old one just in case there was a problem. You can do that by entering “/*” at the beginning of your calc and “*/” at the end (without the quotes). Then paste the formatted calc after that.

This is a handy tool I use just about every day to keep my calculations looking clean and easy to read.

Here is a sample of a calculation, so you can see how the tool works:

Left(“This is a sample. Try it out or use your own!”;0) &
If(Round(Amt; Precision)<0; “-”; “”) & “$” & Right(Middle(10^11+
Round(Abs(Amt); Precision); 1; 3) & “,” & Middle(10^11+Round(Abs(
Amt);Precision); 4; 3) & “,” & Middle(10^11+Round(Abs(Amt);
Precision); 7; 3) & “,” & Middle(10^11+Round(Abs(Amt); Precision);
10; 3) & If(Precision>0; “.” & Right(10^11 + 10^Precision*(Round(Abs(Amt)-Int(
Abs(Amt)); Precision)); Precision); “”);Length(Int(
Round(Abs(Amt); Precision))) + If(Precision>0; 1+Precision; 0) +
Int((Length(Int(Round(Abs(Amt); Precision)))-1)/3))

Left( “This is a sample. Try it out or use your own!”; 0 ) &
If( Round( Amt; Precision ) < 0; “-”; “” ) & “$” &
Right(
Middle( 10 ^ 11 + Round( Abs( Amt ); Precision ); 1; 3 ) & “,” &
Middle( 10 ^ 11 + Round( Abs( Amt ); Precision ); 4; 3 ) & “,” &
Middle( 10 ^ 11 + Round( Abs( Amt ); Precision ); 7; 3 ) & “,” &
Middle( 10 ^ 11 + Round( Abs( Amt ); Precision ); 10; 3 ) &
If(
Precision > 0;
“.” &
Right(
10 ^ 11 + 10 ^ Precision *
( Round( Abs( Amt ) – Int( Abs( Amt ) ); Precision ) );
Precision
);
“”
);
Length( Int( Round( Abs( Amt ); Precision ) ) ) +
If( Precision > 0; 1 + Precision; 0 ) +
Int( ( Length( Int( Round( Abs( Amt ); Precision ) ) ) – 1 ) / 3 )
)