Apr 18

Differences between Total and TotalRate

This post was written by BJ Basañes

 4 Comments »

Problem

If you’ll look in the db schema of IS, in some tables you will find numeric values saved in 2 fields that are similarly named but with the other one postfixed with “Rate”. For this article, we’ll use the CustomerSalesOrder table as example. This table is the main table where online/storefront orders are saved. This table contains the fields Subtotal, SubtotalRate, Total, and TotalRate among others. Most often I noticed that the values for Subtotal and SubtotalRate are always the same. This is also true for the Total and TotalRate. For example:

  • Customer orders items that sums up to $300.00.
    • Subtotal = 300.00
    • SubtotalRate = 300.00
  • Adding shipping charge of $25.00.
    • Total = 325.00
    • TotalRate = 325.00

Looking up the database documentation in connectedbusiness.com:

Total
The total balance of the customer in a transaction
TotalRate
The total base balance of the customer in a transaction

Well… that didn’t help. The descriptions were too simple for my taste. It’s still not clear or descriptive enough.

Tests

I suspected that the difference of the common field should be in the currency rates. So I did a test by using a customer from France. Logging in with this customer would instruct the website to show EURO currency instead of Dollars. So, let’s say I ordered the same two items as the example above. We’ll also assume that the shipping charge is the same. The results would be:

  • Subtotal = 300.00, SubtotalRate = 435.18
  • Freight = 25.00, FreightRate = 36.26
  • Total = 325, TotalRate = 471.44

Note that the numbers are rounded off to two decimal places.

Conclusions

Based on the results above, we can compute the ratio between the field pairs. The ratio would be 1.4506 for all three pairs. This number is the exchange rate for Euro (with Dollars being the home currency). With these, we can deduce that the fields postfixed with “Rate” are the numerical equivalents of the customer’s currency.

Analyzing this was very important because we were involved with integrations with 3rd party services such as preCharge and Cardinal Commerce and they require us to acquire the total amounts of orders. We were perplexed on what field to use.