2 Replies Latest reply on May 13, 2012 2:09 PM by MorkAfur

    Calculation or Script?



      Calculation or Script?


      I have a field where the user can enter pound and ounces data either like this: 10:15 (10 pounds and 15 ounces) or like this: 10.4 (where the part after the "." has already been divided by 16).

      So, in the database, I was going to create a calculation on this field, but couldn't find enough documentation to get a basic IF statement working. (sigh)

      My goals for the calculation are below (and a work in progress...), but the calculation is quickly getting to complicated for this format of calculation forumlas -- that is, where all the logic is in simple in-line calculation forumlas.

      In the "IF" statement, for example, I'd like to be able to call another script or calculation "function" to handle the true and false parts of the IF. As it is now, this calculation will be messy and hard to maintain.

      Additionally, if I try to use a script instead, then I don't have all the calculation functions I need (like the string manipulation).

      Not sure how to approach this. I'm slogging my way through learning how FMP does this stuff...

      Also, I was trying to define a variable in the calculation below which seems to be OK, but then the "IF" statement below says it's expecting a +, -, * or / and doesn't work. I need to check the return value from the PatternCount in the IF to figure out what to do, right?

      How best to do this?



      // Need to convert a weight entered like 9:15 to decimal and if decima (divide the part after ":" by 16 and replace the ":" with "." If a // "." is already entered, do nothing (assume decimal entry already).

      // should only have one ": in the string
      $colonCount = PatternCount ( Weight ; ":" )
      //If ( $colonCount <= 1 ; "1" ; "22 ")  // just a quick meaningless hard coded example does not work, tried this first as a test.


      Again, my steps were going to be:

      1. See if there's a ":" in the entered weight (text) field.

      2. If so, take the substring to the right of the ":", convert it to number, divide by 16 and put back and change the ":" to a "." The weight is now in decimal form.

      3. Otherwise, if there's already a "." or no ":" or ".", assume correct data entry and do nothing.


      What am I missing?

      Thanks in advance.


        • 1. Re: Calculation or Script?

          Assuming your input field is a text field called "weight", try this as the calculation:

          Let( [ p = PatternCount( weight ; ":" ) ; left = LeftWords( Substitute( weight ; ":" ; " " ) ; 1 ) ; right= RightWords( Substitute( weight ; ":" ; " " ) ; 1 ) ] ;  If( p>0 ; GetAsNumber(left + right/16) ; GetAsNumber( weight ))) 


          P.S. This is probably just one of several ways and  can be further simplified.

          • 2. Re: Calculation or Script?

            Wow, that's really cool.

            I see I have a long way to go before I understand all the FMP functions.

            Thanks Jade. :)