Hide NavigationShow Navigation

  • Contents
  • Index
  • Search
 
Show results that include all search words

 

Entering a Price SQL

Stock prices can be calculated using a simple SQL statement, which gives you considerable flexibility in defining your new price. Price SQL statements can be entered in the following situations:

Use the information below to construct a simple SQL formula in the Price SQL field. (For advanced users, you can use any valid SQL formula with any field present 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 Config at Admin > Stock > Price Names.

  • AVECOST

  • STDCOST

Variables

Additional useful variables, prefixed with an @ symbol, are available for use in calculations.

The following variables are available when configuring Sell Prices:

  • @EXCHRATE - the exchange rate used with respect to 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)

The following variables are available when creating price changes:

  • @NEWPRICE

  • @CURRENCY - the current exchange rate for the nominated currency

Examples

Price Scenario

Price SQL

Price should be equivalent to Average Cost Plus 30% Mark-up

AVECOST/0.70

Price should be equivalent to standard cost Plus 30 Margin

STDCOST*1.30

Price should be equivalent to Retail price (Sellprice2) less 10%

SELLPRICE2*0.9

Price Should be equivalent to Sellprice1 in this price name's Foreign Currency

SELLPRICE1/@CURRENCY

Price should be equivalent to Average cost + 33% rounded down and then rounded back up to just under the next dollar, e.g. $5.99.

FLOOR (AVECOST*1.33)+0.99