Dot Formulas

Overview

• Dot Formulas, or “stop lights”, are used to flag problem orders or those that require special handling.
• The program supports three user-defined dot formulas, each corresponding to a red, green, or yellow “stop light” at the Process Orders screen.
• Defining a dot formula requires some basic knowledge of Structured Query Language (SQL). See SQL Formula Basics later in the article for some examples. 

Programming Stop Lights

1. 1. Main Menu -> Gear Icon (Settings).
   2. System Functions -> Set System Parameters.
   3. Parameter Group = Program (Or use the search box and type “dot”).
   4. Parameter = GreenDotFormula, RedDotFormula, YellowDotFormula.
   5. .For each parameter, enter a True/False formula following the below
      1. True -> Enables light.
      2. False -> Disables light.
   6. Save parameter.
   7. Close and Reopen Stone Edge for changes to take effect.

Viewing Stop Lights

• When the condition of a given Dot Formula is met, -> appropriately colored “stop light” is visible on the Process Orders screen: order tab and items tab.
  NOTE: The screenshot below shows all 3 colors active simultaneously, though normally, this is not the case and depends on the formula defining each stoplight.

SQL Formula Basics with Stone Edge

• Because Stone Edge uses field references, only the “WHERE” part of the statement is needed
  • Stone Edge field references must be in [square brackets]
• Commonly used in statements:
  • AND or & -> Both statements must be true
  • OR or! -> Only 1 statement needs to be true
  • “quotes” -> delimited identifier
  • “” double quotes”” -> contains a statement
  • * -> “Wildcard” represents 0 or more characters
  • ? -> “Single Character Wildcard” represents a character
  • dlookup -> DLookup Function (microsoft.com) (specific to access)
• More info on SQL Formulas: SQL Tutorial (w3schools.com)

Examples of SQL Formulas

[FinalGrandTotal]>100
Turns on light if total price of order is more than $100)

[SourceOrderNumber] & “”>””
Turns on light if the current order was imported from a Web store (see next example for Americart and Virtual Cart stores).

[SourceOrderID] & “”>””
Turns on light if the current order was imported from an Americart or Virtual Cart store.

[Surcharge] > 0 AND [BalanceDue] > 0
Turns on light if the current order has any surcharges and also has a balance due.

Year([OrderDate])=Year(Date) AND Month([OrderDate])=Month(Date) AND Day([OrderDate]) between 10 and 20 and [ShipCompany] & “”>””
Turns on light if the order date is between the 10th and 20th of the current month, and there is anything entered in the Ship To Company field.

dlookup(“avs”,”transactions”,”ordernumber=[ordernumber]”) & “”=””
Turns on light if the first transaction for the current order has no AVS data.

dlookup(“avs”,”transactions”,”ordernumber=[ordernumber]”)&””>”” AND instr(“BEGNRSU”,dlookup(“avs”,”transactions”,”ordernumber=[ordernumber]”)&””)>0
Turns on light if the first transaction for the current order has AVS data, and the AVS response can be found within the string of characters “BEGNRSU”.

SEOM Fields that can be used in Dot Formulas