UDR Formula and Expression Dependency Rules

Summary

The information in this article applies to LIMS 1.x and Access 2.0.

Columns in a user-defined report (UDR) may include values from analysis results, formulas, and expressions. This article describes the order in which columns are calculated and how formulas and expressions may use values in other columns.

More Information

LIMS uses the following steps to generate a UDR:

1. Build a list of analyses whose results are required for the report.

2. For each sample queried for the report, query the database for results for each analysis obtained in step #1.

3. Initialize the report’s underlying data by setting the values for any column with an analysis to the values obtained in step #2, replacing any nulls with the column’s null replacement text, if any.

4. Starting with the leftmost column (column A) set the value of the underlying data for any column with a formula to the result obtained by evaluating the formula, replacing any nulls with the column’s null replacement text, if any.

5. Edit the UDR’s report template and for each UDR column with an expression, set the template column’s ControlSource property to the same expression. Close the report template.

6. Preview the report by running the report template.

Using this order of events it is important to understand that a UDR’s underlying data is populated only with data from analyses and data calculated with a formula. This data is written to a temporary table in LIMSCODE with column names corresponding to the UDR’s column letters. This temporary table is the record source for the template — an Access report. Expressions are evaluated by the Access report generator only when the template is opened for preview or printing. Below is a list of formula and expression rules derived from this UDR generation process.

Formulas

Rule 1: A formula can reference any column whose value is obtained solely from an analysis result. The referenced column can be either to the left or right of the formula column.

Rule 2: The order of formula evaluation is left to right so a formula can not reference a column to its right if that column’s value is obtained from a formula.

Rule 3: Formulas can not reference any column whose value is obtained from an expression.

Expressions

Rule 1: An expression can reference any column whose value is obtained from either an analysis result or formula. The referenced column can be either to the left or right of the expression column.

Rule 2: A column whose value is obtained from an analysis result can also have an expression. Note that this allows an expression to reference its own column letter — the column in the underlying table.

Rule 3: A column whose underlying value is obtained from a formula can also have an expression. Note that this allows an expression to reference its own column letter — the column in the underlying table.

Rule 4: An expression can reference a column whose value is obtained from another expression by using the report column’s text control name (e.g. txtValueX where X is the column) in place of the column letter. Note that circular references are not allowed. For example if column A has expression =txtValueB*2 and column B has expression =txtValueA*3, a circular reference occurs and both columns display an error.