Formating Numbers (in Tables)

[dmdodd]

Newbie/Quick question.

I have a Table Component and want the numbers being displayed in it to be:

(a) center in the cell, and

( have a format where there are commas every 3 numbers. It gives you the option to set the number of decimal places, but not formatting. I searched for a while concerning formatting but only found the 'datetime format' info.

Thanks for a great product and great support.

FYI - I just upgraded to Lite from Express :-)

~Daniel

[AzeoTech]

a) there is a column in the table properties called "align"

you need to come up with an expression that formats with commas. There is no built in expression, largely because commas in numbers tend to be more of a financial sector thing and not a scientific / industrial thing.

[dmdodd]

Any chance of a quick "hint" as to what the expression would look like to format the number so every 3 numbers up from the decimal there's a comma?

Personally, I'm amazed not more people have asked for it, when ever there's a number of 5 digits or more, I can't stand not having commas in place.

Thanks again,

Dan

[AzeoTech]

Its actually not particularly easy. You can extract the value of any hundreds section using this sort of pattern:

floor((x % 1e6) / 1e3)

in this example its the thousands. For millions, change it to 1e9 and 1e6, etc. For the hundreds, tens, etc, just do x % 1e3. The problem is knowing when and where to do it. For example, if you do:

"" + floor((x % 1e12)/1e9) + "," + floor((x % 1e9)/1e6) + "," + floor((x % 1e6)/1e3) + "," + format("%.3f", x % 1e3)

It works fine as long as the number is over 1 billion. Under a billion and you get 0, in front. You can use an iif() for that, but then you have issues if any particular segment has preceeding 0's. You can get around that by doing format("%03d"), but then if you have a value like 10,000, you'll get 010,000, so again you could use an iif(). So, yes, the expression could be written out fully, but it'd be pretty long. This, I believe, is it and will work up to, but not including 1 billion:

iif(x > 1e6,"" + floor((x % 1e9)/1e6) + ",", "") + iif(x > 1e3, iif(x > 1e6, format("%03d", floor((x % 1e6)/1e3)),"" + floor((x % 1e6)/1e3)) + ",", "") + iif(x > 1e3, format("%03d", x % 1e3) + mid(format("%.3f", x % 1),1,5), format("%.3f",x%1e3))

If you need to cover the billions, just repeat the first iif() and put it in the front, increasing all the 1eX by 3 (i.e. 1e3 becomes 1e6, etc.)

If you look carefully, you can actually simplify this quite a bit, but I'll leave that to you. Truthfully, DAQFactory will be pretty fast evaluating even something as long as this so its probably not worth your time, or making it more complex by optimizing it, considering the refresh rate of the screen.

A couple things:

1) I use a trick of starting with an empty string and adding to it which causes DAQFactory to automatically convert all the numbers in the expression to a string. I do this anywhere I have an expression that starts with a number and adds "," to it. Remember, each part of an iif() is its own expression.

2) there is no %03f, only %03d so in the case where I have to add preceeding 0's in the tens/hundreds, I also have to add the decimal, which I do by doing x%1 and formatting that with %.3f which gives me 3 decimals. Since it will also make it 0.xxx I use the mid() function to strip that starting zero. If I don't need the preceeding 0's (which only happens if the value is < 1000.,

[dmdodd]

Wow, that was way more coding that I'd have expected, good work though... it worked perfectly.

The only thing I noticed was that the decimal places option in the properties box for these screen components looses functionality, and I only needed 1 dp so i merely changed the %.3f to %.1f and all seems well.

Just a shame I will have to put this fairly lengthy code in every screen component's expression that I want to format like this, unless there's a way to format EVERY number displayed on screen this way?

Great work though once again

[AzeoTech]

Do a search for algorithms to do this in other languages like C and you'll see they are even more complicated than what I presented.

Precision only works on numbers. Once you start adding commas, its a string, not a number.

There are two ways to avoid typing in the code all the time. The easiest is to simply make the formula into a function. Create a sequence, call it addCommas, then put this in it:

function addCommas(x)
   return(iif(x > 1e6,"" + floor((x % 1e9)/1e6) + ",", "") + iif(x > 1e3, iif(x > 1e6, format("%03d", floor((x % 1e6)/1e3)),"" + floor((x % 1e6)/1e3)) + ",", "") + iif(x > 1e3, format("%03d", x % 1e3) + mid(format("%.3f", x % 1),1,5), format("%.3f",x%1e3)))

Then anywhere you want to display with commas, put:

addCommas(myChannel[0])

[dmdodd]

Awesome, you guys(/gals) rock!

Gracias!