Sample formulas for custom columns

Example: Calculating the profit margin

This is a very simple formula used to calculate the profit margin.

[Price] - [Cost]

In the Advanced Editor, this can be accomplished like this:

• Under Operands, expand the Controls branch to locate and expand the collection that contains the Price IDO property.
• Double-click the Price property.
• The property displays in the Enter Formula/Expression field like this: [Price]
• Click the minus ( – ) operator button.
• In the Operands field, locate and double-click the Cost IDO property.

That gives you the formula as presented.

Example: Calculating the profit margin percent

This expression includes a conditional statement to make sure that we do not attempt to divide by zero (in cases where the Price is zero).

IF( [Price] <> 0, ([Price] - [Cost]) / [Price] * 100, 0)

Roughly translated, this formula says: Check the Price. If it is not zero, then subtract the Cost from the Price and divide the result by the Price and multiply by 100. Otherwise, if the Price is zero, then set the value to zero (0).

Example: Calculating a summary for the profit margin percent

This expression calculates the total of all prices and the total of all costs, and if the total of all prices is not zero, calculates a summary of the profit margin percent for the totals.

IF (SUM([Price]) <> 0, (SUM([Price]) - SUM([Cost])) / SUM([Price]) * 100, 0)

This expression also uses a conditional IF statement to ensure that we are not dividing by zero.

Example: Using dates and times in expressions

This sample expression determines the age of an invoice and then uses it to create an "aging bucket".

DATEDIFF( "d", [InvDate], TODAY() )

IF ([Age] > 90, [DerTcAmtBalance], 0)

Example: Manipulating strings

This sample expression finds and returns the first two characters of each item name.

LEFT( [Item], 2 )

This type of manipulation can be useful if you want to determine items with the same prefixes, for example. You could then use the results of this expression to further manipulate the data that has been returned.