Panorama > Formulas
 
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 too 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:

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.

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

History

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