Entering a Price SQL
You can calculate stock prices using a SQL statement. You can enter a price SQL statement when:
- Setting up Sell Prices in the Exo Business Configurator.
- Creating a price change or a scheduled price change.
Use the information below to construct a simple SQL formula in the Price SQL field. Advanced users you can use any valid SQL formula with any field in the STOCK_ITEM table, including Extra Fields.)
Fieldnames
Commonly used fieldnames are:
-
SELLPRICE1 ... SELLPRICE10 - these ten fields correspond to the ten price names set up in Exo Business Configurator under Admin > Stock > Price Names.
-
AVECOST
-
STDCOST
Variables
You can use these variables when configuring Sell Prices:
-
@EXCHRATE - the exchange rate used with the currency selected for the price.
-
@TAXRATE - the tax rate entered for the Sales GST property on the Details 2 tab.
Examples
Price Scenario |
Price SQL |
Calculate a price by factoring another price |
SELPRICE2 * 1.3 |
Calculate foreign price from local cost |
SELLPRICE2 * @EXCHRATE |
Calculate GST inclusive price from GST exclusive cost |
STDCOST * ((100+@TAXRATE)/100) |
Calculate GST exclusive price from GST inclusive price |
SELLPRICE2 * ((100-@TAXRATE)/100) |
Use these variables when creating price changes:
-
@NEWPRICE
-
@CURRENCY - the current exchange rate for the nominated currency
Examples
Price Scenario |
Price SQL |
Price should equal Average Cost Plus 30% Mark-up |
AVECOST/0.70 |
Price should equal standard cost Plus 30 Margin |
STDCOST*1.30 |
Price should equal Retail price (Sellprice2) less 10% |
SELLPRICE2*0.9 |
Price Should equal Sellprice1 in this price name's Foreign Currency |
SELLPRICE1/@CURRENCY |
Price should equal Average cost + 33% rounded down and then rounded back up to just under the next dollar — for example, $5.99. |
FLOOR (AVECOST*1.33)+0.99 |