MYOB Exo Business

Hide NavigationShow Navigation

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

 

Entering a Price SQL

You can calculate stock prices using a SQL statement. You can enter a price SQL statement when:

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