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 extensive capabilities for relationally linking multiple files together (these capabilities were greatly expanded beginning with Panorama X 10.2).

To set up a relational link between the current database and one or more other databases, use the Database Options>Relations dialog sheet. This panel allows you to tell Panorama what the common fields are between two databases.

This panel may look complicated, but if the databases have exactly corresponding fields a relation can be set up in seconds with just a few clicks. See Relational Database Management to learn all the details about configuring a relation with this dialog. If the fields don’t exactly correspond, you can set up formulas to tell Panorama how they do match up.

Once a relation has been established between two databases, you can use that relation to quickly select data that corresponds to the current database, to display related information in a form, to accelerate data entry of related information, to post updated data to a related database, and to perform a full join between two databases.

#### Selecting Data Related to the Current Record

If the current database has one or more databases related to it, additional items are added to the Search menu to allow you to quickly select data related to the current record. These extra items also appear in the menu that appears when you click on the record count in the tool bar. This illustration shows how easy it is to select data related to the current record.

Note: For clarity, the illustration shows the Vendor and PayTo fields as the active fields in the respective databases, but this is not necessary. Unless the relation involves a line item field, it doesn’t matter what field is active when the selection is made.

A relational selection can also be programmed into your own custom code, see selectrelated to learn how to do this.

#### Displaying (or Printing) Related Data in a Form

Panorama has two functions that can be used to display related data on a form. When using these functions, the related data is not copied into the current database, but it can be displayed as if it was part of the current database. This example uses the related( function to display the address of the company the check was written to, even though the address is stored in a separate database.

If the formula is constructed properly, the related data display will update automatically as you switch from record to record (see related( to learn how to ensure this happens).

If you expect there to be multiple records related to the current record, use the relatedarray( function. This example uses this function to display a list of all of the checks written to this vendor. Again, the list of checks is being displayed directly from the related Checkbook database, without ever being copied into the Vendors database.

This example uses a Text List Object to display the list of checks, but you can also use a Matrix Object or a Text Display Object.

#### Linking Clairvoyance™ and Data Entry to a Related Database

Panorama’s relational system can be tightly integrated with Clairvoyance™ (auto-complete) and with data entry. To illustrate this, I’ll use two databases set up for a hobby shop – a sales database (order entry) and a customer account database. These databases are related as shown by the arrows in this diagram.

To see how remotely linked Clairvoyance™ works, just start typing into the Name field in the sales database (this diagram shows the form instead of the data sheet, but the underlying data is the same and relations work the same in either view.) The contact information in the order form is linked to the customer database, so Clairvoyance™ kicks in across databases to auto-complete the name.

When the return or tab key is pressed, Panorama uses the pre-defined relation to populate all of the contact information in the new order. Note that this auto join happens completely automatically – there is no coding needed, or even a formula (other than formulas set up in the relation definition).

This order form database is also relationally linked to a catalog (price list) database. When I start typing an item into the order, Clairvoyance™ auto-completes the entry for me from the catalog, even if this item has never been entered into an order before.

When the return or tab key is pressed, Panorama automatically populates the price and calculates all totals.

Simply rinse and repeat to complete the rest of the order.

To learn how to set up Auto Join and Remote Clairvoyance™ see Relational Database Management.

#### Posting Updated Data to a Related Database

When two databases share common information, a simple one line program using the posttorelated statement can be used to update a related database with changes made in the current database, as shown in this diagram.

The same program will work to add completely new records to the related database.

There’s not much more to it, but see the posttorelated statement for additional details.

#### Creating a Related Record in a Related Database

When two databases share common information, a simple one line program using the addrelatedrecord statement can be used to create a new record in the related database with the same key and data values as the current record in the current database, as shown in this diagram.

See the addrelatedrecord statement for additional details.

#### Joining two Related Databases

The final operation available for related databases is a full relational join. To perform a join, Panorama scans both the current and the related database, identifies all matching records, and updates the current database with information from the matching records in the related database. Optionally, it can also append any non-matching data from the related database into the current database.

To perform a join into the current database, choose File>Import>Join Panorama Database with Current Database. This opens the Join Database dialog.

This dialog allows you to select the database you want to join from, and to customize the options for the join. See Join Databases to learn more.

The join operation can also be programmed into your custom code. See the join statement to learn how to do this.

#### Additional Resources

For more information about relational database topics, see these pages:

- addrelatedrecord -- The addrelatedrecord statement adds a new record to the related database corresponding to the current database.
- findrelated -- The findrelated statement finds information that matches the current record in a related database.
- Join Databases -- joining another database into the current database (relational join).
- join -- The join statement joins data from another database into the current database.
- joinonerecord -- The joinonerecord statement joins matching data from another database into the current record.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- posttorelated -- The posttorelated statement posts data in the current record to the corresponding record in a related database.
- related( -- The related( function searches for a record in a related database that matches the current record in the current database based on 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( -- The relatedarray( function 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( -- The relatedrecordid( function 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 Database Management -- linking together multiple databases based on common data.
- Relational Workshop -- tool that assists in composing relational lookup(, superlookup(, lookupall( and arraybuild( functions.
- selectrecordsrelatedto -- The selectrecordsrelatedto statement selects records in the current database that are related to any record in another database.
- selectrelated -- The selectrelated statement selects information that matches the current record in a related database.

### Legacy Database Link Features

The relational system described above makes working with related database very convenient and easy. This system was not available prior to Panorama X 10.2. However, it was possible to work with related databases by using a variety of “lookup” functions that can search for and retrieve information from another database. These are much less convenient to use because the complete specification for how the databases are related must be explicitly included every time the function is used. However, many existing database already use these functions, so they will continue to be available even though we recommend using the new relational system going forward. (If you are new to Panorama, you’ll probably want to skip the rest of this page.)

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

- 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.
- globaldictionarybuild -- The globaldictionarybuild statement builds a global dictionary by scanning a database.
- join -- The join statement joins data from another database into the current database.
- joinonerecord -- The joinonerecord statement joins matching data from another database into the current record.
- 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.
- related( -- The related( function searches for a record in a related database that matches the current record in the current database based on 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( -- The relatedarray( function 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( -- The relatedrecordid( function 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( -- The serverlookup( function 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( -- 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

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.

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

- addrelatedrecord -- adds a new record to the related database corresponding to the current database.
- findrelated -- finds information that matches the current record in a related database.
- Import Database -- importing another database into the current database.
- join -- joins data from another database into the current database.
- Join Databases -- joining another database into the current database (relational join).
- joindialog -- opens the standard Join Database dialog.
- joinonerecord -- joins matching data from another database into the current record.
- posttorelated -- posts data in the current record to the corresponding record in a related database.
- related( -- searches for a record in a related database that matches the current record in the current database based on 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 Database Management -- linking together multiple databases based on common data.
- Relational Workshop -- tool that assists in composing relational lookup(, superlookup(, lookupall( and arraybuild( functions.
- selectrecordsrelatedto -- selects records in the current database that are related to any record in another database.
- selectrelated -- selects information that matches the current record in a related database.

History

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