This blog post is part of a series designed to demystify the process of understanding and writing your own business rules.
Math; most people love it or they hate it, but we can all agree that it’s necessary at times, especially where things like prices are concerned.
Luckily, automation exists and tools like business rules can help you perform simple math functions on your data. You can use rules to add (+), subtract (-), multiply (*), and divide (/). Let’s get to it.
Say you want to have a sale, and you need to take 5% off your retail price and then add the shipping cost that is stored in a custom attribute. To take 5% off the price, you can multiply it 0.95:
($itemretailprice * 0.95) + $shippingcost
This is where that math you learned in grade school comes in handy. Parentheses are great in math functions to make it clear what needs to be done first. For example, in the function above we’re taking 5% off the retail price, then adding the shipping cost to the output of that. You could also add the retail price and shipping costs, then take 5% off the sum of these:
($itemretailprice + $shippingcost) * 0.95
So, if you had a retail price of $100, and a shipping cost of $5.95, the first rule would return:
(100 * 0.95) + 5.95 = 100.95
While the second rule would return:
(100 + 5.95) * 0.95 = 100.6525
It’s important to note that having a price of $100.6525 will result in errors in marketplaces due to all of the digits after the decimal place. Luckily, we have rounding functions to help us out.
Rounding Functions
Today we’re reviewing three rounding functions available in our system; CEILING, FLOOR, and FORMATNUMBER.
CEILING and FLOOR Functions
The CEILING function rounds everything up to the nearest whole number, while FLOOR rounds down to the nearest whole number. So, using the examples above:
CEILING(($itemretailprice + $shippingcost) * 0.95) — Rounds 100.6525 to 101
FLOOR(($itemretailprice + $shippingcost) * 0.95) — Rounds 100.6525 to 100
These functions are super useful for things like weight conversion. Let’s say you have a weight in pounds but want to convert it to kilograms and round up to the nearest whole number:
CEILING($itemweight * 0.4536)
But it’s also a useful function for adjusting prices. Like, for example, to have all of your prices end in .99. You could use the CEILING function to round up, then subtract 0.01:
CEILING(($itemretailprice + $shippingcost) * 0.95) – 0.01
FORMATNUMBER Function
While CEILING and FLOOR are great for rounding to whole numbers, the FORMATNUMBER function can be used to declare how many decimal places you want to round to. The second parameter is of the format “FX,” where X is the number of decimal places you want to round to. Continuing our example:
FORMATNUMBER(($itemretailprice + $shippingcost) * 0.95, “F2”)
The function above will round to 2 decimal places, turning 100.6525 to 100.65.
FORMATNUMBER also rounds to the nearest number instead of always rounding up or down like CEILING and FLOOR, so 100.6525 rounds down to 100.65, but 100.6585 would round to up to 100.66.
Comparison Functions
Business rules can also be used to compare data to determine which value to send. Let’s say in the above example that we also have a field called MAP price and we want to send the higher value between this field and the rule:
FORMATNUMBER(($itemretailprice + $shippingcost) * 0.95, “F2”)
We could use an IF statement and a greater than or less than comparison:
IF($mapprice > FORMATNUMBER(($itemretailprice + $shippingcost) * 0.95, “F2”), $mapprice, FORMATNUMBER(($itemretailprice + $shippingcost) * 0.95, “F2”))
As you can see, this gets complicated quickly, especially if we add in that it should be the higher of the MAP prices, the rule, or the buy-it-now price.
In situations like this, the MAX function, which can take up to 8 inputs, is easier. The one caveat with the MAX function is it will not work with blank values. So if not all of your items have a MAP price, you would want to use an IFBLANK function to pass zero as a default:
MAX(IFBLANK($mapprice, “0”), FORMATNUMBER(($itemretailprice + $shippingcost) * 0.95, “F2”), $itembinprice)
We also have a MIN function to find the lowest value of up to 8 inputs. Like the MAX function, it doesn’t accept blank value inputs, so using IFBLANK with a default that’s higher than any value your other fields would return, such as “999999999”, is best.
That’s all for today’s lesson. If you want to learn more and can’t wait two weeks for our next blog post, feel free to explore more about business rules on our SSC and if you’re struggling with a rule, you can always open a case with support to assist you.
