Formatting rules when building expressions

Solution Properties

Solution ID:

200427112202810

Last Modified Date:

Wed Nov 02 11:39:18 UTC 2022

Taxonomy Path:

Case category//How do I...//setup/create/run a report

Author:

[email protected]

Formatting rules when building expressions

Description

Cause

Resolution

When creating expressions, operators are used to define the comparison between two variables.

+	Add	Adds one numeric or date value to another	AUDIT_SPLIT.NET_AMOUNT + AUDIT_SPLIT.TAX_AMOUNT Adds the transaction net amount to the tax amount to produce the gross amount.
-	Subtract	Deducts one numeric or date value from another	SALES_LEDGER.CREDIT_LIMIT - SALES_LEDGER.BALANCE Subtracts the customer's balance from their credit limit to show how much credit remains.
>	Greater than	Specifies that the value required is higher than the value in the filter.	SALES_LEDGER.BALANCE > 100 Locates and selects all customer accounts where the balance is 100.00000001 or higher.
<	Less than	Specifies that the value required is less than the value in the filter.	SALES_LEDGER.BALANCE < 100 Locates and selects all customer accounts where the balance is 99.9999999 or less, including negative figures.
>=	Greater than or equal to	Specifies that the value required is either the same as, or higher than the stated value.	SALES_LEDGER.BALANCE >= 100 Locates and selects only customer accounts with a balance of 100 or higher.
<=	Less than or equal to	Specifies that the value required is either the same as, or less than the stated value.	SALES_LEDGER.BALANCE <= 0 Locates and selects only customer accounts with a balance of zero or less.
=	Equal to	Specifies that the whole field is equal to a value.	SALES_LEDGER.BALANCE = 0 Locates and selects only customer accounts that have a zero balance.
<>	Not equal to	Specifies that the value required in the field is not equal to the stated value.	SALES_LEDGER.BALANCE <> 0 Locates and selects only customer accounts that do not have a zero balance, this includes those with a negative balance.
LIKE	Like	The like operator is used with alphabetic fields.	SALES_LEDGER.NAME LIKE "ABC" - only sales ledger accounts which exactly match ABC will be picked up. A text value must be surrounded by speech marks. A numeric value does not need to be surrounded.
NOT LIKE	Not like	The not like operator is used with alphabetic fields.	SALES_LEDGER.NAME NOT LIKE "ABC" - only sales ledger accounts which exactly match ABC are excluded.

Linking operator

Linking operators are used to link together two parts of a statement in a certain way.

AND

The AND statement is used to link variables that are not alike, but where both values need to be matched before the item is selected.

SALES_LEDGER.DEPT_NUMBER = 1 AND SALES_LEDGER.BALANCE<>0

Locates and selects only those accounts who are in Department 1 and have a balance not equal to zero.

The OR statement is used to select variables where the status of the variable can be either of the stated selections.

AUDIT_HEADER.TYPE = "SI" OR AUDIT_HEADER.TYPE = "SC"

Locates and selects transactions where the transaction type is either Sales Invoice or a Sales Credit. The AND statement would not work here as a transaction type cannot be both SI and SC at the same time.

Other operators

When creating expressions, operators are used to define the comparison between two variables.

()	Brackets	Specifies the order in which filters are applied	SALES_LEDGER.BALANCE > 0 AND (AUDIT_HEADER.TYPE LIKE "SI" OR AUDIT_HEADER.TYPE LIKE "SC") The brackets seperate the OR statement from the AND statement.
%	Percentage wildcard	Specifies an item of text which can be followed, or preceded, by any other symbols.	SALES_LEDGER.ACCOUNT_REF LIKE "AA%" Locates and selects any customer reference that starts with AA, regardless of the remainder of the text. AA1, AA2 and so on, through to AAZZZZZZ
_	Underscore wildcard	Specifies a single character in a string being replaced by any other symbol.	SALES_LEDGER.ACCOUNT_REF LIKE "A_A" Locates and selects any three character customer reference, where the first and third characters are A, regardless of the second character. A1A, A2A and so on, through to AZA
""	Blanks	References a blank field.	SALES_LEDGER.E_MAIL Like "" Returns any customer accounts with nothing entered into the email field.
= NULL	Equal to	Used with Date fields, where the field is automatically updated by the Program, or is not required to be entered, and specifies that the field is blank.	SALES_LEDGER.LAST_INV_DATE = NULL Locates and selects all accounts that have not yet had an invoice posted to them.
<> NULL	Not equal to	Used with Date fields, where the field is automatically updated by the program, or is not required to be entered, and specifies that the field is not blank.	SALES_LEDGER.LAST_INV_DATE <> NULL. Locates and selects all sales accounts who have had an invoiced posted to them.
IN	Like	Used where several sets of data are required and they are not in a logical range or sequence.	SALES_LEDGER.ACCOUNT_REF IN ("AAA","JJJ","LLL","MMM","YYY","888") Locates and selects the Sales Ledger accounts AAA, JJJ, LLL, MMM, YYY and 888 only.
NOT IN	Not like	Used where certain sets of data that are not in a logical range or sequence, need to be excluded.	SALES_LEDGER.ACCOUNT_REF NOT IN ("AAA","JJJ","KKK","MMM","YYY","888") Will exclude Sales Ledger accounts AAA, JJJ, KKK, MMM, YYY and 888.

[BCB:19:UK - Sales message :ECB]

Steps to duplicate