The chapters so far asked whether a value gets in, whether it gets across, and whether it gets recorded. This post asks a different question: what the value that got in actually stands for.
The quantity, unit price, amount, discount rate and exchange rate on a document look almost identical in the TableSchema definition, each one a decimal number with a precision. The real difference lies in three places: how many decimal places to show, whether to round when the value is written, and who has the say over the number of places. Most systems hand these three to the table's DDL, the format strings on screen, and the Math.Round calls in the business logic, and each of the three gets changed on its own. To be maintainable, the three have to be governed by one mechanism.
This post covers:
- What a numeric field is marked with is its meaning, not its number of decimal places
- How the decimal places of an amount depend on the currency
- How the decimal places of a quantity or a weight depend on the unit of measure
- Whether detail lines are rounded before they are added up or after, and why that order decides whether the total is right
1. Mark the meaning, not the decimal places
Once a table declares decimal(19,4), you know the column holds at most four decimal places. What you do not know is how many it should show, whether a calculated value should be rounded, or, when a customer asks for unit prices to show only two places, whether to change this column or somewhere else.
The framework's approach is to declare a semantic kind, NumberKind, on a field in FormSchema (a FormField), and let it decide how many places to keep and whether to round:
NumberKind |
Meaning | Rounding strategy | Where the places come from |
|---|---|---|---|
Quantity |
Quantity | Round |
The unit of measure |
Weight |
Weight | Round |
The unit of measure |
Amount |
Amount | Round |
The currency |
Percent |
Percentage | Round |
The company |
UnitPrice |
Unit price | Preserve |
The company |
Cost |
Cost | Preserve |
The company |
ExchangeRate |
Exchange rate | Preserve |
Fixed by the system |
Numbers that come out of a calculation are rounded; numbers that go into one are not. An amount is printed on the document, added into totals and posted to the books, so it has to be rounded to the places it is meant to have. A unit price, a cost or an exchange rate is there to be multiplied, and rounding it carries the error into every calculation that follows. So the framework's rounding method, RoundByKind, hands those three back unchanged. A unit price shown with four places is only for people to read, and the framework does not round it to four places because of that.
A field without a NumberKind gets no format and no rounding from the framework, and leaving it off raises no error.
Whether the places can be worked out in advance depends on whether they change while a user is working on a document. The places for a percentage, a unit price and a cost are set by the company, and an exchange rate is fixed by the framework at five. None of these four change, so their formats are worked out when the definition is delivered, and the front end prints by them.
The places for an amount, a quantity or a weight do change: an amount follows its currency, and a quantity or a weight follows its unit. Those are the next two sections.
2. The currency decides an amount's decimal places
A currency's decimal places are a property of the currency, not company policy: the Japanese yen has no subunit, the US dollar has two places, and the Bahraini dinar has three. ISO 4217 sets this, and it does not change from one company to the next. So currency places live in a master shared by the whole system, and no individual company can touch it. What a company does decide is which currency is its base currency, and whether the final amount payable is aligned once more to a cash rounding unit (more on that later).
That master is a definition file, kept in the same place as the form definitions and served through the same cache (The Purpose and Classification of Definition Files showed that all thirteen definition types work this way), and it is sent to the front end along with them (excerpt from the CurrencySettings.xml the framework ships):
<CurrencyItem Code="USD" Numeric="840" Rounding="0.01" Symbol="$" Name="US Dollar" />
<CurrencyItem Code="JPY" Numeric="392" Rounding="1" Symbol="¥" Name="Japanese Yen" />
<CurrencyItem Code="BHD" Numeric="048" Rounding="0.001" Symbol="BD" Name="Bahraini Dinar" />
What it stores is not the number of places but the smallest natural unit, and the places are derived from that (0.01 is two places, 1 is zero). The smallest unit is what a currency actually exists in, and cash rounding, which comes later, uses it as well.
An amount's places cannot be worked out in advance, because a user may switch the currency on screen from US dollars to yen. So the definition does not write down an amount's places, only which column holds its currency. If none is named, the document's currency column is used, and the places are worked out once that column has a value.
That means rows in the same column can each have different places. Adding US dollars to yen means nothing, so the column footer shows a total only when the whole column is in a single currency. The column converted into the company's base currency has only one currency, so it always adds up.
When the currency column is empty, the company's base currency is used. The base currency is a required company setting, and if it is not set, the framework throws an exception when it works out an amount rather than falling back to a default number of places.
3. The unit decides the decimal places of a quantity or a weight
Quantities follow the same reasoning: entered in pieces, a quantity should have no decimals, and entered in kilograms it can be 0.375. Whether it can have decimals depends on which unit that row was entered in, not on the field.
The unit master is also a definition file shared by the whole system, and each unit's places are a property of that unit, not the company's to decide (excerpt from the UnitSettings.xml the framework ships):
<UnitItem Code="PCS" Decimals="0" Dimension="count" Name="Pieces" />
<UnitItem Code="KG" Decimals="3" Dimension="weight" Name="Kilogram" />
The two masters differ in how they store it. A unit of measure stores its places directly, while a currency stores its smallest unit and the places are derived from it, because cash rounding needs that smallest unit.
The approach matches the one for amounts: a quantity field only states which column holds its unit, and its places are looked up in the unit master by that column's value. The difference is what happens when the value is missing:
| Where the code comes from | When that column is empty | Default at the company level | |
|---|---|---|---|
| Amount | The currency column that the amount field names, or the document's currency column if it names none | The company's base currency is used | The base currency, which is required |
| Quantity / weight | The unit column that the quantity field names | There is nothing to stand in for it | No default unit |
A company has a base currency, yet it will not have a default unit, because the detail lines of a single document can perfectly well be one in kilograms and the next in cases.
The principle is this: a field marked Quantity or Weight must name a unit column, and a number that needs no unit is an ordinary numeric field with no NumberKind.
4. Round then add, or add then round
Three detail lines, each one piece at a unit price of 10.3333. Intuitively, adding at full precision and rounding once at the end is more accurate. Running it through the expressions in the case study's FormSchema:
| Approach | Each row shows | Total |
|---|---|---|
| Round first, then add | 10.33 | 30.99 |
| Add first, then round | 10.33 | 31.00 |
A cent apart, and the wrong one is the one that looks more accurate. The document shows three lines of 10.33, which a user adds up to 30.99, while the master total says 31.00, so the document does not agree with itself. An ERP system requires the master total to equal the sum of the detail lines without being a single cent off, because documents get printed, reconciled and audited.
The framework's approach is to round each row as soon as it has been calculated, so the total only ever receives rounded values and the two agree as a matter of course. This rule is called round-then-sum. It is the only place the framework rounds automatically, and numbers a user types in are not rounded.
Rounding comes in two layers
Each detail row is rounded to the natural decimal places of its currency, and that layer is the same across the whole system. The final amount payable on a document can take one more layer: alignment to a cash rounding unit set by the company. The Swiss franc, for example, has no coin for a single cent, so a company can set 0.05, and then 12.34 is collected as 12.35 and 12.32 as 12.30.
The two layers are different in nature. The first removes precision that does not exist. The second deliberately creates a difference, and the extra cent collected or the two cents short both have to be posted to a rounding account. So at this layer the framework returns only the amount payable, and the caller works out and records the difference itself.
The database column is capacity, not decimal places
Table columns have a precision too, but that is a capacity limit: it only says how many places fit, and has nothing to do with how many places the value should show or be rounded to. The same amount type is created as decimal(19,4), numeric(19,4), DECIMAL(19,4), NUMBER(19,4) and NUMERIC(19,4) in the five databases, written differently with the same numbers.
The limit is deliberately set well above the business places. Otherwise every additional company and every additional currency would mean another ALTER TABLE, and that could not be run in operation.
Places beyond the capacity are not currently handled by the framework. When a value is sent to the database, it carries only the value, the type, the length and whether NULL is allowed, not a precision, and before the write it is not rounded to the column's capacity either.
So what happens to the excess is up to each database. Write 1.234567 into a column with four decimal places and SQLite keeps it as it is (measured), while the other databases trim it by their own rules, so the same data stored in different databases ends up with different precision.
A database can limit the places with a CHECK constraint, but that only rejects data that does not fit, and it does not round. Storing a value at the right number of places has to be handled by the framework before the write, and that step has not been built yet.
Back to Northwind
Only two fields in the case study's FormSchema are marked with a meaning, and they happen to fall one on each side (excerpt from Order.FormSchema.xml):
<FormField FieldName="unit_price" DbType="Currency" NumberKind="UnitPrice" />
<FormField FieldName="amount" DbType="Currency" NumberKind="Amount" ReadOnly="true"
ValueExpression="quantity * unit_price * (1 - discount)" />
The first is the unit price, whose places are set by the company. When the definition is delivered, unit_price receives a format with four places, but the column in the case study's hand-written layout carries no marker, so the screen still shows the raw value. Enter 10.33335, and after a round of evaluation it is still 10.33335, not rounded.
The second is the computed field from Declarative Business Rules and Computed Fields, and it belongs to the currency group. The case study has no currency column, so the company's base currency, USD, is used, two places are looked up in the currency master, and the framework rounds to two places once it has evaluated the value. When the definition is delivered, the format for amount is left empty and it names no currency column either; the two places on screen come from the marker on that layout column, worked out from the base currency. Neither multiple currencies nor units of measure can be demonstrated, and ExchangeRate goes unused.
The master total is not in the definition. OrderBO adds up the amount of the detail lines before saving, and those amount values have already been rounded row by row by the framework before they are added, so this satisfies round-then-sum: the framework rounds, and the application adds. That post on business rules said a sum across rows is something expressions cannot currently write. The rounding half is already done by the framework, and all that remains is the adding.
In closing
From the semantic marker on a field to the capacity of a table column, this post has been answering one question: who decides how many decimal places a number has.
The answer is spread across different places, and deliberately so. Unit prices, costs, percentages and exchange rates are set by the company or the framework, because that is policy. Amounts are decided by the currency, and quantities and weights by the unit, because those are properties of the currency and the unit themselves. Whether the amount payable is aligned once more is decided by the company, because that is collection policy. How many places a column can hold is decided by the table, because that is only capacity.
Mixing these up is where things go wrong. Tie the places to the table, and a new currency means changing the structure. Tie them to the display, and unit prices and exchange rates lose precision to rounding. Round after adding, and the master total no longer matches the detail lines.
Beyond the places, there is the value itself. The framework automatically rounds only values it has calculated itself, and the unit prices, discounts and amounts a user types in are left untouched. Rounding is a rule for calculated results, not a correction of what the user entered; the moment the framework changes a number on a user's behalf, the books gain a difference that nobody can say who made.
The next post, The Semantic Boundaries Between Date, DateTime and Time, turns to another kind of data. Numbers at least all look alike, but time does not: a single CLR type holds several kinds of value with different meanings, and the type system cannot tell them apart.
📚 Part of the thirty-post series ERP Architect's Notes: Designing a Definition-Driven Framework
📘 Original article on HackMD, in Traditional Chinese:
👉 https://hackmd.io/@jeff377/day-26-numeric-semantics
📢 You are welcome to republish this, please credit the source.
📬 Follow for more engineering notes:
Facebook | HackMD | GitHub | NuGet
Top comments (0)