A LOGIC statement will evaluate as either TRUE or FALSE – the TRUE result will trigger the conditional formatting – a FALSE result will ignore the conditional formatting.
EXAMPLE 1
A simple example of Conditional Formatting is to SHOW or HIDE a variable (typically a Text Box or Logo image).
As the variable is usually visible on the design prior to any conditional formatting is applied the logic test needs to be constructed so when TRUE the condition to Suppress Printing will be triggered,
It is easier to construct a logic test statement to be TRUE to SHOW a variable – thus the logic statement that would be TRUE to SHOW the variable needs to be negated (NOT) to prevent it from showing.
Consider the asterisk (*) displayed when a Sales Ledger account is over its Credit Limit. the logic_test statement for the FILTER would be Balance <= Credt_Limit and the Text Box would hold the asterisk.
The asterisk (*) will be visible on the report and will therefore need to be Conditionally Suppressed by the FILTER. Suppression will take place when the FILTER is TRUE.
A more complex example of Conditional Formatting is to SHOW one Text Box while other Text Boxes are HIDDEN. Consider three Text Boxes that display bank details for Euro, US Dollar and GBP currencies. In the final use of the conditional formatting the three Text Boxes will sit above each other so only one is visible while the other two are suppressed.
Three Text Boxes are required and overlaid on to each other – Each has its own logic_test statement FILTER and one must be TRUE when the other two are both FALSE.
Currency – Pounds Sterling – GBP
Currency – US Dollar – USD
Currency – EUR
Symbol = “£” (but may be blank)
Symbol = “US$”
Symbol = “€”
The FILTER must be TRUE for the Text Box to be suppressed –
NOT(Symbol =”£” OR Symbol = “”)
Symbol <> “US$”
Symbol <> “€”
The FILTER statement is the NOT version of the statement that describes the contents of the symbol field. The filter for when the Currency is GBP requires two possible scenarios to be tested. Some users will set they BASE currency to have no symbol defined. Also, different Sage companies may use a variant for their currency symbols. Creating a filter that will work across all Sage companies will need to test for the currency’s ISO Code rather than its symbol!
Overlaying Text Boxes are OK. for a few variants but what if you want to select from a dozen or more sales persons depending on a given Cost Centre analysis code held on SOP order header?
Rather than overlay all the possible Text Boxes that would be required it is better to employ an expression that returns TEXT when an IF-THEN-ELSE statement is TRUE.
Analysis_Code = “ABC”, Analysis_Code = “DEF”, …. Analysis_Code = “XYZ”. Each of these logic filters would need their own Text Box and need to be overlaid. Such a solution is difficult to update and ensure all options are considered.
Creating a nested If-Then-Else scenario is easier to construct and amend when analysis codes or salespersons are added/deleted/replaced.
The expression that replaces a set of overlaid Text Boxes would be;
The If-Then-Else construct is evaluated from left to right and stops as soon as a TRUE result is encountered. If no TRUE result is encountered it will run to the end and report back the ERROR.
