Dot Formulas

Contents of this Topic: Show

Overview

Viewing Stop Lights

Process Orders: Order Tab - the conditions of all three dot formulas (stop lights) are met in this example  

Programming Stop Lights

  1. Go to Main Menu>Settings>System Functions>Set System Parameters.

  2. Select one of the following parameters: GreenDotFormula, RedDotFormula, or YellowDotFormula.

  3. Enter a formula that evaluates to TRUE or FALSE condition. TRUE enables a light; FALSE disables it.

  4. 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.

 

  1. Save the new parameter.

  2. Optional: Select one of the other dot formula parameters and repeat steps 3 - 5.

  3. Exit and re-open the program to have the changes take effect.

Appendix A

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

OrderNumber

CCInfo RevisedCouponDiscount
SourceOrderNumber OrderInst NoteToCustomer
OrderDate Comments Taxable
CustomerID ProductTotal TaxNumber
Name TaxTotal ActualShippedWeight
Company ShippingTotal DiscountType
Email 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 Email
pay4 Notes FraudScore

 

 

Created: 3/22/11

Revised:  5/9/12

Published: 04/14/16