Version 7.5
Contents of this Topic: Show
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 of which corresponds 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 Appendix A for some examples or search the Stone Edge User Forum to see how other users have implemented dot formulas.
Process Orders: Order Tab - the conditions of all three dot formulas (stop lights) are met in this example
Go to Main Menu>Settings>System Functions>Set System Parameters.
Select one of the following parameters: GreenDotFormula, RedDotFormula, or YellowDotFormula.
Enter a formula that evaluates to TRUE or FALSE condition. TRUE enables a light; FALSE disables it.
Be sure to put field references that are used by a formula in square brackets, for example, [FinalGrandTotal], as shown in the figure below. In this example, orders with a grand total greater than $100.00 meet this condition and display the green "stop light" at Process Orders.
Save the new parameter.
Optional: Select one of the other dot formula parameters and repeat steps 3 - 5.
Exit and re-open the program to have the changes take effect.
[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".
OrderNumber |
CCInfo | RevisedCouponDiscount |
SourceOrderNumber | OrderInst | NoteToCustomer |
OrderDate | Comments | Taxable |
CustomerID | ProductTotal | TaxNumber |
Name | TaxTotal | ActualShippedWeight |
Company | ShippingTotal | DiscountType |
GrandTotal | DiscountPercent | |
Address | Shipping | DropShipsAny |
Address2 | Discount | ApprovedBy |
City | RevisedDiscount | LocalSortText1 |
State | Coupon | LocalSortText2 |
Zip | CouponDiscount | LocalSortText3 |
Country | Surcharge | LocalSortText4 |
Phone | RevisedSurcharge | LocalSortText5 |
ShipName | RefName | LocalSortInteger1 |
ShipCompany | GiftMessage | LocalSortInteger2 |
ShipAddress | Approved | LocalSortInteger3 |
ShipAddress2 | Cancelled | LocalSortInteger4 |
ShipCity | FinalProductTotal | LocalSortInteger5 |
ShipState | FinalTaxTotal | LocalSortDate1 |
ShipZip | FinalShippingTotal | LocalSortDate2 |
ShipCountry | FinalGrandTotal | LocalSortDate3 |
ShipPhone | BalanceDue | LocalSortDate4 |
PayType | ReviewReason | LocalSortDate5 |
pay1 | BackOrdersToFill | TaxByPOS |
pay2 | ShippedWeight | SourceOrderID |
pay3 | FinalTaxRate | |
pay4 | Notes | FraudScore |
Created: 3/22/11
Revised: 5/9/12
Published: 04/14/16