[FIXED] Web2py Number Formatting for Thousands

Issue

I'm sort of new to Web2py. I have a system that's working just fine, but I want to make an improvement regarding visualization. There's a couple of fields that use numbers (defined as double in their respective define_table methods) to represent currency, but I want them to also show with a separator for thousands, such as 183,403,293.34. I checked some documentation, but I couldn't find a direct way to handle this form of customization, though I could be missing something.

Any suggestions regarding this? Cheers!

Solution

First, if representing currency, you should use the decimal field type rather than double (some calculations using double values may yield incorrect results due to the use of floating point representations internally). However, if using SQLite, there is no distinction between decimal and double, so in that case, you might want to multiply all values by 100 and instead store integers.

In any case, to display a given numeric value with thousands separators in Python, you can do:

'{:,}'.format(myvalue)

For more details, see https://stackoverflow.com/a/10742904/440323 and https://stackoverflow.com/a/21208495/440323.

If you are using the values via web2py functionality that makes use of the field's represent function (e.g., the grid or the .render() method), you can define a custom represent function, such as:

Field('amount', 'decimal(12, 2)',
      represent=lambda v, r: '{:,}'.format(v) if v is not None else '')

Answered By - Anthony