Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

Another decimal point issue with JSON function

Discussion created by airmoi on Jun 1, 2017
Latest reply on Nov 26, 2017 by Ben

Like • Show 0 Likes0
Comment • 18

As explained in a previous post here Data API decimal separator issue with Tableau (and everything else...) , json standard defines "." as the decimal separator.

When using JsonSetElement on a comma based system, the number is converted properly in the json string.

Unfortunately, when you try to get back that value, using JsonGetElement, value is returned as is, with a "." decimal separator, leading to errors and inconsistent datas...

Here is a concrete example you may test in a data viewer :

Let ( [

_MyNumber = 2,5;

_MyJson = JSONSetElement ( "{}" ; "MyNumber" ; _MyNumber ; JSONNumber )

];

"MyJson = " & _MyJson

& ¶ & "MyNumber = " & _MyNumber

& ¶ & "Json Number returned = " & JSONGetElement ( _MyJson ; "MyNumber" )

)

It returns :

MyJson = {"MyNumber":2.5}

MyNumber = 2,5

Json Number returned = 2.5 <== not a correct number for a comma based system !!

Outcomes

18 Replies

FileKraft Jun 1, 2017 8:03 AM
did you try to toggle from System Settings to File Settings?
Like • Show 0 Likes0
Actions
airmoi @ FileKraft on Jun 1, 2017 8:19 AM
File as the same settings as the system (it takes system settings on creation), that won't work.

By the way, this would not be a satisfactory solution, we can't ask customer to use a different separator in their solutions just because FileMaker can't handle it properly...

The only workaround I found is to wrap the function in a custom function to convert numbers properly, which is dumb !
1 person found this helpful
Like • Show 1 Like1
Actions
FileKraft @ airmoi on Jun 1, 2017 8:26 AM
i was just curious - since there has been also inconsistent behavior with Date formatting using GetNthRecord where system and file settings are not respected and might swap month and day ..

(haven't tested if this is finally fixed - it occurred in 13 and 14 though)
Like • Show 0 Likes0
Actions
TSGal Jun 2, 2017 8:24 AM
airmoi:

Thank you for your posts.

I have sent all information to our Development and Testing departments for review. When I receive any feedback, I will let you know.

TSGal
FileMaker, Inc.
Like • Show 0 Likes0
Actions
TSGal @ airmoi on Jun 20, 2017 1:35 PM
airmoi:

Our Testing department is able to reproduce the issue. All information has been sent to Development for further review.

TSGal
FileMaker, Inc.
Like • Show 0 Likes0
Actions
helusay Jun 21, 2017 3:00 PM
I fiddled with you calculation a bit and I think you may be able to use something similar to this as a work around:

Let ( [
_MyNumber = "2,5";
_MyJson = JSONSetElement ( "{}" ; "MyNumber" ; Substitute ( _MyNumber ; "," ; "." ) ; JSONNumber )
];

"MyJson = " & _MyJson & "¶" &
"MyNumber = " & _MyNumber & "¶" &
"Json Number Returned = " & Substitute ( JSONGetElement ( _MyJson ; "MyNumber" ) ; "." ; "," )
)

Result:
MyJson = {"MyNumber":2.5}
MyNumber = 2,5
Json Number Returned = 2,5

Not sure if it is helpful to you or not, but if you are going to use a field for _MyNumber you could express that as GetAsText( field ).
Like • Show 0 Likes0
Actions
airmoi @ TSGal on Jun 22, 2017 1:05 AM
Hi TSGal,

Glad to hear that !
Hope they won't fix it using comma's in json...

helusay That's already what we are doing, but it assumes you already know which data type is expected, which is not always the case...
Like • Show 0 Likes0
Actions
helusay Jun 22, 2017 9:31 AM
Hey airmoi,

That is why I suggested changing the variable to "_MyNumber = GetAsText ( field )". When you are not sure what the data type will be, GetAsText ( field ) will always return your result as a string which can then be converted to a workable data type.
Like • Show 0 Likes0
Actions
TSGal Jul 10, 2017 3:17 PM
airmoi:

This issue has been addressed in FileMaker Pro 16.0.2.

TSGal
FileMaker, Inc.
2 people found this helpful
Like • Show 2 Likes2
Actions
AndreasT @ TSGal on Aug 15, 2017 2:28 PM
I ran into this issue today on a comma based locale. I am using version 16.0.2.

I DON'T want FM to mess with the JSON data, but because of this update it does.

The JSON I get from another system is something like this:

{
"results": [{
       "geometry": {
            "location": {
                 "lat": 50.6428116,
                 "lng": 25.0086824
            }
       }
}]
}

JSONGetElement ( _GLOBALS::mapdata ; "results[0].geometry.location.lat" )

returns 50,6428116 , comma substituted.

This conversion clearly should be optional or am I missing something?

P.S. I know I can substitute this out afterwards.
Like • Show 0 Likes0
Actions
FileKraft @ AndreasT on Aug 16, 2017 1:53 AM
seams correct because it interpretes the result as number and matches your number locale.
Like • Show 1 Like1
Actions
AndreasT @ FileKraft on Aug 16, 2017 2:43 AM
I disagree. Any function that has a clear objective (such as retrieving an element from a JSON object), should not impose additional interpretation logic on the result without it being optional. Of the top of my head, I don't know of any other FileMaker function of that does this. Try feeding a number in the wrong locale to a number function. They all choke.
In this case it's a number yes, but it's a map coordinate supposed to be fed back to Google in the same format they provide it. No point in localizing it as I then have to substitute it to send it back.
Like • Show 0 Likes0
Actions
FileKraft @ AndreasT on Aug 16, 2017 3:06 AM
i understand and see the inconsistency. would you prefer it as like as in executeSQl gives you back a date as text string you need to apply the conversion yourself to get it to a FM date field?

so you prefer that FM JSON returns literal text strings, I think it is preferable to preserve object typing IMHO.
Like • Show 0 Likes0
Actions
AndreasT @ FileKraft on Aug 16, 2017 4:54 AM
I would want it to be optional or raw like it's done with SQL. No way I would want any forced conversion.

So what if I get a customer in another locale using the same file? All kinds of issues pop up. I know one can restrict locale to the File's original in the File options but that's not what people usually want.
I always use the system setting here as people want to see their familiar decimal places and such. If you do that and then spread the file to another locale while using the JSONGetElement function, your logic will break. And that makes no sense.
Or we have to code for every contingency and that's just a waste of time and another thing that can break.

Make it optional.

Add an OPTIONAL type parameter to JSONGetElement, similar to the type parameter in JSONSetElement.
If there is no type parameter specified, interpret all you want, otherwise obey the parameter.
1 person found this helpful
Like • Show 0 Likes0
Actions
TSGal @ AndreasT on Aug 16, 2017 7:22 AM
AndreasT:

Thank you for your posts and comments. I have sent your posts and example to our Development and Testing departments for review. When I receive any feedback, I will let you know.

TSGal
FileMaker, Inc.
Like • Show 0 Likes0
Actions
FileKraft @ AndreasT on Aug 17, 2017 5:13 AM
AndreasT wrote:

I would want it to be optional or raw like it's done with SQL. No way I would want any forced conversion.

that is what i meant. a number in JSON should stay as a number if parsed out and matching the locale.
Like • Show 0 Likes0
Actions
TSGal @ AndreasT on Aug 18, 2017 11:20 AM
AndreasT:

Testing has sent your information to Development for further review.

TSGal
FileMaker, Inc.
1 person found this helpful
Like • Show 0 Likes0
Actions
Ben @ AndreasT on Nov 26, 2017 2:23 AM
Have you suggested this in Product Ideas? I'd vote it up
In addition it would be nice if ExecuteSQL and FM JSON were consistent and similar in behaviour.
Like • Show 0 Likes0
Actions

Another decimal point issue with JSON function

Outcomes

Related Content

Recommended Content

Incoming Links