Having trouble with numeric formats in WEBDEV?

Having trouble with numeric formats in WEBDEV?

Digging another one off my pile of backlogged blog post ideas because someone else asked me about it this week. If you struggle with numeric formats in your WEBDEV projects, you are not alone. So let’s take a look at a few things and see what we use as out best practice.

First, let’s look at the settings as they come “out of the box”. I am not sure when this project-level setting came about, it may be a side effect of the intergraded IDE, but you will notice by default it wants to set the formatting of numbers based on the linguistic options of the operating system.

2020-05-16 09_02_22-Pete's Development Desktop

That would seem to make a lot of sense, but it has always lead to unexpected results for me in a WEBDEV project. Let’s look at the other side of the coin, our edit controls, again “out of the box” it wants us to set them to “Numeric defined by the project”

2020-05-16 09_07_33-Pete's Development Desktop

Again seems like a great idea, until we put it into action. I typed 15000 into the edit control and pressed tab, this is what I got when testing via GO

2020-05-16 09_09_06-Pete's Development Desktop

That’s right it forced 2 decimal places and used a , for the decimal point. BTW, for the uninitiated pretty much the entire world uses , for the decimal point other than the US and UK. My theory is that the browser doesn’t get all of the correct linguistics options from the OS and WEBDEV is defaulting back to it core logic which would be french formatting with a , for a decimal point and a space for the thousand separator. I also suspect this could get yet a different response when published to a web server, depending on the server os, hosting software (IIS/Apache), phase of the moon, etc. But let’s forget all of that for a bit, notice it is forcing 2 decimal places with I do “as defined by the project”. Well, that ain’t great if I wanted whole numbers, etc. So I find I can’t really use that setting anyway, and always end up setting the formatting myself. So let’s do that, say we want to allow up to 999,999.99 in this field and support positive and negative numbers. That is even one of the list box choices from the format popup.

2020-05-16 09_18_50-Pete's Development Desktop

But if you select that, and test again via GO, you will discover 15000 still gets formatted like above. So let’s go back over to the project settings and force the formatting of numbers instead of using the linguistics of the OS.

2020-05-16 09_22_13-Pete's Development Desktop

And finally, our 15000 looks correct!

2020-05-16 09_23_24-Pete's Development Desktop

BTW, the exact same type of settings exists for Currency as well, so you should change them there as well. 

Bonus Material edit control arithmetic

Now that we have our numbers displaying correctly. Let’s look at doing some arithmetic with them. If you have been using WEBDEV for very long you likely have discovered that edit controls can sometimes be treated always strings, and this can lead to some unexpected results.

Let’s look at these 2 statements

"Version 1:" + edtNumber1 + edtNumber2
"Version 2:" + (edtNumber1 + edtNumber2)

Those parentheses make a big difference in the results below

Version 1:100-10
Version 2:90

The best practice is to always do math in a numeric field first and always wrap edit controls with the Val function. It’s a bit of belt and suspenders that you don’t always need, but let me tell you from experience a bug caused by an edit control being treated as a string is hard to find and can introduce so very bad data into your database.

tmpVar is numeric = Val(edtNumber1) + Val(edtNumber2)
stcServerResults += [CR] + "Version 3:" + tmpVar

The definition of your edit control also plays a role, so far we have been using two edit controls defined as numeric using the formatting we set up in the first part of this article. If we change the format of edtNumber1 to be text, let’s see what happens

2020-05-16 09_49_49-Pete's Development Desktop

Server Results
Version 1:100-10
Version 2:100-10
Version 3:90

Notice version 2 no longer works but 3 does. That is why I said the best practice was to use a temporary numeric and wrap the controls with Val. At the very least always wrap your controls with Val, as you see Version 4 will also work in this scenario, but it sure is getting complicated with the parentheses and it only takes one misplaced parenthesis to completely change the result.

"Version 4:" + (Val(edtNumber1) + Val(edtNumber2))
Version 4:90

BTW, it would also be a good idea to read the online help concerning the Val function. It does some very interesting things

  1. It evaluates the string character by character and stops as soon as it hits a string that it doesn’t think is part of a number, so
    1. Val(“90+10”) = 90
    2. Val(“90ABC”) = 90
    3. Val(“90D1”) = 900
      1. Wait you say! See Rule #2
  2. It evaluates standard scientific notation, which is why 90D1 above = 900

So now let’s go for the gold, and really complicate life! Accounts want parentheses to indicate negative numbers, not the negative sign, let’s see what kind of wrench that will throw into the works.

First, we need to go back over to the project settings and change the Negative format

2020-05-16 10_12_55-Pete's Development Desktop

Our first issue is this only helps us if the edit control format is set to “defined by the project”. And we already discussed above the downside to that. But that is ok when we talk about accountants we are generally only talking about currency, so let’s revisit our project settings but this time, use the currency tab

2020-05-16 10_18_03-Pete's Development Desktop

And we change the control type to a Currency. Notice there are two “defined by the project” options, which determine if the currency symbol is displayed or not.

2020-05-16 10_20_37-Pete's Development Desktop

I changed both “defined by the project” so that the currency symbol isn’t displayed and this is the way the edit controls look.

2020-05-16 10_23_55-Pete's Development Desktop

So far so good! But you may have noticed in my code I had some that results said Server Results. That is because I have been riding in this Rodeo long enough to know that the browser and server don’t always act the same!

Our Server results look good

Server Results
Version 1:100-10
Version 2:90
Version 3:90
Version 4:90

But the exact same code in the browser does not!

Browser Results
Version 1:10010
Version 2:110
Version 3:110
Version 4:110

And just for review here is the code

stcBrowserResults = "Browser Results"
stcBrowserResults += [CR] + "Version 1:" + edtNumber1 + edtNumber2
stcBrowserResults += [CR] + "Version 2:" + (edtNumber1 + edtNumber2)

tmpVar is numeric = Val(edtNumber1) + Val(edtNumber2)
stcBrowserResults += [CR] + "Version 3:" + tmpVar
stcBrowserResults += [CR] + "Version 4:" + (Val(edtNumber1) + Val(edtNumber2))

The browser is treating the 10 as a positive number, my theory is that they are likely using standard javaScript to do the Val function and it doesn’t know anything about parentheses as negatives. I thought maybe I could write a quick little browser function to help out but even in browser code, all I get when I look at the edit control is 10. Even something as simple as Info(edtNumber2) returns 10. I was hoping for at least (10) but no such luck. So, unfortunately, the only suggestion I have for you is if you have edit controls with parentheses for negatives, you will need to do all the math on the server-side. I will be submitting this to PCSOFT to see if I can get any further information on this issue.






One thought on “Having trouble with numeric formats in WEBDEV?

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s