superlookup(
DATABASE
,
QUERYFORMULA
,
DATAFORMULA
)

The superlookup( function searches a database for a record that matches a query formula, then uses a second formula to return other information from the same record. This is similar to the lookup( function, but instead of using an individual field for the key and data, any valid true/false (Boolean) formula can be used.


Parameters

This function has three parameters:

database – is the database that will be searched. This database must be currently open. If this parameter is "" then the current database will be scanned. Note: The lookup( function will search all records in the database, including records that are not currently selected. If you want to search only selected records, use the selected option described below.

queryformula – is a formula that determines whether a record should be considered a match. The entire formula must be enclosed in quotes (see Quotes).

dataformula – is the formula that will be used to extract data from the target database. The entire formula must be enclosed in quotes (see Quotes).


Description

This function searches a database looking for records for which the query formula is true. When it finds a match, it stops searching and uses the data formula to return some or all of the contents of that record. Unlike the regular lookup( function, the superlookup( function is not limited to individual fields for determining the match and returning data, but can match based on multiple fields or more complex relationships. When using variables in a superlookup( function to retrieve information from a different database, both the query formula and the data formula are evaluated in the scope of the database being searched – NOT the current database. Therefore a local variable will work, but, for example, a fileglobal variable will not.


Note: To learn more about the theory and technical details about working with links between databases, see Linking with Another Database. The superlookup( function can also be constructed automatically for you with the Relational Workshop wizard.


For example, suppose you want to find out the address of someone in a Mailing List program that uses separate first and last name fields. The regular lookup( function will not work for this, because it only allows a single key field. This example shows how to look up an address from such a database.

superlookup("Mailing List",
    {First="John" and Last="Wilson"}, ☞ Query formula
    {Address+", "+City+", "+State+" "+Zip}) ☞ Data formula

Of course you usually won’t want to look up a fixed name like this. Suppose you have the name you are looking for in variables named firstName and lastName – here’s a formula that will perform the lookup.

superlookup("Mailing List",
    {First=firstName and Last=lastName},
    {Address+", "+City+", "+State+" "+Zip})

Suppose the name is in another database that you currently have open, in fields named FirstName and LastName. You might think that you could perform the lookup like this:

superlookup("Mailing List",
    {First=FirstName and Last=LastName}, ☞ WRONG!
    {Address+", "+City+", "+State+" "+Zip})

The problem is that the function will look for the FirstName and LastName in the Mailing List database, not the current database. To tell the function that these fields are in the current database, enclose the field names with double chevrons, like this:

superlookup("Mailing List",
    {First=««FirstName»» and Last=««LastName»»},
    {Address+", "+City+", "+State+" "+Zip})

The use of double chevrons specifies that a field is in the current database, instead of the database that is being searched. There are four fields referenced in this formula:

First ☞ in Mailing List database
Last ☞ in Mailing List database
FirstName ☞ in current database (because of double chevrons)
LastName ☞ in current database (because of double chevrons)

Advanced Note: Internally, Panorama actually converts double chevrons into the fieldvalue( function. So assuming that the current database is named Invoice, the example above is internally converted into this:

superlookup("Mailing List",
    {First=fieldvalue("Invoice",FirstName) and Last=fieldvalue("Invoice",LastName)},
    {Address+", "+City+", "+State+" "+Zip})

The double chevron notation is a convenience, making this formula much easier to write, and to understand.

SuperLookup Options

In addition to the primary three parameters, you can also add one or more option/value pairs to the end of the function.

superlookup(database,queryformula,dataformula,option1,value1,option2,value2, ...)

These options allow you to modify the behaviour of the superlookup( function. The available options are described below:

Selected (True/False)

By default, the superlookup( function searches thru all records, whether visible or not. You can use this option to tell it to search only selected (visible) records, as shown in this example:

superlookup("Mailing List",
    {First="John" and Last="Wilson"},
    {Address+", "+City+", "+State+" "+Zip},
    "selected",true())

This option makes the superlookup( function operate in a similar manner to the lookupselected( function.

Priority (First/Last)

By default, the superlookup( function finds the first matching record in the database (the match closest to the top of the database). You can use this option to tell it to instead find the last matching record in the database (the match closest to the bottom of the database).

superlookup("Mailing List",
    {First="John" and Last="Wilson"},
    {Address+", "+City+", "+State+" "+Zip},
    "priority","last")

This option makes the superlookup( function operate in a similar manner to the lookuplast( function.

Wrap (True/False)

By default, the superlookup( function finds the first matching record in the database (the match closest to the top of the database). It does this by starting from the first record and searching down for a matching record. If it reaches the bottom of the database without finding a match, the default value is used.

If the wrap option is true, the search starts where the last search left off. If the search reaches the end of the database without finding a match, it wraps back around to the top of the database, and resumes searching from there.

The wrap option is primarily useful for high performance when used with a formulafill statement with sorted data. If you know that the database you are looking into is sorted by the same key you are searching for, and the data in the current database is sorted in the same order, the wrap option can enable huge performance gains – a formulafill that might take minutes or hours can possibly be completed in seconds. Make sure the data is properly sorted before using this option – it’s possible to make performance significantly worse if the data is not in the correct order.

Default (True/False)

By default, the superlookup( function returns an error if no matching record is found. This option allows you to specify a default value if no match is available.

superlookup("Mailing List",
    {First="John" and Last="Wilson"},
    {Address+", "+City+", "+State+" "+Zip},
    "default","<< No Address Available >>")
Summary (0–7)

By default, the superlookup( function searches all records, including data and summary records. This option allows you to restrict the search to summary records of a certain level or above. This example will search all summary records, but no data records.

superlookup("Mailing List",
    {First="John" and Last="Wilson"},
    {Address+", "+City+", "+State+" "+Zip},
    "summary",1)
SkipCurrentRecord (True/False)

By default, the superlookup( function can return any record as a match, including the currently selected record. Use this option if you don’t want the current record to be considered a match.

superlookup("Mailing List",
    {First="John" and Last="Wilson"},
    {Address+", "+City+", "+State+" "+Zip},
    "skipcurrentrecord",true())

See Also


History

VersionStatusNotes
10.0NewNew in this version.