1 2 Previous Next 15 Replies Latest reply on Sep 25, 2012 11:54 AM by philmodjunk

    Decimal seperator "weird" functioning.



      Decimal seperator "weird" functioning.


           This has kept me busy for almost 4 hours straight now, without any progress.

           The main problem:
           When I enter a number, in a numeric field (non-filtered), everything goes allright.
           Once I enter that same value using a script, FileMaker (11 Pro Advanced, on a local DB) does not recognise the decimal seperator (although in both cases it's a period).

           I've already checked my script's output with a dialog, but until the field, everything seems ok.

           Just placing a period as text behind the first character is not an option in this case, due to the fact that the number will be somewhere between 5 and 80 000.

           Yes, I know I over-use the PHP registering, but that was due to some other bug earlier in the process.
           Both scripts are ran with full access privileges

           The scripts that places the text into the field:

           (times script, 60s interval)

                Set Variable [$t; Value:PHP_Register]
                Set Variable [$r; Value:PHP_Execute ( "$from = 'EUR';

                $to = 'USD';
                $handle = fopen($url, 'r');
                if ($handle) {
                    $result = fgetcsv($handle);
                echo $result[0];" )]
                If[PatternCount ($r "Plug")]
                   Show Custom Dialog ["HELP"; "The PHP plug-in is not registered!"]  //Yes, this one still occurs sometimes
                   Set Veriable [$r; Value:TextReplace ( $r ; "," ; "." )]    //This was an earlier try to fix it, but did not work
                   Set Field [onrelevant::EURUSD; $r]                            //Store it in a numeric global storage field
                   Perform Script ["o_p_8"]                                               //This due to o_p_8 first being a Trigger Script
                End If
           Freeze Window
           Go to Layout ["onrelevant" (onrelevant)]
           Set Variable [$p; Value:onrelevant::EURUSD]
           Go to Layout [Overeenkomst"(Overeenkomst)]
           Go to Record/Request/Page [First]
              Set Field [Overeenkomst::TotaalUSD; Overeenkomst::TotaalEUR * $p]
              Go to Record/Request/Page [Next; Exit after last]
           End Loop
           Go to Layout [original layout]
           TextReplace ( text; searchString; replaceString )
                Let ([
                searchStringSplit = Substitute(searchString; "*"; ¶); 
                beginString = GetValue(searchStringSplit; 1); 
                endString = GetValue(searchStringSplit; 2); 
                lenBegin = Length(beginString);
                lenEnd = Length(endString);
                ptBegin = Position(text; beginString; 1; 1);
                ptEnd = Position(text; endString; ptBegin + lenBegin; 1) + lenEnd;
                lenAll = ptEnd - ptBegin; 
                keepText = Middle(text; ptBegin + lenBegin; lenAll - (lenBegin + lenEnd)); 
                modText = Left(text; ptBegin - 1) & Substitute(replaceString; "*"; keepText);
                remainText = Middle(text; ptEnd; 9999999)
                lenBegin > 0 and lenEnd > 0 and 
                ptBegin > 0 and ptEnd > lenEnd;
                modText & 
                TextReplace(remainText; searchString; replaceString)
                 ; text))


        • 1. Re: Decimal seperator "weird" functioning.

               What exactly is going wrong?

               is your script using a "." decimal separator but you are seeing "," as the decimal separator?

               , is the decimal separator used in place of . as the decimal separator in many parts of the world. Are your locations settings for your operating system set up correctly? Is this a file that was created with these location settings already specified?

          • 2. Re: Decimal seperator "weird" functioning.

                 The main thing is, my local settings indicate the "." being the decimal seperator, which I prefer.

                 Still, FileMaker works with neither (the replacement does work though). When placing the number (e.g. 1.29003) into the global storage, everything goes ok, but as soon as placing the number into Overeenkomst::TotaalUSD, the "." vanishes, eventhough both the global and TotaalUSD have the same settings for currency (only difference is the symbol), are both numeric and both have no filtering.

                 When typing in the numbers manually (using "."), the decimal seperator seems to work fine, while using "," just gets filtered out (using spaces as thousand-seperators for both global and TotaalUSD)

            • 3. Re: Decimal seperator "weird" functioning.

                   A field where "total" is part of the name usually is a calculation or summary field. Is Overeenkomst::TotaalUSD a field of one of those types or a field of type number?

                   Are you sayting that

                   Set Field [Overeenkomst::TotaalUSD ; 1.290003 ]

                   Puts 1290003 into the field?

                   Is this what is displayed, but you see 1.29003 when you click or tab into the field?

                   With the currency data formatting that you selected, did you select a fixed number of decimal places?

              • 4. Re: Decimal seperator "weird" functioning.

                     Overeenkomst::TotaalUSD is a field of type number.
                     And yes, Set Field [Overeenkomst::TotaalUSD ; 1.290003 ] puts 1290003 in the field.

                     This is both what is displayed as well as when I click in the field's layout edit box.
                     I've tried both with and without a fixed number of decimal places, both with the same result.

                • 5. Re: Decimal seperator "weird" functioning.

                       Your "." is being interpreted as "," which can be set in 4 locations.

                       1) Control panel/System Preferences.

                       2) Country Keyboard

                       3) FMP Numeric Format Field

                       4) Character Set being used  FMP Prefences[Font].

                       It appears that your FMP file may have been defined under one condition and you may have changed to another.

                       1 & 2 set the system mathmatical interpretation.



                  • 6. Re: Decimal seperator "weird" functioning.

                         I'll check that as soon as I'm at work again, because it's power adapter is still there.

                    • 7. Re: Decimal seperator "weird" functioning.

                           I'd check the field options for this field and see if there might be an auto-enter calculation that is filtering out the decimal separator. Otherwise, you should see some character in the field being used as a decimal separator when you click or tab into the field. (You see the actual data without the specified data formatting when you enter the field like this--which is why asked for what you see when you do this.)

                           The expression:

                           Filter ( Self ; 9876543210 )

                           for example, would filter out any decimal separator characters.

                           Filter ( Self ; "9876543210,.)

                           on the other hand, would retain both thousands and decimal separators in the data entered into the field.

                      • 8. Re: Decimal seperator "weird" functioning.

                             Robin, try some calculations with your field in another field.   Also use scientific format which uses "E" as a power of 10 or  1E3 = 1000 or 1E2=100 and there is not a "," or "." envolved.   Multilply your field by of say "1.234" in a new calculation field of  YourField * 1E2 should result in "123.4" but if it returns "123400" then thousands separtor is reversed.

                             I use Mac.


                        • 9. Re: Decimal seperator "weird" functioning.

                               Ah, forgot to mention I run on Windows 7 Ultimate x64 (build 7600).
                               Anyway, thinking it over again, I don't think it is reversed, because when manually typing in the field, everything works fine.

                               I'll check every suggestion though tomorrow as soon as my laptop boots again (It's kind of low on power now).


                               As I stated in the initial post, I have not build any filters in it, but I will check it anyway.

                          • 10. Re: Decimal seperator "weird" functioning.

                                 At this moment, I am testing all the possibilities you've posted.

                                 Turning the field into an actual calculation instead of script-inserted:    Did not work (although I also had to disable ' Perform Script ["o_p_8"] ' )
                                 Setting all formatting to general (of the global field, TotaalEUR and TotaalUSD) : Did not work
                                 The default character set is already being used inside FileMaker (which has worked earlier)
                                 A calculated field with 1E2 * 123.4 returns 12340, which is as expected with this calculation.
                                 TotaalUSD is now the calculation of: TotaalEUR * onrelevant::EURUSD

                                 Current Value of onrelevant::EURUSD = 1.291 (Both viewing and editing)
                                 Current Value of TotaalUSD = 1291 (Both viewing and editing)
                                 Current Value of TotaalEUR = 1 (Both viewing and editing)
                                 TotaalEUR is not calculated yet due to testing

                                 Images of my system formatting:

                                 [ Update ]
                                 After testing around a bit, I've come to the conclusion that the period retrieved from yahoo, is just a comma which looks like a period because of a wrong choice of font. One if the disadvantages though: " Substitute ( $r ; "," ; "." ) " does not replace the comma with a period like FileMaker uses.

                            • 11. Re: Decimal seperator "weird" functioning.

                                   Double checking... 

                                   Try a new test calculation using the FieldName * 1E2 ..... i wasnt sure if you did that so the Field content was used.

                                   Or test with  Code Function  which should return a 46 with a "."


                                   GetASURLEncoded Funtions which may adjust it properly.


                              • 12. Re: Decimal seperator "weird" functioning.

                                     The main problem at this point is the substitute function not replacing the "," with "."
                                     Also, FileMaker does not seem to react on my change of system settings between the decimal seperator (but it does change the dialog display)

                                     I might just go work around the problem with using the PHP plug-in which is already loaded anyway (I myself am more of a programmer then a layout designer, so PHP isn't the problem). My boss also thinks this is the best way at this moment, seeming it comes faster to a result, instead of working on the same bug for a week.

                                     Whenever I found what was wrong, I'll say so. For now I will leave it alone and work around the problem (that pretty much fits the rest of the DB, which seems to be running on duct-tape)

                                • 13. Re: Decimal seperator "weird" functioning.

                                            The main problem at this point is the substitute function not replacing the "," with "."

                                       That should work. The fact that is doesn't work for you suggests that the character is not really a comma but some other character. If you can figure out what that character is, then it should be possible to use substitute to replace it with a period.

                                  • 14. Re: Decimal seperator "weird" functioning.

                                         Yes, I've tried just copying it from the output, placing it into the filter, but that didn't work in FileMaker. It did work in PHP, so I'll stick with PHP.

                                    1 2 Previous Next