5 Replies Latest reply on Feb 12, 2015 6:14 AM by Ben

    ExecuteSQL decimal separator issue?

    powell

      I'm developing in Italy, where decimal and thousand separators are inverted compared to the US usage, so decimal is comma while thousands is dot.

      Using the ExecuteSQL function, pointing to the Filemaker database (not an external source), returns US separators.

       

      So, for instance, I've got a number that is 10,5 and a calculation field, based on an ExecuteSQL function, where I have 2.6.

      Trying to SUM the fields is a big mess! 2.6 is converted in 26... so I will have unwanted results.

       

      Any idea on how to setup decimal separators on ExecuteSQL function?

        • 1. Re: ExecuteSQL decimal separator issue?
          powell

          Still looking for an answer but, in the meanwhile, found a workaround replacing the dot in the returned value with comma (previously testing if the dot is or not the decimal separator).

           

          Example:

           

          Substitute (

               ExecuteSQL("SELECT Sum(PrzTot) FROM AcquistiRighe WHERE prodotto = '" & Prodotto & "' AND cAnno = " & Anno; ""; "");     # (1)

               If (Position(GetAsText(1,1); "."; 1; 1)> 0; ""; ".");     # (2)

                ",")

           

          (1) this returns the value with dot decimal separator

          (2) if 1,1 (decimal in Italy is converted into 1.1 in US environement) contains "." then I don't search for anything (empty string) otherwise contains comma and look for dot (".")

          (3) replace what found in 2 with a comma ","

          • 2. Re: ExecuteSQL decimal separator issue?
            beverly

            powell, that's good that you found a fix.

             

            Can you specify the platform, OS and what version of FM you used. Is it localized for everything but the ExecuteSQL function?

             

            I think you need to report this.

            Beverly

            • 3. Re: ExecuteSQL decimal separator issue?
              powell

              Hi Beverly!

               

              Can you specify the platform, OS and what version of FM you used.

               

              Mac OS 10.7.3

              FM 12.0v1

               

               

              Is it localized for everything but the ExecuteSQL function?

               

              Yes, everything is localized for Region Italy (dates, times, numbers) and everything works fine (except this function).

               

              I think you need to report this.

              Where should I report it?

               

              Thanks

              • 4. Re: ExecuteSQL decimal separator issue?
                Ben

                Hi Powell,

                 

                I am in Germany - I have exactly the same problem as you - here also we use , as the decimal separator. Like you I am using the Substitute ( ) function to change "." to ","

                 

                As to Where you should report this, you'll find a technical support phone number at Supporto standard FileMaker | FileMaker (Italia)

                 

                I have reported this problem today, when I get an answer I'll post it here.

                 

                My system details:

                MAC OS X 10.10

                FileMakerPro, Advanced and Server 13 (latest versions)

                • 5. Re: ExecuteSQL decimal separator issue?
                  Ben

                  Hi Powell,

                   

                  sorry this took so long - I've been busy!

                   

                  According to Filemaker Support: "this is not a Bug it's a feature" as documented in the Filemaker Documentation:

                   

                  FileMaker Pro returns date, time, and number data in Unicode/SQL format when using ExecuteSQL. So it will return the number field using "." instead of ",".

                  Please see page 844 in the FileMaker Pro 13 help: http://fmhelp.filemaker.com/docs/13/en/fmp13_help.pdf

                   

                  they also said:

                   

                  I have forwarded your comments to my Level 2 Support. They seem to agree with your comments, that this is not a good state of affairs.

                   


                  However, as this is how the software is currently designed to work, we can only encourage you to fill in a feature request:

                   

                   

                  http://www.filemaker.com/company/contact/feature_request.html

                   


                  This is the best route to our development team, to get your voice heard.

                   

                  So there we have it

                   

                  my work-around, in general terms, looks like this:

                   

                  Let ([

                  ~separator = [A FUNCTION TO DETERMINE THE DECIMAL SEPARATOR OF THE CLIENT] ;

                  ~table = Quote ( Left ( GetFieldName ( tableOccuranceName::number ) ; Position ( GetFieldName ( tableOccuranceName::number ) ; ":" ; 1 ; 1 ) -1 ) ) ;

                  ~number = Quote (  Middle ( GetFieldName ( tableOccuranceName::number ) ; Position ( GetFieldName (tableOccuranceName::number ) ; ":" ; 1 ; 1 ) + 2 ; Length ( GetFieldName (tableOccuranceName::number ) ) - Position ( GetFieldName ( tableOccuranceName::number ) ; "::" ; 1 ; 1 ) ) ) ;

                  ~query = "SELECT " & ~number & " FROM " & ~table & “

                  ];

                  Substitute ( ExecuteSQL ( ~query ; "" ; "”  ) ; “.” ; ~separator )

                  )

                   

                  In fact i use the customs functions: GFN() and GTN() from Kevin Frank [thank you Kevin] which you can find here:

                   

                  FM 12 ExecuteSQL: Robust Coding, part 1 | FileMakerHacks

                   

                  to get the table and field names in SQL-readable form and I simply set "," (comma) as the separator for now.

                   

                  Still it's pretty ugly!

                   

                  So now I am off to fill in a feature request.