Linking with Another Database

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 text below on this page describes how to write lookup formulas “from scratch.” Starting with Panorama X 10.1.1, there is also a Relational Workshop wizard that can assist with the composition of these formulas. This workshop will build a complete relational link formula for you by choosing from a series of pop-up menus. It’s super easy.

See Relational Workshop to learn the details of using this wizard. Continue reading on this page to learn the nitty-gritty details of how links between databases work.

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.

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.

**fieldvalue(**-- The fieldvalue( function 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(**-- The formulavalue( function 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.**lastlookupdatabase(**-- The lastlookupdatabase( function returns the name of the database referenced in the most recent lookup.**lookup(**-- 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.**lookupall(**-- The lookupall( function 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(**-- The lookupalldouble( function is similar to the lookupall( function, but it returns two fields from the target database instead of just one.**lookupalloctet(**-- The lookupalloctet( function is similar to the lookupall( function, but it returns eight fields from the target database instead of just one.**lookupallquadruple(**-- The lookupallquadruple( function is similar to the*lookupall(*function, but it returns four fields from the target database instead of just one.**lookupallquintuplet(**-- The lookupallquintuplet( function is similar to the*lookupall(*function, but it returns five fields from the target database instead of just one.**lookupallseptuplet(**-- The lookupallseptuplet( function is similar to the lookupall( function, but it returns seven fields from the target database instead of just one.**lookupallsextet(**-- The lookupallsextet( function is similar to the lookupall( function, but it returns six fields from the target database instead of just one.**lookupalltriple(**-- The lookupalltriple( function is similar to the lookupall( function, but it returns three fields from the target database instead of just one.**lookuplast(**-- 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.**lookuplastselected(**-- The lookuplastselected( function 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(**-- The lookupmoredata( function looks up an additional data field based on the previous lookup.**lookupmoreformula(**-- The lookupmoreformula( function looks up additional data based on a previous lookup.**lookupselected(**-- The lookupselected( function 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.**Relational Workshop**-- tool that assists in composing relational lookup(, superlookup(, lookupall( and arraybuild( functions.**superlookup(**-- 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.**table(**-- The table( function 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.

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. You can also use the Relational Workshop wizard in the *Help* menu as a shortcut to creating superlookup( formulas.

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})
```

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.

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.

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.

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**

- Arithmetic Formulas -- mathematical operators and functions.
- Characters and Unicode Values -- working with individual characters of text.
- Constants -- values embedded into a formula.
- Date Arithmetic Formulas -- performing calculations on dates, and converting between dates and text.
- Formula Workshop -- formula workshop wizard for testing and experimenting with formulas.
- formulacalc -- allows you to evaluate a formula that you were not able to code into the procedure when it was being written.
- Formulas -- basics of formulas: components and grammar.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- Functions -- index of all functions available for use in Panorama formulas.
- makemergeformula( -- builds a formula from an “auto-wrap" style merge template.
- Non Decimal Numbers -- working with numbers in alternate (non-decimal) bases, including binary, octal and hexadecimal.
- Numbers -- Working with numeric values in a formula, and converting between numbers and text.
- Operators -- index of all operators available for use in Panorama formulas.
- Quotes -- text constants embedded into a formula
- Statements -- index of all statements available for use in Panorama procedures.
- SuperDates -- date and time combined into a single value.
- Tag Parsing --
- Text Formulas -- manipulating text with a formula (concatenation, extraction, rearranging, etc.)
- Time Arithmetic Formulas -- performing calculations on times, and converting between times and text.
- True/False Formulas -- logical boolean calculations.
- Using Fields in a Formula -- accessing database fields within a formula.
- Values -- the raw material that formulas work with -- numbers and text.
- Variables -- storing and retrieving individual items of data, not part of a database.

**History**

Version | Status | Notes |

10.0 | Updated | Carried over from Panorama 6.0, but with several new functions, including superlookup( and lookupmoredata(. |