Critical Gap in API and JSON

Bugs and Feature Requests
1

I appreciate the complexity of the transition to API 2.0, which has been underway for a few years now. However, there’s a critical aspect that needs attention. It is surprising that Xojo has allowed such an oversight to continue for so long.

Recently, a bug/feature request was submitted regarding the handling of decimals in JSON (https://tracker.xojo.com/xojoinc/xojo/-/issues/74416).

The absence of this feature makes it impossible to accurately represent financial transactions in JSON when dealing with DOLLARS and CENTS. Without it, the JSON data frequently rounds itself off by a penny, which brings to mind the humorous rounding error in the Superman movie where all the pennies were piled into an account to buy a Ferrari.

For those of us developing business software, especially applications involving pricing and taxes, explaining to tax collection agencies that Xojo doesn’t round money correctly can be “problematic”. (the IRS doesn’t have a sense of humor)

If you are building Software for business. I urge you to vote this bug.

4 Likes
2

I urge you to use integers for such purposes as any system may handle doubles differently. You want standardization instead of flexibility for financial stuff.

Even in time based data transfer:
For example of time you can use a double but may be deemed to loose accuracy. So someone invented epoch timestamps in seconds and one in milliseconds and a int64 fits that easily.

Now for financial stuff you may want to use an integer in cents 199 and (as an addition) maybe a string to show the actual value e.g. for display “1.99” like most api’s in the worlld use for example Stripe, Mollie etc.

Don’t try to be different as i don’t think that would be a good way. I may be worth a try to add such but you may end up in (double) trouble.

1 Like
3

Most other systems (or programming languages) have a DataType that handles (decimal) numbers properly (not as Doubles).

If an (external) JSON-API defines it needs values as (decimal) numbers, then we need to send a JSON accordingly.

Nearly every JSON implementation is able to set number values with a user-defined format when it comes to decimal numbers. Such as we know of those provided as Xojo Plugins, or other custom JSON classes available for Xojo.

Not so with “Xojo Core/Framework Functionality”… I haven’t found a way (in recent Xojo Versions such as 2023rx) to have Xojo’s JSONItem produce proper (decimal) number values.

2 Likes
4

interesting, thanks

you mean stripe send 2 values ? string ands integer ? and you send int as cents ?

1 Like
5

In that case you would use JSONMBS class since it can pass and store numbers with their string value internally, so no double rounding happens.

That is both implemented in our old and new JSON class.

2 Likes
6

Stripe asks for value in cents: The Price object | Stripe API Reference
If you are so inclined you may send it a decimal value, but it must be a string.

Jürg’s desire for JSONItem isn’t totally invalid, but the way Jay is storing a currency value is inadvisable. Professional developers with experience in finance software recommend storing currency in whole units. Give it a quick Google search if you want some reading.

2 Likes
7

I think the problem with JSONItem now is that if we are talking to an API that needs/request a numeric value (with let’s say 2 decimals) we now need to say to our client “sorry we can’t do that, we can send doubles with many decimals more in some cases or send the value as string with 2 decimal places” while before it was possible without plugins.

There are some APIs that request cents other don’t. Storing financial data using integer will not help if we are asked to send 2 decimals and now JSONItem is not able to do so.

8

Yes most financial apis do both.

9

Eh, well i still urge the one that asked it to use integers since that IS always accurate on financial data. Having the decimals added is fine, but not advised. Just ask your local bank, i’m sure they will run away from such doubles or even formatted doubles. Every language may give different results or outcomes on reading them back.

You are free to ask for any feature to define as you wish but this topic was about financial data (or so i seen it) and that needs 100% accuracy and unchanged values. Only ints or strings may give you that with JSON.

10

Before this topic has been started, there was the Issue 74416.
That is solely about Xojo’s JSONItem no longer being able to produce properly formatted (decimal) number values. And there are many JSON API’s out there that want to received (decimal) number values - so Xojo should be able to deliver this, which used to be possible, but got “deprecated” (with no replacement) - and existing code (built with pre-2019 Xojo Versions) will produce different (expected) JSON-output (when compiled with Xojo 2023rx).

I agree that financial data is usually handled differently. That has been an example use-case by the OP of this forum topic.

In my opinion this topic isn’t specifically about “How to handle financial data in JSON”, but about the lack of Xojo being able to produce formatted (decimal) number values in JSON. A very basic feature that used to work in Xojo, and which about every JSON implementation can do.

4 Likes
11

Yes, this is very valid. I’m with Derk: send integers when you control the API. But if you don’t control the API, you’re kind of screwed.

2 Likes
12

The JSON spec does not offer support for a currency type, so even if you force the value to two decimal places, it would still be considered a Double or Float.

As others have suggested, use an integer instead if you control both ends, or a string might work if you don’t.

4 Likes
13

In the mean time, we always use Round on floats that are given to us.

var d as double = json.value("amount")
var n as integer = round(d * 100)

Now you have a safe integer value to work with.

1 Like
14

I’m not here to argue the request/issue but still advise to work towards using a safe, known method of standardizing the code instead of using unsafe, known to cause issues methods of parsing information. I wouldn’t want to have my bank or timing software use .DecimalFormat ever.

1 Like
15

Again if for financial stuf i would NOT use such, you may however do as YOU like. I’m just advising otherwise so that anyone who will publicly read this wil think twice. There is now lot’s of advice coming from others on this forum.

Again i’m not against the isse/feature request. Keep in mind JSON is a standard, i’d rather have xojo to stay on the standards.

1 Like
16

Just asking, does JsonItem.Value.CurrencyValue not return the correct value? I haven’t tested it. Or is that OP’s issue?

17

Since JSONItem uses double for floating point numbers, it will always round or format them with many digits.

e.g. compare JSONMBS to JSONItem:

Dim json As String = "{""test"": 1.23}"
// {"test": 1.23}

Dim j1 As New JSONItem(json)
Dim s1 As String = j1.ToString
// {"test":1.2299999999999999822}

Dim j2 As New JSONMBS(json)
Dim s2 As String = j2.toString
// {"test":1.23}

Break

And a lot of APIs using databases may have no problem to output decimal values from e.g. MySQL via JSON and include exact precision.

18 
Dim json As String = "{""test"": 1.23}"

in c# you could write as 1.23f or 1.23d or 1.23c
money values should be currency type and to/from json in xojo should result in same value.

19

Having a “Global Formatter” for decimal numbers is a wrong way of tackling this problem.

What we need is done in other languages using some special types know by JSON handling functions.

In Xojo, for instance, a currency type should render always as a number with 4 decimals, and having some special “Numeric Type” would handle all exceptional cases.

Var json As New JSONItem

Var cur As Currency 

json.Value("name") = "test"

cur = 1.935
json.Value("balanceDouble") = 1.935
json.Value("balanceCurrency") = Cur  // Should render as number with 4 decimals as "balanceCurrency": 1.9350
json.Value("balanceDecimal") = JSONNumber("1.935") // Special type that just renders the content without quotes as "balanceDecimal": 1.935

Var s As String = json.ToString // Xojo sadly does not handle things this way
1 Like
20

Not all.