1 2 Previous Next 17 Replies Latest reply on Aug 18, 2017 11:20 AM by TSGal

    Another decimal point issue with JSON function

    airmoi

      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 !!

        • 1. Re: Another decimal point issue with JSON function
          FileKraft

          did you try to toggle from System Settings to File Settings?

          • 2. Re: Another decimal point issue with JSON function
            airmoi

            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 of 1 people found this helpful
            • 3. Re: Another decimal point issue with JSON function
              FileKraft

              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)   

              • 4. Re: Another decimal point issue with JSON function
                TSGal

                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.

                • 5. Re: Another decimal point issue with JSON function
                  TSGal

                  airmoi:

                   

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

                   

                  TSGal

                  FileMaker, Inc.

                  • 6. Re: Another decimal point issue with JSON function
                    helusay

                    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 ).

                    • 7. Re: Another decimal point issue with JSON function
                      airmoi

                      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...

                      • 8. Re: Another decimal point issue with JSON function
                        helusay

                        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.

                        • 9. Re: Another decimal point issue with JSON function
                          TSGal

                          airmoi:

                           

                          This issue has been addressed in FileMaker Pro 16.0.2.

                           

                          TSGal

                          FileMaker, Inc.

                          2 of 2 people found this helpful
                          • 10. Re: Another decimal point issue with JSON function
                            AndreasT

                            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.

                            • 11. Re: Another decimal point issue with JSON function
                              FileKraft

                              seams correct because it interpretes the result as number and matches your number locale.

                              • 12. Re: Another decimal point issue with JSON function
                                AndreasT

                                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.

                                • 13. Re: Another decimal point issue with JSON function
                                  FileKraft

                                  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.

                                  • 14. Re: Another decimal point issue with JSON function
                                    AndreasT

                                    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 2 Previous Next