
1. Re: Calculated Field, Leading Zeroes and limiting precision
deninger Mar 16, 2013 7:48 AM (in response to radiosaigon)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
radiosaigon Mar 16, 2013 7:35 AM (in response to deninger)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 Mar 16, 2013 7:55 AM (in response to radiosaigon)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
radiosaigon Mar 16, 2013 8:07 AM (in response to deninger)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 Mar 16, 2013 8:08 AM (in response to radiosaigon)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 nonadvance 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
radiosaigon Mar 16, 2013 8:17 AM (in response to deninger)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
radiosaigon Mar 16, 2013 8:42 AM (in response to radiosaigon)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 Mar 16, 2013 9:04 AM (in response to radiosaigon)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
radiosaigon Mar 16, 2013 9:22 AM (in response to deninger)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 Mar 16, 2013 9:47 AM (in response to radiosaigon)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
radiosaigon Mar 16, 2013 10:33 AM (in response to deninger)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
radiosaigon Mar 16, 2013 11:20 AM (in response to radiosaigon)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 Mar 16, 2013 11:22 AM (in response to radiosaigon)In comment the lines?
Sent from my iPhone

14. Re: Calculated Field, Leading Zeroes and limiting precision
radiosaigon Mar 16, 2013 11:47 AM (in response to deninger)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!