AnsweredAssumed Answered

How to get the decimal separator of the current file

Question asked by Ben on Nov 26, 2017
Latest reply on Jan 15, 2018 by beverly

I'm not new to FileMaker and this problem is not new, it is a new look at an old issue which I need to solve

 

FYI - FMS 16 and Clients are FM16, but I don't think this is a version specific problem

 

The short version:

================================

I need a way to determine what decimal separator is being used by the file where a script is running so that I can substitute . and , as required.

 

Is there a way to "reset" the decimal separator in a file which was created on a machine where the system used the . decimal separator but is now running on a server that has a , as the system decimal separator?

 

The long version:

================================

 

Long ago I created a solution on a Mac with English (UK)  system settings which uses the . decimal. This solution now runs on a German Windows server which uses a comma , decimal separator. Some of the client users have their  system settings as German with a decimal , and others as English with a decimal .

I can't control the system settings of the client users. The file options setting for Text is: "always use current system settings" which I would like to keep this way as that is what the users are used to.

 

I have had various issues with the decimal character causing FileMaker to misinterpret numbers especially when moving numbers in and out of variables

 

I need a way to determine what decimal separator is being used by the file where a script is running so that I can substitute . and , as required.

 

The simplest way seemed to be: Middle ( Evaluate ( 3/2 ) ; 2 ; 1 ) which should return the decimal separator.

 

On a new, clean, test file this works as expected returning a , on a german machine and a . on an english machine.

 

But on my old file, running in a German system I get unexpected results in the data viewer:
Evaluate ( 3/2 ) returns 1,5 (as expected)
Middle ( Evaluate( 3/2; 2 ; 1 ) returns a . not a comma - an unexpected result

 

running in a English system I get the expected results
Evaluate ( 3/2 ) returns 1.5
Middle ( Evaluate( 3/2; 2 ; 1 ) returns a .

 

A further example:

GetAsNumber( "2,5" )

GetAsNumber ( "2.5" )

 

On a german system that uses the , decimal separator

In clean new file the results are as expected.

GetAsNumber( "2,5" ) returns 2,5

GetAsNumber ( "2.5" ) returns 25

 

In the old file, still running on the german machine, the results are:

GetAsNumber( "2,5" ) returns 25

GetAsNumber( "2.5" ) returns 2,5

 

I have only recently discovered this and it probably explains the issues I have had in the past. I don't understand how this works. Does anyone have a clue what is going on here and if there is a way to get the decimal separator in the file to math the decimal separator of the system settings?

 

Of course this can also lead to issues when using ExecuteSQL and FM JSON...(!)

 

Following on I tried this:

 

On the old file on the German machine I can do this:

GetAsNumber ( Substitute ( "2,5" ; "," ; "." ) ) which returns 2,5

or even this:

GetAsNumber ( Substitute ( "2,5" ; "," ; Middle ( Evaluate (3/2) ; 2 ; 1 ) ) ) which returns 2,5

 

So finally I must do this to take in either possibility and it seems to work whatever the system setting:

GetAsNumber ( Substitute ( "2,5" ; ["," ; Middle ( Evaluate (3/2) ; 2 ; 1 ) ] ; [ "." ; Middle ( Evaluate (3/2) ; 2 ; 1 ) ] ) )

 

That last line (obviously written nicer) seems to be the solution to my problem, but surely there is a nicer way to do this?

 

Any help and comments much appreciated

Outcomes