Database Reference
In-Depth Information
Total Due Calculation #1: Using the If() Function
Most people's first thought would be to use the
If()
function, since the calculation needs to
check
if
one condition is true:
▪ Is the value of the Date Due field earlier than today's date?
The calculation then takes the result of the
If()
function and returns one of two possible res-
ults:
▪ If it's
true
that the due date has passed, then add five percent (.05) of the Total Due to the
value in Total Due.
▪ If it's
not true
that the due date has passed, then display the Total Due normally.
In plainer English, the
If()
condition checks to see if the due date has passed. If so, it adds
five percent to the Total Due amount; if not, it returns the Total Due amount.
The full calculation might look like the following:
If ( // test
Get ( CurrentDate ) > Date Due
and // Calculate the total due here to make sure it's not zero
Sum ( Line Items::Extended Price ) > allPayments ;
// True Result
Sum ( Line Items::Extended Price ) + ( Sum ( Line Items::Extended Price )
* .05 ) ; // add a 5% surcharge if Invoice is past due
// False Result
Sum ( Line Items::Extended Price ) // display the Total Due normally
)
When the due date has passed, the value in your smart Total Due field changes to reflect a
late payment penalty.
NOTE
Since the Total Due field already calculates the due balance, you may be tempted to create a
new
field that calculates five percent of every invoice and then adds that value in only if the invoice is
past due. But that would clutter your database with a superfluous field. Also, it's far better to have
all your math in one place in case your business rules change.
Total Due Calculation #2: Using the Case() Function
Lots of people like the
Case()
function so much that they always use it, even in places
where the
If()
function is perfectly competent. You might choose to use
Case()
if there's
