Notice
  • Please enter your DISQUS subdomain in order to use the 'Disqus Comments (for Joomla)' plugin. If you don't have a DISQUS account, register for one here

Core FileMaker Blog

Keep up to date with CoreSolutions

Steve Hearn Steve Hearn

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 )
)

Leave a comment

4 comments

CoreSolutions Logo

London Office:
1-1615 N Routledge Park
London, ON N6H 5L6

Toronto Office:
2425 Matheson Blvd East
7th Floor
Mississauga, ON L4W 5K4

Toll Free: (800) 650-8882
London: (519) 641-7727
Toronto: (416) 410-8649
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

SUBSCRIBE

Click the 'Subscribe' button to sign up for news & software tips.

Beyond Software

Copyright © 2018 CoreSolutions Software, Inc. All Rights Reserved