Dot Formulas

Contents of this Topic: ShowHide

Overview

• Dot Formulas program feature designed to help identify orders that are potentially fraudulent. Refer to Stop Lights., or "stop lights refers to the small red, yellow and green circles which appear on the Process Orders screen, signalling problem orders, or orders that require special handling, etc. Stop lights must be configured by the user and are also referred to as "Dot Formulas".", 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 synonymous with the Microsoft Access Form object or graphical user interface (GUI).

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

Viewing Stop Lights

• When the condition of a given Dot Formula is met, the appropriately colored "stop light" is visible when the Order or Items tab of the of Process Orders screen is selected. In the figure below, all three stop lights are visible simply for the purpose of illustration, which is not typically the case.

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 settings in Stone Edge that enable users to configure the program integrate with their business operations. Stone Edge includes hundreds of System Parameters to do just that. To change a system parameter, go to the Main Menu > Settings > System Functions > Set System Parameters..

2. Select to "left-click" the mouse on an object, usually a row in a table, in the context of Monsoon OMS documentation 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.

5. Save the new parameter.

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

7. 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 The Address Verification System (AVS) is a system used to verify the identity of the person claiming to own the credit card. The system will check the billing address of the credit card provided by the user with the address on file at the credit card company.","transactions","ordernumber=[ordernumber]") & ""=""
Turns on light if the first transaction for the current order has no AVS The Address Verification System (AVS) is a system used to verify the identity of the person claiming to own the credit card. The system will check the billing address of the credit card provided by the user with the address on file at the credit card company. 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".

OMS Fields that can be used in Dot Formulas

Created: 3/22/11

Revised:  5/9/12

Published: 04/14/16