Joins are drawn from the Left Hand Side table to the Right Hand Side table.
Data from the LHS table will NOT be returned if the INNER join does not link to any data from the RHS table.
However, If the INNER join does return data from the RHS table the data in the LHS table is also available to the report.
When tables are linked with an OUTER join the data from the LHS table will always be available to the report regardless of the join not linking to any data in the RHS table.
However, although the OUTER join does not return any physical data the value returned to the report is held as a NULL value. More about NULL values can be read further down this page.
A STATIC join is not joined to any other tables – It is a method of defining a table, so it can be referenced in the report.
A logic-test statement is evaluated and will be either TRUE or FALSE
Replace the IF with the logic-test. Replace the THEN with the TRUE result . Replace the ELSE with the FALSE result (or another IF-THEN-ELSE construct for a nested function).
The old syntax was If(logic_test,true_result,false_result) and if it was nested then it would be;
In a nested version of the function the false_result is replaced by a new logic_test and the false_result comes and the very end – it is the result returned when all the tests have failed (returned FALSE).
When creating a nested if-then-else function it is good practice to test for all the expected TRUE results so the false_result is only returned when a scenario has not been tested.
Was the non-tested scenario a mistake? If not the returned false_result is correct, otherwise, the false-result should be set to return the fact that the false_result is reporting an ERROR.
If the false_result is only used to return an error code set the value to be returned as either “Error” or a number, known to be an impossible result – such as 999999.
A LOGIC statement will evaluate as either TRUE (1) or FALSE (0). Be careful when testing for a variable to be NULL. A NULL value is NOT the same as an EMPTY string.
The AND operation can be thought of as the same as multiplication. The OR operation can be thought of as addition.
Although FALSE will always be equated to zero TRUE will be equated to any number from one. Thus, two or three etc. will be considered to return TRUE
There are eight combinations to consider;
TRUE and / or TRUE , TRUE and / or FALSE , FALSE and / or TRUE , FALSE and / or FALSE
Replacing TRUE with ‘1’ and FALSE with ‘0’ & replacing and with ‘x’ & or with ‘+’ we can do some simple mathematics!
AND (x)
TRUE (1)
FALSE (0)
OR (+)
TRUE (1)
FALSE (0)
TRUE (1)
TRUE
FALSE
TRUE (1)
TRUE
TRUE
FALSE (0)
FALSE
FALSE
FALSE (0)
TRUE
FALSE
TRUE and / or TRUE ; 1 x 1 = 1 (TRUE) ; 1 + 1 = 2 (TRUE)
TRUE and / or FALSE ; 1 x 0 = 0 (FALSE) ; 1 + 0 = 1 (TRUE)
FALSE and / or TRUE ; 0 x 1 = 0 (FALSE) ; 0 + 1 = 1 (TRUE)
FALSE and / or FALSE ; 0 x 0 = 0 (FALSE) ; 0 + 0 = 0 (FALSE)
Using the AND operation both variables must be TRUE for the combination to be TRUE
Using the OR operation either or both variables must be TRUE for the combination to be TRUE
The NOT function is used to reverse the logic-statement’s result.
What is a NULL value? Consider the stock table and the supplier table. These tables can be linked to relate the suppliers associated with the stock record.
When viewing the Stock Record’s <Supplier> tab there may be no listed suppliers, there may be a single supplier or there may be many suppliers.
If there are any suppliers listed one of them will also be recorded as the preferred supplier. However, if there are no listed suppliers then we have a NULL scenario.
A data-field in a table will usually be set to not permit a NULL value. Such a data-field will therefore hold and empty string (“”). If the data-field is permitted to be null it will remain null until it is populated via an update.
If for the purpose of a report we need to do some form of logic-test on the supplier’s preferred status for the selected stock item we need to know not only if the supplier is the preferred supplier but also if the stock item has any linked suppliers.
Referring to the notes on JOINS we must have the join between the Stock record (LHS table) and the Supplier record (RHS table) set as an OUTER join.
Thus, the Stock record will always be available to the report, but the Supplier data will only be returned if a link exists. When a link does not exist the value of the variable used to link will hold a NULL value.
The old syntax for testing for NULL was IS NULL or IS NOT NULL
The new syntax is = NULL or <> NULL
Please note that an EMPTY string is not NULL (because a link exists).
Go to Report 153 – (Stock Control) Price £25 – Stock Items With Linked Suppliers And Their prices
This report employs the concept of testing for NULL records and suppress the unwanted supplier records when the supplier exists, BUT they are not the PREFERRED supplier.
The report sample below is only displaying the stock items that have no linked supplier or only the supplier details that belong to the PREFERRED supplier.
This is because of the criteria tick box has been ticked (setting CRITERIA.StockItems_SpareBit1 = “Yes”) and the filter expression against the DETAIL section holding the logic statement shown immediately below that has returned TRUE.
(CRITERIA.StockItems_SpareBit1 = “Yes” AND ( StockItemSuppliers.Preferred = True or StockItemSuppliers.ItemID = null) ) or CRITERIA.StockItems_SpareBit1 = “No”
Because CRITERIA.StockItems_SpareBit1 can only be ticked or un-ticked testing for “Yes” is redundant but helps to clarify the logic! The expression can be simplified to read;
StockItemSuppliers.Preferred = True or StockItemSuppliers.ItemID = null or CRITERIA.StockItems_SpareBit1 = “No”
The logic test expression is evaluated from left to right with the statements in the brackets being wholly evaluated before the final OR statement.
Whatever the statements in the brackets returns the final OR statement will be TRUE when the criteria is not ticked.
Thus, when NOT ticked the DETAIL section will show all available data.
When ticked the statements in the brackets drops suppliers not marked as preferred.
The StockItemSuppliers.ItemID = null is required rather than the statement StockItemSuppliers.Preferred = null because this test will never return a TRUE result.
Null values only apply to non-existing joins – they do not exist for non-existing data.