superlookup( function

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

arraybuild( -- builds an array by scanning a database and creating an array element for every record (including invisible records) in the database (see Text Arrays).
arrayselectedbuild( -- builds an array by scanning a database and creating an array element for every visible (selected) record in the database (see Text Arrays).
fieldvalue( -- grabs the contents of a field in the current record of a database. You can grab data from the current database, or from another database.
formulavalue( -- calculates the result of a formula based on data in the current record of any open database. This is similar to the fieldvalue( function, but allows any formula to be used, rather than just a single field.
globaldictionarybuild -- builds a global dictionary by scanning a database.
join -- joins data from another database into the current database.
joinonerecord -- joins matching data from another database into the current record.
lastlookupdatabase( -- returns the name of the database referenced in the most recent lookup.
lookup( -- searches a database for a value, then returns other information from the same record. For example, the *lookup(* function can look up a phone number given a customer name, or look up a price given a part number.
lookupall( -- builds a text array containing one item for every record in the target database where the data in the keyField matches the keyData. Each item in the text array contains the value extracted from the dataField for that record. If the data field is a numeric or date field, it is converted to text using the default patterns for that field.
lookupalldouble( -- is similar to the lookupall( function, but it returns two fields from the target database instead of just one.
lookupalloctet( -- is similar to the lookupall( function, but it returns eight fields from the target database instead of just one.
lookupallquadruple( -- is similar to the lookupall( function, but it returns four fields from the target database instead of just one.
lookupallquintuplet( -- is similar to the lookupall( function, but it returns five fields from the target database instead of just one.
lookupallseptuplet( -- is similar to the lookupall( function, but it returns seven fields from the target database instead of just one.
lookupallsextet( -- is similar to the lookupall( function, but it returns six fields from the target database instead of just one.
lookupalltriple( -- is similar to the lookupall( function, but it returns three fields from the target database instead of just one.
lookuplast( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplast(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookup( function which searches from the top of the database, the *lookuplast(* function searches backwards from the bottom.
lookuplastselected( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookuplastselected(* function can look up a phone number given a customer name, or look up a price given a part number. Unlike the lookupselected( function which searches from the top of the database, the *lookuplastselected(* function searches backwards from the bottom.
lookupmoredata( -- looks up an additional data field based on the previous lookup.
lookupmoreformula( -- looks up additional data based on a previous lookup.
lookupselected( -- searches the selected records in a database for a value, then returns other information from the same record. For example, the *lookupselected(* function can look up a phone number given a customer name, or look up a price given a part number.
related( -- searches a database for a record in a related database that matches the current record in the current database based a relational specification (based on one or more key fields or formulas in each database), then uses a 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, a relation is used (see Relational Database Management).
relatedarray( -- builds an Text Array by scanning a database and creating an array element for every record that matches a relational specification (based on one or more key fields or formulas). This is similar to the arraybuild( function, but instead of using an individual field for the key, a relation is used (see Relational Database Management).
relatedrecordid( -- returns the record id of a record in a related database that matches the current record in the current database based on a relational specification.
Relational Workshop -- tool that assists in composing relational lookup(, superlookup(, lookupall( and arraybuild( functions.
serverlookup( -- searches a shared database for a value, then returns other information from the same record. Similar to the lookup( function, but the search is performed on the server instead in the local computer's memory.
table( -- searches a database for a value, then returns other information from the same record. Unlike the lookup( function, the *table(* function does not require an exact match. If it does not find an exact match the *table(* function will use the closest match. For example, the *table(* function can look up a tax rate given an income amount, or look up a shipping price given a zip code and weight.

History

Version	Status	Notes
10.0	New	New in this version.