4 Replies Latest reply on Feb 3, 2017 4:52 AM by wimdecorte

    Calculation Not Working When > 9

    enyko

      Sorry for the confusing title.

       

      We have a calculation that always worked in the past. But this is the first time we need to multiply by 10 and notice it doesn't work. In short, we want to multiply hours worked by a rate ($250). Here is what we have;

       

      If ( Additional DJ Hours < "3" ; 750 ; Additional DJ Hours * 250)

       

      [which should mean, if the hours are less than 3, charge $750. Otherwise, take "Additional DJ Hours" and multiply that by $250]

       

      Additional DJ Hours field information uses a value list which basically consists of numbers 0 - 20.

       

      When we select anything above 9, it gives us $750.


      What am I missing?

       

      Thank you!

        • 1. Re: Calculation Not Working When > 9
          philmodjunk

          Get rid of the quotes and make sure that your data is of type number in that field and not text.

           

          "3" > "1000000"

           

          is a true statement.

           

          3 > 10

           

          is not.

          • 2. Re: Calculation Not Working When > 9
            Vaughan

            "3" (three inside quotes) is data type text and text gets compared alphabetically.

            • 3. Re: Calculation Not Working When > 9
              enyko

              Thank you philmodjunk & Vaughan This worked great!

               

              This possibly should be a separate post but whenever I make updates in FMP like this, is there a way to force it to only affect future data? It looks like this update changed a bunch of records from the past few years and adjusted all the financials (which we don't want). Is there a setting that tells FMP to not make any changes to fields that were already calculated?

               

              Thanks!

              • 4. Re: Calculation Not Working When > 9
                wimdecorte

                Yes, that's possible.  It's called 'design for that'

                 

                Don't use calculated fields for things that only need to be set once and will never change.  If you do use calculations then add an if or case branch based on the creation date of the record (= ugly!)

                 

                One small step better than calculated fields are auto-enter calculations but even here you have to be careful because these can retrigger under certain circumstances.

                A scripted workflow where the script takes care of setting values is my preference.  It avoids retriggering and produces static data that is fast to report on and search on.

                 

                It's a classic design pitfall that many fall into.