Decimal And Currency Formats and International Settings

From OpenPetra Wiki
Revision as of 10:16, 7 May 2015 by Alanjfpaterson (talk | contribs) (→‎User Preferences)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

International Settings

Both Windows and Linux support the notion of 'Globalisation' and accordingly both OS's ship with a number of different possible 'Cultures' and their associated settings. My computer is set to 'en-GB', which means English language and British culture. Americans also use the English language but they would typically install the 'en-US' culture because that gives them US English variations. In Europe there are, for example, 'de-DE', 'de-CH' and 'fr-CH' cultures for German-German, Swiss-German and Swiss-French cultures. For the purposes of this wiki page we simply need to note that each culture comes with predefined settings for how numbers and currency amounts should be displayed by the computer. Some cultures use a dot as a decimal separator, while others use a comma.

However you need to understand that these culture format settings are not fixed. They may change with different releases of Windows and they may change because the client wants to use something different. As an example, I may take my computer with me for a six month secondment to Germany. Whereas in Britain I used a dot for decimal separator, I could use Windows Control Panel to change my separator to a comma because for six months I am surrounded by people who all use a comma and I need to see my documents in the same way that they all do. So for me, for that six months my 'en-GB' culture is set to use a comma. When a programmer sets my Culture to be 'en-GB' that is what he/she will get - not the original one. For a second example take the case of Switzerland. Up to Windows 7 the two Swiss cultures had a dot for a decimal separator in non-currency formats. In Windows 8 this was changed by Microsoft to be a comma - for a good reason that official documentation by a government department stated that this was correct. So culture settings are not fixed in stone. They can change over time and users can change them to personalise their computers. There is one special Culture - the Invariant Culture - and, as its name suggests, that can never be changed. Programmers use that for reading and writing text data from/to files but it is not much use for display purposes.

At the end of the day an individual computer is set up to use four different settings which are of relevance to us:

  • A numeric group separator to separate thousands
  • A numeric decimal separator
  • A currency group separator
  • A currency decimal separator

While all four of these characters can be 'discovered' we can never assume that simply by knowing the Culture name we know what they are .

Currencies and Numbers in OpenPetra

OpenPetra fully supports the display of numbers in the user's preferred regional settings. For a specific computer running Windows you can discover what these settings are by opening Control Panel and selecting 'Clock, Language and Region'. From there choose 'Region and Language' and from there 'Change the date, time or number format'. This will bring up a dialog window. On the 'Formats' tab you need to click the 'Additional Settings' button which brings up a further dialog which has a Number and a Currency tab. These are the places where you can see the individual settings for the four items described above.

These are the default settings that OpenPetra will use for the display of money amounts and other fractional decimal amounts such as exchange rates or a person's height in meters.

User Preferences

Although the specific PC regional format is what is used by default in OpenPetra there are some ways in which you can modify the behaviour, in particular for those cultures where the formats are different for currencies and other numbers. This, as stated above, is the case in Switzerland.

In Switzerland the current standard for the Swiss culture has apostrophe/dot for currency amounts but uses space/comma for non-currency numeric amounts like a person's height. Some screens in OpenPetra have money amounts and non-money amounts on the same screen and it can be confusing for data entry to sometimes need to type a dot and other times to type a comma. So OpenPetra provides user preferences for how to handle the display of Finance information and Partner/Personnel/Conference information.

The default behaviour is to use the currency format for all decimal numbers on Finance screens and to use the number format for all decimal numbers on Partner screens (including the few places where money amounts are used). Or you can use currency format throughout - as was the case with the Swiss culture until Windows 8) - or any other permutation. The screenshots show the user preference options for the Partner and Finance screens.

Finance Preferences.png Partner Preferences.png

The screenshot on the left shows the default Finance preferences. The Regional Settings at the time were suitable for Switzerland: apostrophe for thousands and dot (period) for the decimal separator.

The screenshot on the right shows the default Partner preferences, which also apply to Personnel and Conference. The Regional Settings at the time were suitable for Switzerland: space for thousands and comma for the decimal separator.

Notes For Developers

Coding the Correct Style of TextBox

There are a few 'rules' that must be applied to the coding of screens that display decimal information. OpenPetra has two basic types of numeric text box: TTxtNumericTextBox and TTxtCurrencyTextBox. The numeric box displays various formats of numbers including decimal numbers and currency numbers. The Currency TextBox is actually a combination of a Numeric TextBox and a Currency Label. Currency TextBoxes must be used to display amounts of money. Numeric TextBoxes must not be used to display money amounts because the formatting of the text will be wrong in those cultures that have specific currency formats that differ from numeric formats.

So Rule #1 is that if the entity being displayed is an amount of money the YAML definition for the 'txt' control must have the attribute 'Format' set to 'Currency', but if the entity is not an amount of money the 'txt' control must have the 'Format' attribute set to 'Decimal' (or 'PercentDecimal' or 'Decimal(x)').

Rule #2 is the equivalent to Rule #1 but for a grid that is displaying the same data column. The attribute for the column data type is 'Type'. If the column data is money the YAML should specify 'Type=Currency'. If the data is not an amount of money the YAML should specify 'Type=Decimal' or 'Type=Decimal(x)' where 'x' is the number of decimal digits. If your code creates its own grid (rather than defining the grid attributes in YAML) you will need to either use myGrid.AddCurrencyColumn() or myGrid.AddDecimalColumn() as appropriate.

Displaying the Currency Label

As already noted the TTxtCurrencyTextBox is a control that contains a TTxtNumericTextBox and a Label. The label generally displays the three character (usually) code that is associated with the selected currency - examples being 'EUR', 'GBP', 'USD' and so on. When the screen is created the currency code is undefined and the label will display '###'. This indicates to you, as the programmer, that the Currency Text Box property 'CurrencyCode' has not been set. You will need to set a specific CurrencyCode string during screen initialisation or activation. Often the CurrencyCode value will be the current ledger 'BaseCurrency' property. Alternatively it may depend on the highlighted transaction so will be set in the 'ShowDetailsManual' code.

The CurrencyCode is important because it sets the number of decimal places that are displayed in the text box. Most of the 84 currencies that OpenPetra supports use two decimal places - like US dollars and cents or British pounds and pence. But a few significant world currencies such as the Japanese Yen do not have fractional amounts so require no decimal places. Sixteen currencies have no decimals and one has just one decimal (although this currency is no longer legal tender). So it is not correct to assume that a currency will always require two decimal digits and it may be annoying for a user of that currency to find that we expect it.

So Rule #3 is: always try to set the CurrencyCode of a Currency TextBox to the 'correct' value.

It will not be long before, as a programmer, you encounter the need to hide the currency label. This may happen because the screen design demands it or because no single currency describes the data being shown. So there are some additional attributes that can be used to manage the display of the currency label.

  • AlwaysHideLabel: If you want to have the label hidden at all times (by design) set 'AlwaysHideLabel=true' in YAML (or in code). When you do this the label will always be invisible, but a valid CurrencyCode can still apply. This is the best way of hiding the currency label since it leaves you in control of genuinely specifying a valid currency.
  • ShowLabel: If you want to temporarily hide the label, or open the screen initially with an invisible label, set 'ShowLabel=false' in YAML or in code. As soon as you set a value to CurrencyCode the label will become visible again. Remember that before you first set CurrencyCode there will be two decimal digits, which may not be correct. You could in code set a correct value for CurrencyCode and then set ShowLabel=false, although that seems unlikely and using 'AlwaysHideLabel' might be a better programming approach.
  • You can set CurrencyCode to an empty string. Once again this is unlikely to be good programming practice, but, if you do write the code this way, the label will indeed appear to be invisible and because an empty string is an invalid currency the control will use its default of two decimal digits which may not be what is best for all users. It may be appropriate to set CurrencyCode to an empty string in intialisation of your screen and then, as soon as a code is known, apply it. That is effectively the same as setting ShowLabel=false in YAML and then setting the code as soon as it is known.
  • Multiple Currencies: The recommended way to deal with a situation where the appropriate currency code is difficult to determine is to use one of two predefined constants in the Ict.Common.CommonControls namespace:
    • TTxtCurrencyTextBox.CURRENCY_STANDARD_0_DP applies a CurrencyCode that uses no decimal digits
    • TTxtCurrencyTextBox.CURRENCY_STANDARD_2_DP applies a CurrencyCode that uses two decimal digits
    • Use one of the above with the 'AlwaysHideLabel' property set to true.

Here are two good examples of situations where multiple currencies can arise:

  • The first is in GL Batches for the entity known as the 'Hash Code' for the batch. This is a simple sum of all the batch totals but in GL these totals can be in multiple currencies. A Hash Code could be the sum of Euros and Dollars. So, although the concept is certainly an amount of money and the TextBox needs to be a currency TextBox, the CurrencyCode is indeterminate. So we always display Hash Codes with two decimal digits using a CurrencyCode of CURRENCY_STANDARD_2_DP.
  • A second example of a situation where there is no single currency to use for the code is a screen which is cross-ledger such as the Partner Mailings screen. In this case the programmer can use code to discover the ledgers that are available to the current user (ones that he/she has permission to view) and then iterate through the ledgers checking their base currencies. From each base currency the decimal digits can be discovered. The currency code can be set to the generic code that matches the maximum number of digits encountered.

Testing Your Code

If you carefully follow the three rules specified in the previous sections your code will work correctly. But if your development computer is set up to use a standard British English or standard German German culture you will probably never find if you have made a mistake. All of these cultures have the same formats for money and non-money amounts and they all use two decimal digits. So it is easy to write incorrect code that lies un-noticed until it is used in, say, Switzerland or Japan.

At some stage it will be necessary for you to do the following two things:

  • Set your Regional Settings to apostrophe and dot for currencies and space and comma for numbers. This is not as drastic as it sounds for your general work. It won't make any difference to your coding work itself and even programs like Excel will probably continue to work the same. But it will enable you to see that your screens work correctly, especially when you try different User Preferences in OpenPetra. (I have had my computer set up this way for a number of weeks and have not found it gets in the way of my work or what I use my computer for.)
  • Create a ledger in Japanese Yen. Then see what your Finance screens look like!

A Note About NUnit Tests

We have a number of NUnit tests that attempt to check the behaviour of numeric and currency TextBoxes. These tests are quite difficult to write to be able to be aware of all the changes that a developer could make to his/her regional settings. At the time of writing (May 2015) the tests all pass provided that:

  • Your PC uses standard Regional Settings for the en-GB culture, or
  • Your PC uses en-GB with any vales in Regional Settings for the four currency/number thousands/decimal digits settings, or
  • Your PC uses any standard Regional Setting for the German-German culture, or
  • Your PC uses any other Culture, modified or not.

The tests will likely fail if your PC uses de-DE culture that you have modified as described in the previous section.