Formulas that look for values in columns and views

Lookup formulas are useful in choice list field formulas to search for values in another database or in the same database.

Referring to views in @function formulas

To refer to a view in an @function formula such as an @DbColumn or @DbLookup, use the actual view name or an alias, in quotes, or use the full cascading name, with an additional backslash (\) so that Notes® can interpret the cascading name. For example:

"By Author"
"View1"
"By Author\\Last Name"

Referring to columns in formulas

To refer to a column in an @DbColumn or @DbLookup formula, use the column number, rather than its title. Columns are numbered from left to right: the leftmost column is column number 1. Refresh the view to make sure you see all its columns.

Some columns are not counted in a search, so skip them when you're counting column numbers:

  • Skip any columns that display a constant value.

    However, if a column contains a formula that happens to return the same result for every document (so that it appears to be a constant value even though it is not), the column does not display a constant value. Include the column in your column count.

  • Skip any columns that consist solely of the following simple Notes functions or @functions:

    Collapse/Expand (+/-)

    # in View

    # of Responses (All Levels)

    # of Responses (1 Level)

    @DocChildren

    @DocDescendants

    @DocNumber

    @DocParentNumber

    @DocSiblings

    @IsCategory

    @IsExpandable

If you plan to use a lookup formula, such as @DbColumn or @DbLookup, to retrieve data from a view, include a sorted column that the formula can use as the lookup key.

CAUTION: If the column used as the lookup key can be resorted by clicking on the header, the formula will return unpredictable results, depending on the current sort.