Many database applications require multiple database files working together. For example, organizing a company’s order entry operations usually requires an invoice file, an inventory/price list file, and possibly a customer file. Panorama has several functions that can search for and retrieve information from another database.

The Lookup( Function

The primary method for accessing information in other databases is the lookup( function (and other related functions). This function can search for and retrieve information from any open database. Need to look up a price or a customer’s credit limit? Chances are the lookup( function is the tool for the job.

When you look up information manually (for example, looking up someone’s number in the phone book), you are actually performing a multi-step process. You start with one piece of information—a person’s name, for example. The first step is to locate the correct phone book. Once you’ve located the correct book, you must search through it to find the name of the person you are looking for. When you find the name, the final step is to copy down the person’s phone number. Panorama’s lookup( function follows a similar process when it looks up data. For example, suppose you want to find out the number of calories in an orange using the database shown here.

Here is the formula for looking up the number of calories in an orange. The parameters to the lookup contain all the information necessary to locate the information.

The first parameter is called the lookup database. It tells Panorama what database to look in for the information, in this case Groceries. This database must be open (however, it does not have to have any visible windows, see opensecret).

The second and third parameters tell Panorama how to search for the data you want. In this case Panorama is being told to “search through the Fruit column until you find Orange.” The field to look in (in this case Fruit) is called the key field. The data to look for (in this case Orange) is called the key value. By the way, Panorama is very picky about the key value. It must exactly match the value in the database, or Panorama won’t find a match. In this case only Orange will work — not orange or ORANGE or even oRaNGe!

Assuming Panorama does find Orange in the database, the fourth parameter tells Panorama what to do next. This fourth parameter is called the data field, and it may be any field in the lookup database. In this case it is Calories, so Panorama will lookup the value in the Calories field (70) and return it as the result of the function.

Failed Lookups

What if you try to lookup something that doesn’t exist in the database – for example Mandarin Orange. Normally, the lookup( function simply returns an error in this situation. However, you can also supply an additional default parameter, like this:

lookup("Groceries",Fruit,"Mandarin Orange",Calories,100)

In this case, the lookup( function will return a default of 100 calories for any failed lookup. The default value should match the data type of the data field. Since Calories is a numeric field, the default is also numeric. If the data field had been a text field (for instance Serving Size) the default would need to be text (for example "").

Lookup Variations There are actually several different variations of the lookup function.

The SuperLookup( Function

The superlookup( function is similar to the lookup( function, but instead of using an individual field for the key and data, any valid logical (Boolean) formula can be used. Unlike the regular lookup( function described above, 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. In its basic form, this function has three parameters.

superlookup(database, queryFormula, dataFormula)

The first parameter, database, is the database that contains the information to be located. This database must be open (however, it does not have to have any visible windows, see opensecret).

The second parameter tells Panorama how to search for the data you want. Instead of specifying a single field and data value, a formula that identifies the desired information is supplied. This formula must be quoted, as you’ll see in the examples below.

The third parameter is another formula that assembles the desired information from the database record that was identified by the second parameter. Again, this formula must be quoted.

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)

By default, the superlookup( function returns an error if no matching record is found. You can also specify a default value to be used if no match is available.

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

There are several other optional settings available, see superlookup( to learn more about these.

Looking Up Additional Data

After you have looked up information, you may need to retrieve additional information from the same record. Panorama has two functions for doing this, lookupmoredata( and lookupmoreformula(. Both of these functions are designed to be used immediately after any lookup function, including lookup(, lookuplast(, lookupselected(, lookuplastselected(, and superlookup(.

The lookupmoredata( function has a single parameter, the name of the field to retrieve additional data from. In this example, the code uses lookup( to get the address from a Mailing List database and copies it into the Street field in the current database. It then retrieves the City, State and ZipCode fields from the same record located.

Street = lookup("Mailing List","Name",Name,"Address")
City = lookupmoredata("City")
State = lookupmoredata("State")
Zip = lookupmoredata("ZipCode")

The lookupmoreformula( function also has a single parameter, a formula. (The formula must be quoted.) Panorama uses this formula to construct data to retreive from the record that was located by the previous lookup( function. In this example, the code locates a check from a Checkbook database. It first retrieves who the check was written to, then retrieves the age of the check (in days – today’s date minus the date of the check.)

Vendor = lookup("Checkbook","Number",ckNumber,"PayTo")
Age = lookupmoreformula({today()-Date})

Looking Up Data in the Current File

You can use the lookuplast( function to look up the previous entry, with the same value, in the same database. For example, in a checkbook database you can automate repetitive payments by looking up the previous payment to the same company. If the first parameter, the database name, is "", the lookup will be into the current database. The lookuplast( function won’t treat the current record as a match.

lookuplast("",PayTo,PayTo,Amount,0)

Suppose that your last check to Pacific Mutual was $178.34. Using the formula above you could automatically enter this value the next time you write a check to this company.

The superlookup( function also has options to search backwards and to skip the current record, see the superlookup( page for details.

“Looking Up” from the Current Record

All of the functions described so far have started by searching for a record that matches some sort of criteria, then returning data from that record. The fieldvalue( function does only the second part of that. Instead of searching for a record, it simply retrieves data from whatever record is currently selected in the specified database. The function has two parameters — the name of the database to grab from (the database must be open) and the name of the field within that database. For example here is the formula to look up the number of calories of the currently selected fruit.

fieldvalue("Groceries",Calories)

The value returned by this function will change depending on what record is active in the Groceries database.

Note: In Panorama 6 and earlier, this was called the grabdata( function, and you can still use that name.

Looking Up Rates in a Rate Table

The table( function is designed for looking up rates from a table. For example, this function can be used to look up shipping rates, tax rates, discount rates, or any kind of stepped rate where the rate changes according to a sliding scale. To illustrate this function, consider this shipping rate database.

For packages from 0 to 49.99 pounds the rate is 2.50 per pound. For packages from 50 to 99.99 pounds the rate is 2.35 per pound, from 100 to 249.99 the rate is 2.25 etc. Suppose we use a regular lookup function to look up the weight, like this.

lookup("Shipping Rates",Weight,PackageWeight,«Rate Per Pound»,0,0)

This formula will work fine for weights that appear in the table like 50, 100 and 250. But for other weights like 47 or 182 the formula will return the default value, zero. To fix this, use the table function instead of the lookup function.

table("Shipping Rates",Weight,PackageWeight,«Rate Per Pound»,0,0)

The table function will return the closest lower match. This means that if the PackageWeight is 3, 17 or 42 the formula will return 2.50. If the PackageWeight is 110 or 246 the formula will return 2.25, etc. Here is a complete formula that calculates the shipping cost for any package.

PackageWeight*table("Shipping Rates",Weight,PackageWeight,«Rate Per Pound»,0,0)

The formula looks up the rate per pound and then multiplies that rate by the package weight.

Looking Up Multiple Values at Once

In some applications you may want to lookup ALL records that match certain criteria, rather than just the first. For example you might want to list all checks from a certain vendor, or all checks over a certain amount in a certain date range. Panorama has two function that can do this type of operation: lookupall( and arraybuild(. See the documentation for these functions for details. If you are intending to display the data on a form, the Text List Object and Matrix Object also have this capability built in.


See Also


History

VersionStatusNotes
10.0UpdatedCarried over from Panorama 6.0, but with several new functions, including superlookup( and lookupmoredata(.