The following operators can be used in filters and / or formulas. There are three basic types of operators:
Arithmetic - Used in calculations.
Logical - Used in comparisons and other logical operations.
General - Used for formatting, for grouping operations and for complex functions.
Operator |
Type |
Context |
Operation |
Example |
+ |
Add |
Arithmetic |
Any |
[INV:Price]+.50 - Adds $.50 to the Price of each Inventory item. |
- |
Subtract |
Arithmetic |
Any |
[INV:Price]-.50 - Subtracts $.50 from the Price of each Inventory item. |
/ |
Divide |
Arithmetic |
Any |
[INV:ListPrice]/2 - Divides the List Price by 2. |
* |
Multiply |
Arithmetic |
Any |
[INV:ListPrice] * [INV:QOH] - Multiplies List Price by Quantity on Hand. |
^ |
To The Power Of |
Arithmetic |
Any |
[INV:V1Cost]^2< - Vendor 1 Cost squared (to the power of 2). |
\ |
(Literal) Treat character that follows as a character and not as a symbol or operator |
General |
Any |
[INV:SalePrice] \* [INV:QtyOrdered] - The Sale price, followed by "*", followed by the Quantity Ordered (i.e., "2.99 * 100", as opposed to "299"). |
[] |
[Listname:FieldName] |
General |
Any |
1- Press [Ins] in the Formula field. |
() |
Group |
General |
Any |
([MTX:QOH]+[MTX:QLayaway])*2 - Add Matrix Quantity on Hand to Matrix Layaway Quantity, then multiply the result by 2. |
@ |
Precedes a Function Name |
General |
Any |
@DOW(100200) - Runs the Day of the Week function for 10/02/00. See Filters and Formula Functions. |
& |
Concatenate - Combine strings of text/numbers together, end-to-end |
General |
Any |
[INV:Category]& [INV:Description]& [MTX:Element Codes] - Inventory Category&, followed by a space, followed by Inventory Description&, followed by a space, followed by Matrix Element Codes. |
~ |
Is Empty Field. True if operand it is blank, False if it is not |
Logical |
Single operand on either side |
~[INV:Description] or [INV:Description]~ - Returns True (-1) if the Inventory Description is blank, False (0) if it is not. |
~ |
If first operand is blank, use the second operand |
General |
Operand on either side |
[MTX:Description]~[INV:Description] - Prints the Matrix item Description, if there is one. If there is not, prints the main Inventory item Description. |
* |
Contains |
Logical |
Space after field identifier |
[INV:Description]{space}*Paper - Returns True if the Inventory Description contains the word "Paper", False if it does not. |
* |
Contains |
Logical |
Non-numeric character on one side |
[MTX:Description]{space}*4 - Returns True if the Matrix item Description contains "4" (because the alpha-numeric Description field is non-numeric, even if a particular Description consists entirely of numbers). Compare this to [INV:ListPrice]*95 (without a space) - Multiplies the numeric Inventory List Price field by 95. |
* |
Begins With |
Logical |
Space after field identifier |
[INV:Description]{space}Steno* - Returns True if the Inventory item Description begins with "Steno". Works for numeric fields. |
! |
Does Not contain |
Logical |
Any |
[INV:Description]!Paper - Returns True if the Inventory Description does not include the word "Paper", false if it does. |
> |
Is Greater Than |
Logical |
Any |
[INV:QOH]>100 - Returns True if Inventory Quantity on Hand is greater than 100. If QOH = 100, returns False. |
< |
Is Less Than |
Logical |
Any |
[INV:QOH]<100 - Returns True if Inventory Quantity on Hand is less than 100. If QOH = 100, returns False. |
>= |
Is Greater Than or Equal To |
Logical |
Any |
[INV:QOH]>=100 - Returns True if Inventory Quantity on Hand is greater than or equal to 100. |
<= |
Is Less Than or Equal To |
Logical |
Any |
[INV:QOH]<=100 - Returns True if Inventory Quantity on Hand is less than or equal to 100. |
<> |
Is Not Equal To |
Logical |
Any |
[INV:QOH]<>100 - Returns True if Inventory Quantity on Hand does not equal 100. If QOH = 100, returns False. |
!= |
Is Not Equal To |
Logical |
Any |
[INV:QOH]!= 100 - Returns True if Inventory Quantity on Hand does not equal 100. If QOH = 100, returns False. |
&& |
Must Match Both Criteria |
Logical |
Any |
[INV:QOH]>100&&[INV:Price]<5 - Returns True if Inventory Quantity on Hand is greater than 100 and Price is less than $5.00. |
AND |
Must Match Both Criteria |
Logical |
Space before and after the "AND" |
[INV:QOH]>100 AND [INV:Price]<5 - Returns True if Inventory Quantity on Hand is greater than 100 and Price is less than $5.00. Converts AND to &&. |
|| |
May Match Either Criteria |
Logical |
Any |
[INV:QOH]>100||[INV:Price]<5 - Returns True if either Inventory Quantity on Hand is greater than 100 or Price is less than $5.00. |
OR |
May Match Either Criteria |
Logical |
Space before and after the "OR" |
[INV:QOH]>100 OR [INV:Price]<5> - Returns True if eitherInventory Quantity on Hand is greater than 100 or Price is less than $5.00. Converts OR to ||. |