Panorama > Formulas > Linking with Another Database
 
lookuplast(
lookuplast(
DATABASE
,
KEYFIELD
,
KEYDATA
,
DATAFIELD
,
DEFAULT
,
LEVEL
)

The lookuplast( function 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.

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.

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, however, 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.

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. However, if you are searching through the current database, the *lookuplast(* function will skip the current record, even if it is the last matching record in the database.) If it cannot locate the information, it returns the default value, or an error if the default value is not supplied.

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

This lookuplast( example finds the most recent order for a given customer, and the amount of that order.

local theCustomer,lastOrderDate,lastOrderAmount
theCustomer=""
gettext "Customer name:",theCustomer
lastOrderDate=
lookuplast("Invoice",Company,theCustomer,Date,0,0)
if lastOrderDate=0
    message "No previous invoices for this customer."
    stop
endif
lastOrderAmount=lookuplast("Invoice",Company,theCustomer,Total,0,0)
message theCompany+"‘s most recent order was for "+
    pattern(lastOrderAmount,"$#,.##")+" on "+
    datepattern(lastOrderDate,"Month ddnth, yyyy")+"."

Here’s another example from an Invoice database that combines the lookup( and lookuplast( functions. This example first tries to look up a customer in the Customers database. If the customer is not found there, the program checks to see if there is a previous invoice for this customer. (The current invoice is probably the most recent invoice for this customer, but the *lookuplast(* function skips the current record when searching the current database, forcing it to continue searching and find the previous invoice for the customer, if any.)

Address= lookup("Customers",Company,Company,Address,"",0)
if Address <> ""
    City=lookup("Customers",Company,Company,City,"",0)
    State=lookup("Customers",Company,Company,State,"",0)
    Zip=lookup("Customers",Company,Company,Zip,"",0)
else
    Address=lookuplast(info("databasename"),Company,Company,Address,"",0)
    City=lookuplast(info("databasename"),Company,Company,City,"",0
    State=lookuplast(info("databasename"),Company,Company,State,"",0)
    Zip=lookuplast(info("databasename"),Company,Company,Zip,"",0)
endif

Remember, the lookuplast( function locates the matching information that is physically closest to the bottom of the database. What this proximity to the bottom means depends on how the database is currently sorted.

See Also

History

VersionStatusNotes
10.0UpdatedCarried 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.