lookup(
DATABASE
,
KEYFIELD
,
KEYDATA
,
DATAFIELD
,
DEFAULT
,
LEVEL
)
The lookup( function 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.
Parameters
This function has six 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 lookupselected( function.
keyfield – is the name of the field that you want to search in. For example if you want to look up all checks written to a certain vendor, this should be the field that contains vendor names. The field must be in the database specified by the first parameter. (Note: For compatibility with older versions of Panorama, you are allowed to quote this parameter but quoting is not necessary.)
keydata – is the actual data that you want to search for. For example if you want to look up all checks written to a certain vendor, this should be the actual vendor name. This parameter is often a field in the current database, but it could also be a variable, a constant, or a general expression.
datafield – is the name of the field that you want to retrieve data from. For example if you want to retrieve check numbers, this should be the name of the field that contains check numbers. This must be a field in the database specified by the first parameter.
default – is the value you want this function to return if it is unable to find the information specified by the keyfield and keydata parameters. The data type of the default value should match the data type of the datafield. If the datafield is numeric, the default should usually be zero. If the datafield is text, the default should usually be "". Note: This parameter is optional. If it is omitted, the lookup( function will return an error if the search fails. Any formula may be used to calculate the default value. If the default value is a formula, any fields in that formula are calculated based on the CURRENT database, NOT the database that is being looked up into.
level – is the minimum summary level to be searched. Usually this parameter is zero so that the entire database will be searched. If the level is set to 1 through 7, only summary records will be searched. Note: This parameter is optional. If it is omitted, all records will be searched.
Description
This function searches a database looking for records where the keyField matches the specified keyValue. When it finds a match, it stops searching and returns the contents of the requested dataField from that record.
Note: To learn more about the theory and technical details about working with links between databases, see Linking with Another Database. The lookup( function can also be constructed automatically for you with the Relational Workshop wizard.
To illustrate this function, assume that you have a Price List database that contains fields named Product and Price, and that your current database contains a field named Item that contains a product name. This formula will look up the price:
lookup("Price List",Product,Item,Price)
If there is no product that matches the name in Item, the formula above will result in an error. Adding a default value eliminates the possibility of this error:
lookup("Price List",Product,Item,Price,0)
If a default value is supplied, it should have the same data type as the DataField parameter. In this case, the Price field is numeric, so the default value is numeric also.
This example assumes you have a Vendors database with Company and PaymentCategory field. (It also assumes the current database has a Name field that contains company names.) If the company name is not found in the Vendors database, this formula returns None as the category:
lookup("Vendors",Company,Name,PaymentCategory,"None")
Of course you could also return nothing as the default:
lookup("Vendors",Company,Name,PaymentCategory,"")
In a procedure, you could use multiple lookup( functions in a row to transfer multiple fields.
Address=lookup("Customers",Company,Company,Address,"")
City=lookup("Customers",Company,Company,City,"")
State=lookup("Customers",Company,Company,State,"")
Zip=lookup("Customers",Company,Company,Zip,"")
Since each lookup( function can only transfer one value, four lookups are required. However, Panorama doesn’t actually search the Customers file four times. When a procedure performs multiple lookups with the same target database, key field, and key value Panorama realizes that it doesn’t have to re-search the database – it already knows where the data is, and it just goes and gets it. (If your database is set up for it, you can use the speedcopy statement to move this data even faster.)
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.
- 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.
- superlookup( -- 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.
- 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
| 10.0 | Updated | Carried over from Panorama 6.0, but now the default and level parameters are optional. Also, you now are allowed to use "" to specify the current database. |