1 2 Previous Next 17 Replies Latest reply on Mar 16, 2013 6:30 PM by radiosaigon

    Calculated Field, Leading Zeroes and limiting precision

      Greetings

       

      Further to the discussion here:, I have made a few modifications to the calculation provided by jbante. These modifications include:

      • Getting the Cardinal directions (N,S,E,W) reporting corrctly for positive (N, E) cardinals and (S, W) for negative cardinals,
      • Leading Zeroes on whole degrees for both Latitude and Longitude -to more closely comply with "correct" (perhaps more commonly used and familiar?) notation, and
      • Limited the decimal precision of Minutes of arc to 5 places.

       

      So far, so good.

       

      My final stumbling block with this is to get Leading Zeroes in the whole minutes notation of the coordinates. For example, the Decimal Latitude coordinate -4.00699999183416 is correctly rounding to -4.00699999. After calculation, the result is displaying as: S04° .42'... which should be, with "correct" notation: S04° 00.42'. I've tried adding a Right function, without success... I suspect it may be to do with where I am calling that function, but I can't make it work!

       

      The calculation as it currently exists:

       

      Let ( [

      ~original = LATITUDE ;

      ~negative = ~original < 0 ; // True (1) if value is negative

      ~positive = Abs ( ~original ) ;

      ~degrees = Div ( ~positive ; 1 ) ;

      ~minutes = Mod ( ~positive ; 1 ) * 60 ;

      ~decimal = ~degrees + ~minutes / 60 ;

      ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

      ] ;

      If ( ~negative ; "S" ; "N")

      & Right ( "00" & ~degrees ; 2 ) & "° "

      & Round ( ~minutes ; 5 ) & "'"

      )




      Would appreciate any assistance available!

       

      Regards

      RS

       

       

       

        • 1. Re: Calculated Field, Leading Zeroes and limiting precision
          deninger

          I use a custom function to pad strings (see below). You could use this (either as a custom function, or incorporate it to you let statement) to add leading zeros

           

          /* Pad ( _input ; _char ; _length ; _left_or_right )

           

           

          Padds a string on a given length, left or right.

          Not recursive.

           

           

          Pad ( "James" ; "+-" ; 10 ; "Right" )  --> "James+-+-+"

          Pad ( "45.52" ; "." ; 15 ; "Left" )   --> "..........45.52"

          Substitute ( text ; searchString ; replaceString )

          */

           

           

          Let ([

              _str = Substitute ( ( 10  ^  _length ) - 1 ; 9 ; _char )

            ; _side = Case ( _left_or_right = "right" or _left_or_right = "r"  or _left_or_right = "Right"; "R" ; "L" )

          ];

          Case ( _side = "R"

             ; Left ( _input & _str ; _length ) ; Right ( _str & _input ; _length )

           

           

          )

          )

           

          Message was edited by: deninger (modified the comment to reflect the actual pad direction, as I had changed it years ago to reflect how I though it should work)

          • 2. Re: Calculated Field, Leading Zeroes and limiting precision

            Thanks deninger, I may be a bit obtuse, but I don't really see how that will work in my situation. The Minutes part of my coordinate expression needs to add zeroes as part of the "mathematical" expression... where the calculated number is less than 10, I need one leading zero, where it's less than 1, I need 2 leading zeros.

             

            If your function will do that, how do you recommend I implement it? I'm a bit of a nub at all this ;-)

             

            Regards

            RS

            • 3. Re: Calculated Field, Leading Zeroes and limiting precision
              deninger

              Let ( [

                        ~original = -4.00699999183416 ;

                        ~negative = ~original < 0 ;          // True (1) if value is negative

                        ~positive = Abs ( ~original ) ;

                        ~degrees = Div ( ~positive ; 1 ) ;

                        ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                        ~decimal = ~degrees + ~minutes / 60 ;

                        ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

              ] ;

                         If ( ~negative ; "S" ; "N")

                        & Right ( "00" & ~degrees ; 2 ) & "° "

                        & Pad (Round ( ~minutes ; 5 ) ; "0" ; 5 ; "Left") & "'"

              )

               

              Evaluates to S04° 00.42'

               

              You might consider padding the degrees this way instead of your Right ("00" & ~degrees ; 2) in the case where you have a value greater than S09°

               

              Message was edited by: deninger

              • 4. Re: Calculated Field, Leading Zeroes and limiting precision

                OK, deninger, many thanks! That's definitely the result I am seeking. I see from your original reply that I need to define the Pad function within the Let statement, correct? So if I include:

                 

                Let ([

                    _str = Substitute ( ( 10  ^  _length ) - 1 ; 9 ; _char )

                  ; _side = Case ( _left_or_right = "right" or _left_or_right = "r"  or _left_or_right = "Right"; "R" ; "L" )

                ];

                Case ( _side = "R"

                   ; Left ( _input & _str ; _length ) ; Right ( _str & _input ; _length )

                 

                 

                )

                )

                 

                within the existing Let statement, that will define the Pad function?

                 

                hmmmm... perhaps a bit of a problem: I am using FMP 11, not Advanced. It seems I don't have the option to add Custom Functions. Can I script this, or better to run it entirely within the calculation? I have tried once to define it within the calculation and it tells me it can't find the field -length? Is there some modification I need to do within the Let statement to get the Pad function working?

                 

                Message was edited by: radiosaigon

                • 5. Re: Calculated Field, Leading Zeroes and limiting precision
                  deninger

                  If you are using FM Advance, you can add a custom function (see Manage -> Custom Functions). This makes the custom function available in any calculation. Doing it this way, it works like I wrote it above. A custom function actually shows up in the calculations drop down just like the built in functions. These functions are available to those opening the database with a non-advance version, but are not editible by the them. This is one really big reason to buy FM Advance if one is developing.

                   

                  If you are not using Advance, you will have to manually add a few parts to your let statement calculation. Below is a simplified version (that does not require custom functions nor does it handle arbitrary padding. It is set to pad up to two zeros on the front)

                   

                  Let ( [

                            ~original = -4.00699999183416 ;

                            ~negative = ~original < 0 ;          // True (1) if value is negative

                            ~positive = Abs ( ~original ) ;

                            ~degrees = Div ( ~positive ; 1 ) ;

                            ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                            ~roundedMinutes = Round ( ~minutes ; 5 );

                            ~paddedMinutes = Right ("00" & ~roundedMinutes ; 5);

                            ~decimal = ~degrees + ~minutes / 60 ;

                            ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                   

                  ] ;

                             If ( ~negative ; "S" ; "N")

                            & Right ( "00" & ~degrees ; 2 ) & "° "

                            & ~paddedMinutes & "'"

                  )

                   

                  Evalutes to S04° 00.42'

                  • 6. Re: Calculated Field, Leading Zeroes and limiting precision

                    Thanks very much deninger! That seems to be working in most cases... I'm seeing the odd outlier. For example: -3.90333336777985 is evaluating as:

                    S03° 054.2' That's not happening in every case, but often enough to be a problem.  Something I'm not seeing in there?

                    • 7. Re: Calculated Field, Leading Zeroes and limiting precision

                      Ah, I think possibly I see the problem. What I did was reduce the paddedMinutes to 4, which solved the problem of the additional 0 "flier" to the left of the whole minutes. That removed the padded 0 from the minutes less than 10 though.

                       

                      So I'm thinking perhaps we need your function to work from the Left?

                       

                       

                      ahh no. On reflection, the number of decimal places the function is working on is the problem. I need to restrict the input to the function to a defined number of decimal places in all cases for the function to achieve the aim, even if some/most/all are zeroes! Does FMP have a function to force that?

                      • 8. Re: Calculated Field, Leading Zeroes and limiting precision
                        deninger

                        This is happening because your value for minutes is 54.200002066791 and the trailing zeros are being dropped by the Round function. FM Advance has a great tool called Data Viewer where you can model these calculations and break them into components to debug. Yet another reason to upgrade.

                         

                        Let ( [

                                  ~original = -3.90333336777985 ;

                                  ~negative = ~original < 0 ;          // True (1) if value is negative

                                  ~positive = Abs ( ~original ) ;

                                  ~degrees = Div ( ~positive ; 1 ) ;

                                  ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                                  ~roundedMinutes = Round ( ~minutes ; 5 ) ;

                                  ~intMinutes = Int (~roundedMinutes);

                                  ~decimalMinutePart = ( ~roundedMinutes - ~intMinutes) * 100;

                                  ~paddedDecimalMinutes = Left (~decimalMinutePart & "00" ; 2);

                                  ~paddedIntMinutes = Right ("00" & ~intMinutes ; 2);

                                  ~resultMinutes = ~paddedIntMinutes & "." & ~paddedDecimalMinutes;

                                  ~decimal = ~degrees + ~minutes / 60 ;

                                  ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                         

                        ] ;

                                   If ( ~negative ; "S" ; "N")

                                  & Right ( "00" & ~degrees ; 2 ) & "° "

                                  & ~resultMinutes & "'"

                        )

                         

                        Which evaluates to S03° 54.20'

                         

                        a lot more complicated, but it now handles the case where zeros are dropped by the round function.

                         

                        Also, I don't see ~decimal being used. This could be removed from the statement to clean it up some...

                        • 9. Re: Calculated Field, Leading Zeroes and limiting precision

                          I agree deninger. You are making an excellent case for upgrading to Advanced. I will certainly be looking into that when needs must, but for the meantime the tools I have available have to suffice.

                           

                          We're on the right track. That seems to have sorted out the leading zero issues, given the sample of my database that I've checked -but, there is now a problem with the mathematics that wasn't there before. The coordinates: -4.118167 when converted return S04° 07.9.' instead of:

                          S04° 07.09 -not a typo, the extra .9.' is as it appears now.

                          • 10. Re: Calculated Field, Leading Zeroes and limiting precision
                            deninger

                            One last try

                             

                            Let ( [

                                      ~original = -4.118167 ;

                                      ~negative = ~original < 0 ;          // True (1) if value is negative

                                      ~positive = Abs ( ~original ) ;

                                      ~degrees = Div ( ~positive ; 1 ) ;

                                      ~minutes = Mod ( ~positive ; 1 ) * 60 ;

                                      ~roundedMinutes = Round ( ~minutes ; 5 ) ;

                                      ~intMinutes = Int (~roundedMinutes);

                                      ~decimalMinutePart = ( ~roundedMinutes - ~intMinutes) ;

                                      ~decimalMinutePartAsText = Middle ( ~decimalMinutePart ; 2 ; 2 );

                                      ~paddedDecimalMinutes = Left (~decimalMinutePartAsText & "00" ; 2);

                                      ~paddedIntMinutes = Right ("00" & ~intMinutes ; 2);

                                      ~resultMinutes = ~paddedIntMinutes & "." & ~paddedDecimalMinutes

                            //          ~decimal = ~degrees + ~minutes / 60 ;

                            //         ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                             

                            ] ;

                             

                             

                                       If ( ~negative ; "S" ; "N")

                                      & Right ( "00" & ~degrees ; 2 ) & "° "

                                      & ~resultMinutes & "'"

                             

                             

                            )

                            • 11. Re: Calculated Field, Leading Zeroes and limiting precision

                              HoOoRaHh deninger! That one's the job! I've very quickly checked it against ~400 waypoints, with particular attention to where we were seeing the problems -and so far, every one's a winner! ;-)

                               

                              Many thanks for all your work on this, I really do appreciate it. It's a massive help towards my meeting an 08:00 Monday deadline with good information! I'm very grateful.

                               

                              Best regards

                              RS

                               

                              PS: I'll append this updated formula to the end of the other thread also, in the hope it's of some benefit to someone else trying to sort a similar problem!

                              • 12. Re: Calculated Field, Leading Zeroes and limiting precision

                                I was a little hasty deninger -there is one minor bug. I have lost my N,S/E,W calculation. I'm almost certain it's related to the line you commented out: //         ~decimal = If ( ~negative ; 0 - ~decimal ; /* Else */ ~decimal )

                                 

                                So, with decimal no longer being defined, I have tried:  ~resultMinutes = If ( ~negative ; 0 - ~resultMinutes ; /* Else */ ~resultMinutes )

                                ... but that hasn't helped at all. To be honest, I just can't see where I need to be bringing the final result back to a Negative again. Without that calculation, all my coordinates South of the Equator or West of the Prime Meridian are reporting as N and E respectively!

                                • 13. Re: Calculated Field, Leading Zeroes and limiting precision
                                  deninger

                                  In comment the lines?

                                   

                                  Sent from my iPhone

                                  • 14. Re: Calculated Field, Leading Zeroes and limiting precision

                                    1st thing I tried, naturally. It's strange, it's like there are invisible characters or something that I can't see influencing it. I get a Field Not Found error, with the ~paddedDecimalMinutes from the line above highlighted with the ~decimal from the 1st line uncommented. Weird. There's something there I'm not seeing.

                                     

                                    Additionally, it occurs that the Cardinal point calculation should be evaluating from the 2nd line of the Let statement, shouldn't it? I can't see why not!

                                    1 2 Previous Next