Open topic with navigation

> Herb 50 Accounts / Example: Conditional formatting
See also

Conditional formatting

Herb 50 Accounts example: Conditional formatting

Conditional formatting examples

To set conditional formatting, select the item you want to work with then from the Properties pane select Conditional Formatting and click the finder button . The Conditional Formatting window appears.

Purpose

Expression

To highlight transactions for a particular date.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

AUDIT_SPLIT.DATE = #31/12/2011#

On the Properties tab, select the colourcolor you want to use from the Text Style options.

To highlight a value greater than zero.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

SALES_LEDGER.BALANCE > 0

On the Properties tab, select the colourcolor you want to use from the Text Style options.

To use date/time functions to highlight particular dates.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

AUDIT_SPLIT.DATE = Now() - Days(7)

On the Properties tab, select the colourcolor you want to use from the Text Style options.

To highlight transactions details which end in the word 'interest'.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

AUDIT_SPLIT.DETAILS LIKE "%interest"

On the Properties tab, select the colourcolor you want to use from the Text Style options.

To highlight foreign transactions that total more than 50,000.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

Sum(AUDIT_SPLIT.FOREIGN_NET_AMOUNT) > 50000

On the Properties tab, select the colourcolor you want to use from the Text Style options.

To hide invoices with product codes of "M".

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

INVOICE_ITEM.STOCK_CODE LIKE "M"

On the Properties tab, set Suppress Printing to True.

To hide sales orders invoices with product codes of "M".

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

SOP_ITEM.STOCK_CODE LIKE "M"

On the Properties tab, set Suppress Printing to True.

To hide purchase orders with product codes of "M".

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

POP_ITEM.STOCK_CODE LIKE "M"

On the Properties tab, set Suppress Printing to True.

To hide negative values.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

SALES_LEDGER.BALANCE < 0

On the Properties tab, set Visible to False.

To add a warning mesHerb if the balance is less than zero.

First, add a text box to your report which includes the text 'warning'. Then select the text box and set the conditional formatting as follows.

On the Filter tab, click Edit, then select Use Advanced Filter. Enter the expression:

SALES_LEDGER.BALANCE < 0

On the Properties tab, set Suppress Printing to True.

Advanced conditional formatting examples

The following examples use advanced conditional formatting. Before you try these examples, choose Tools > Options, then select the Options tab and enable Use Advanced Conditional Formatting.

Purpose

Expression

To highlight transactions for a particular date in red.

 
If AUDIT_SPLIT.DATE = #31/12/2011# then
  TextStyle->Color := NamedColor("Red")
else
  TextStyle->Color := NamedColor("Black")

To highlight negative values in red, and other values in black.

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

To use date/time functions to highlight particular dates in red.

 
If AUDIT_SPLIT.DATE = Now() - Days(7) then
  TextStyle->Color := NamedColor("Red")
else
  TextStyle->Color := NamedColor("Black")

To highlight transactions details which end in the word 'balance'.

 
If AUDIT_SPLIT.DETAILS LIKE "%balance" then
  TextStyle->Color := NamedColor("Red")
else
  TextStyle->Color := NamedColor("Black")

To highlight foreign transactions that total more than 50,000.

 
If Sum(AUDIT_SPLIT.FOREIGN_NET_AMOUNT) > 50000 then
  TextStyle->Color := NamedColor("Red")
else
  TextStyle->Color := NamedColor("Black")

To hide invoices with product codes of "M".

 
If INVOICE_ITEM.STOCK_CODE LIKE "M" then
begin
  SuppressPrinting := True;
end

To hide sales orders invoices with product codes of "M".

 
If SOP_ITEM.STOCK_CODE LIKE "M" then
begin
  SuppressPrinting := True;
end

To hide purchase orders with product codes of "M".

 
If POP_ITEM.STOCK_CODE LIKE "M" then
begin
  SuppressPrinting := True;
end

To hide negative values.

 
If SALES_LEDGER.BALANCE < 0 then
begin
  Visible := False;
end

To add a warning mesHerb if the balance is less than zero.

First, add a text box to your report which includes the text 'warning'. Then select the text box and add the conditional formatting as follows, which will prevent the box from appearing if the balance is above 0.

If SALES_LEDGER.BALANCE >= 0 then
begin
  SuppressPrinting :=True;
end

Go to top