Herb 50 Accounts example: Expressions with operators

Use a plus sign to add values.

To get a payment date that is 30 days after an invoice was sent:

INVOICE.INVOICE_DATE+30

This example can be used to display the value on the report or as part of another expression.

Use a minus sign to subtract values.

To calculate the difference between gross and net amounts on an invoice:

INVOICE.INVOICE_GROSS-INVOICE.INVOICE_NET

This expression can be used to display the value on the report or as part of another expression.

Use an asterisk to multiply values.

To calculate what percentage of the sales price the unit price is:

INVOICE_ITEM.UNIT_PRICE/STOCK.SALES_PRICE*100

This expression can be used to display the value on the report or as part of another expression.

Use a forward slash to divide values. Divide. For example, 5/2 = 2.5.

Use ^ for exponential functions; for example, 3^2 means 3 to the power of 2, which results in 9.

This expression can be used to display the value on the report or as part of another expression.

Use a percent symbol to calculate percentages.

If you calculate 5%2, you get 1.

This example can be used to display the value on the report or as part of another expression.

Use a logical NOT to test is a statement is false.

To check if invoice types do not contain "SOP":

if NOT Contains(INVOICE.INVOICE_TYPE, "SOP") then
TextStyle->Color:=NamedColor("Red");

This example is an advanced conditional formatting expression.

Use LIKE to test if a string contains information.

To only include information for the customer with the account reference "Johnson":

SALES_LEDGER.ACCOUNT_REF LIKE "JOHNSON"

This example can be used as a filter expression or for conditional formatting.

Use NOT LIKE to test if a string does not contain information..

To exclude information for the customer with the account reference "Johnson":

SALES_LEDGER.ACCOUNT_REF NOT LIKE "Johnson"

This example can be used as a filter expression or for conditional formatting.

Use a logical AND to test if all statements are true.

Customers with both a turnover for the year to date of less than £1000, and a prior year balance that is greater than £2500:

SALES_LEDGER.TURNOVER_YTD<1000 AND SALES_LEDGER.PRIOR_YEAR>2500

This example can be used as a filter expression or for conditional formatting.

Use a logical OR to test if at least one or another statement is true.

To include customers with either: a turnover for the year to date of less than £1000; or a prior year balance that is greater than £2500:

SALES_LEDGER.TURNOVER_YTD<1000 OR SALES_LEDGER.PRIOR_YEAR>2500

This example can be used as a filter expression or for conditional formatting.

Use brackets (parentheses) to group parts of the expression, to define the order in which it is evaluated.

To include customers with a turnover between £1000 - £2000, and whose prior year balance is greater than £2500:

(SALES_LEDGER.TURNOVER_YTD>1000 AND SALES_LEDGER.TURNOVER_YTD<2000) 
 AND SALES_LEDGER.PRIOR_YEAR>2500

This example can be used as a filter expression or for conditional formatting.

Use curly brackets (braces) to enclose multiple lines in an expression.

If Contains(SALES_LEDGER.NAME, "Johnson") then
{
  TextStyle->Color:=NamedColor("Red");
  TextStyle->Underline:=1;
}

This example is an advanced conditional formatting expression.

Begin is a text equivalent of {.

End is the text equivalent of }.

Use a comma to separate items.

Use a semi-colon to separate multiple lines.

If Contains(SALES_LEDGER.NAME, "Johnson") then
Begin
  TextStyle->Color:=NamedColor("Red");
  TextStyle->Underline:=1;
End

This example is an advanced conditional formatting expression.

Set a variable to be true; for example, to set the word wrap property to true:

WordWrap:=True

Set a variable to be false; for example, to set the word wrap property to false:

WordWrap:=False

To construct a conditional statement, use IF, THEN, ELSE.

If sales ledger names contain "Johnson", they appear red and with a shadow of depth 1. Otherwise the names appear blue:

If Contains(SALES_LEDGER.NAME,"Johnson") then
  Begin
    TextStyle->Color:=NamedColor("Red");
    Formatting->Border->DropShadowDepth:=1;
  End
Else
  TextStyle->Color:=NamedColor("Blue")

This example is an advanced conditional formatting expression.

Use SWITCH to construct a conditional statement.

To set the colour of the country code according to its value; red for Germany (DE), blue for France (FR), and green for any other country.

Switch COUNTRY_CODE.CODE
Case "DE": 
 TextStyle->Color:=NamedColor("Red")
Case "FR": 
 TextStyle->Color:=NamedColor("Blue")
Default:
 Begin
 TextStyle->Color:=NamedColor("Green");
 End

This example is an advanced conditional formatting expression.

Compare if a value is greater than another value.

Customers with a turnover for the year to date that is greater than £1000:

SALES_LEDGER.TURNOVER_YTD>1000

Compare if a value is less than another value.

Customers with a turnover for the year to date that is less than £1000:

SALES_LEDGER.TURNOVER_YTD<1000

This example can be used as a filter expression or for conditional formatting.

Compare if a value is equal to another value.

Customers with a turnover for the year to date that is equal to £1000:

SALES_LEDGER.TURNOVER_YTD=1000

This example can be used as a filter expression or for conditional formatting.

Compare if a value is greater than or equal to another value.

Customers with a turnover for the year to date that is greater than or equal to £1000:

SALES_LEDGER.TURNOVER_YTD>=1000

This example can be used as a filter expression or for conditional formatting.

Compare if a value is less than or equal to another value.

Customers with a turnover for the year to date that is less than or equal to £1000:

SALES_LEDGER.TURNOVER_YTD<=1000

This example can be used as a filter expression or for conditional formatting.

Compare if a value is not equal to another value.

Customers with a turnover that is not equal to £1000:

SALES_LEDGER.TURNOVER_YTD<>1000

This example can be used as a filter expression or for conditional formatting.

This is used to select properties.

To make values of less than 0 appear red:

If SALES_LEDGER.BALANCE<0 then TextStyle->Color:= 
 NamedColor("Red")

The property is only set if the statement on the left is true.

This example is an advanced conditional formatting expression.

This is used to assign a value.

To make values of less than 0 appear red:

If SALES_LEDGER.BALANCE<0 then TextStyle->Color:= NamedColor("Red")

This example can be used as an Advanced Conditional Formatting expression.

Note: You cannot use := to assign a value to a variable (data field), as these are read-only. For example, you cannot use Text:= with a variable, you can only use this with a text box.

To show balances greater than 0 as credit, and balances less than 0 as debit:

SALES_LEDGER.BALANCE>0?"Credit":"Debit"

In this example, 'Credit' appears if a value is more than 0 because it is to the left of ':'. 'Debit' appears for values that do not obey the expression to the left of '?', because it is to the right of ':'.

This example can be used to display the value on the report or as part of another expression.