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).
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 ","
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.
Can you specify the platform, OS and what version of FM you used.
Mac OS 10.7.3
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?
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)
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:
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:
~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:
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.