3 Replies Latest reply on Apr 27, 2011 10:02 AM by LaRetta_1

    Good CalcField Practice



      Good CalcField Practice



      I have a couple of CalcFields in my report, they pull information from several fields each. At the moment the calculation is getting pretty large with lots of "Case IsEmpty" and "Case notIsEmpty" etc.

      Is there any tips or common practice when it comes to creating these types of calcfields that can reduce the calculation?


        • 1. Re: Good CalcField Practice

          The best advice is to use relational structure instead of multiple fields whenever possible.  If you have multiple 'like' fields such as Date1 and Date2 then odds are they should be records.  This will cut down the size and complexity of calculations and allow your solution to run faster.

          When possible, if the fields in question are number fields, use Boolean logic.  It is cleaner and easier to write and it is the fastest at evaluating.  Example: You have a date field and a number field ... both can be tested as:  not date (if you want empty date) or not number (if you want 0 or empty).  Same holds for true.  Case ( Date ; ShipChrg + InvAmt ) means if there is a date otherwise do not evaluate.  It is easier to write that than Case ( not IsEmpty ( Date ) ; ShipChrg + InvAmt ).

          Do not add unnecessary default results such as Case ( not Date ; ShipChg + InvAmt ; "" ).  Case(), If(), AND, NOT and many others quit evaluating when they hit the first true; known as short-circuiting so structure your calculations keeping in mind this short-circuit behavior, such as: Case ( InvAmt < 10 ; InvAmt ; InvAmt < 50 ; InvAmt * 25 )

          Use branch prediction (which will take full advantage of short-circuit behavior).  If you know that 90% of your records would contain a date, write that first, such as: Case ( date ; "Invoice Paid" ; "Invoice Due" )

          Use Let() to add clarity and reduce resources.  If a calculation uses Get ( CurrentDate ) + 15 several times, put it in a Let() so it evaluates only once then refer to that, such as: Let ( d = Get ( CurrentDate ) + 15 ; d < 3 ; "Urgent" ; d < 15 ; "Important" ; d < 30 ; "Reminder" ; "Due" )

          Write your calculations in split lines so they are easier to understand while writing them AND when viewing them later, such as:

          Let ( [
          d = Get ( CurrentDate ) + 15 ;
          diff = d - dateField
          ] ;
          Case (
          d < 3 ; "Urgent" ; d < 15 ; "Important" ; d < 30 ; "Reminder" ; "Due in " & diff & " days"
          ) // END CASE
          ) // END LET

          Unnecessary parenthesis use evaluations, clutter your calc so it is more difficult to understand, and they all of these extra evaluations add to slowing your system down.  Don't be lazy and add parenthesis just so things are clear to you - LEARN to know when and when not to use them. If you start using them unnecessarily then it will become habit and you will never understand when not to use them.  If in doubt, create a test file and try the calculation both ways.  Learn to leave them off unless needed, for instance,  if you want part of your calculation to evaluate first, such as to add and then multiply the result of that addition. 

          Put spaces between everything as indicate in my calculations.  It makes it easier to read AND if you need to change a field, you can then simply double-click the field and it grabs it perfectly.  Otherwise you will struggle to change a value quickly.

          And most importantly ... study the Masters such as Comment, CobaltSky and Mr_Vodka here:  http://www.fmforums.com/

          And do not be too proud to ask how a calculation might be better written and take the time to understand calculations ... create test files and break the pieces out into individual calculations and watch their results as you play with the test data until you grok.

          • 2. Re: Good CalcField Practice

            Thanks for the response, a lot to take in!

            Just a quick question, the reason for my CalcField is to display several fields (all text) as a sentence in the report. It needs to account for 1, some or all fields being blank. Is there anything in particular I should look at or take in to account?


            • 3. Re: Good CalcField Practice

              "At the moment the calculation is getting pretty large with lots of "Case IsEmpty" and "Case notIsEmpty" etc."

              Try using List(), something like:

              Substitute ( List ( text1 ; text2 ; text3 ) ; ¶ ; " " )

              List() will be applied first.  What it will do is produce a carriage-separated list but any blank fields will be ignored.

              Substitute() will then be applied (all calculations evaluate from the inside out.  Subsitute will conver the carriage returns to spaces and you will end up with all the fields within the same paragraph.