Number formatting using custom fields

Custom field functions and field formatting options can be used to format numeric values in a variety of ways.  For example, here the data contains ‘value’ and ‘appearance’ is at it should be displayed:

value appearance
636000 636,000
20000 20,000
1400000 1.4 Million
6200000 6.2 Million
473000000 473 Million
40000000 40 Million
45000000 45 Million
7100000000 7.1 Billion
2200000000 2.2 Billion
15800000 15.8 Million

 

The field ‘value’ would need to use a ‘custom’ format, to force the use of comma for thousands, like this:

[PRECISION=2]
[THOUSANDS=,]

Then a custom field called “appearance” is created using the following command:

IF(FIELDVAL(value), >, 999999999.999,CONCAT(ROUNDVAL(DIV(FIELDVAL(value),1000000000),2,TRUE), ' Billion'),IF(FIELDVAL(value), >, 999999.999,CONCAT(ROUNDVAL(DIV(FIELDVAL(value),1000000),2,TRUE), ' Million'),FIELDSTR(value)))