Panorama > Relational
 
related(
related(
FORMULA
,
SOURCEDATABASE
,
OPTION
,
VALUE
)

The related( function searches a database for a record in a related database that matches the current record in the current database based 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).

Parameters

This function has four parameters:

formula – is the formula that will be used to extract data from the source database. The entire formula must be enclosed in quotes (see Quotes).

sourcedatabase – is the name of the source database (do not include .pandb in the name, just the root name). This database must be open. (Note: This parameter can also be a dictionary, see the *Related( Options* section below.)

option – extra option name (optional, may be omitted), see the text below for details.

value – extra option value (optional, may be omitted), see the text below for details.

Description

This function searches a database for a record in a related database that matches the current record in the current database based 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).

To use this function you must specify the key in both the current database and the second database (which is called the source database). The keys can be specified with parameters supplied as part of the related( function, or can be set up in advance using the Database Options>Relational panel. Setting up the keys in advance makes the related( function super easy to use.

To illustrate this we’ll use two databases, a checkbook and a vendor list. These databases can be relationally linked with the key field PayTo in the Checkbook database and Vendor in the Vendor database.

Here’s how these keys would be set up in the Database Options>Relational panel.

See Relational Database Management to learn all the details about this panel.

Once the relation between the databases has been set up, it can be used by the related( function. Since the keys are included in the relation itself, they don’t have to be included in the related( function. For example, this formula will retrieve the city, state and zip code associated with the current check.

related({City+", "+State+" "+Zip},"Vendors")

This diagram illustrates how this function works.

If you click to a different check and then calculate the formula again, the result will be different. It will always return the information associated with the current key field value.

The related( function can be used in a form object to display data in another database without actually transferring the data to the current database. This example shows this technique used with a Text Display Object to display the vendor address associated with the current check.

You may wonder about the ignore( function in the formula above. This isn’t needed to display the address, but it is needed to make sure that the address updates when the PayTo field changes. Panorama will automatically update the display when any field used in the formula changes, but it doesn’t know that the PayTo field is used, because that fact is hidden inside the relation definition. The ignore( function allows this field to be explicitly added to the formula without changing the calculated result of the formula. With this in place, the form will automatically update the address as the form is moved from check to check.

Sometimes the pre-saved relation template is almost what you want, but not quite. In that case, you can append the options described below to this function to “tweak” how the relation works. For example, if there are multiple key matches in the source database, this formula will always retrieve the last one (closest to the bottom).

related({City+", "+State+" "+Zip},"Vendors","duplicate source key","last")

The extra options can also be specified in a dictionary, like this:

let extraRelatedOptions = initializedictionary("duplicate source key","last")
related({City+", "+State+" "+Zip},"Vendors",extraRelatedOptions)

The rest of this page describes all of the options that are available to configure a related( function operation. Remember, these options can be configured in the File>Database Options>Relations panel (as described in Relational Database Management), or they can be included as an explicit parameter in the related( function itself. For clarity, all of the examples below will explicity include the parameters directly in the related( function.

Related( Options

Relating two databases is a complicated operation. At a minimum, you must specify two basic criteria:

You can also specify additional options such as how to handle missing or duplicate key values (more on this later), and you can restrict the search for a matching record to only a subset of the data in the source database.

Each related( option is specified as a name,value pair, for example "Database","Vendors" or "Key","OrderID". You can specify these options as a list of parameters, like this:

related({...formula...},"database","Vendors","key","OrderID")

or you can combine all of the options into a dictionary, as shown here.

related({...formula...},initializedictionary(
    "database","Vendors",
    "key","OrderID")

At first glance, using a dictionary may seem more complicated, and it is, but it has the advantage that you can prepare the dictionary in advance in a variable, and potentially reuse it in multiple locations in your code (including with the join statement).

Whichever method you use, the option names are case insensitive and ignore spaces, so all three of these three lines will work exactly the same.

"source key","VendorID"
"Source Key","VendorID"
"SOURCEKEY","VendorID"

If you want to temporarily disable an option, you can “comment it out” by prefixing it with two / characters, like this:

"// source key","VendorID"

This is especially useful when you have multiple options specified on a single line.

Source Database

To specify the database that will be related with the current database, use the Database option, as shown in the examples above. The specified source database must already be open (see opendatabase and Auxiliary Databases).

The Database option is not required. If it is omitted the current database will also be used as the source database (note that this is different than the join statement, which doesn’t allow the current database to be the source database). If the current database is used as the source database, the current record will be skipped when searching for a match – the match must be some other record in the current database.

Keys

The related( function has two options for setting up keys, Key and SourceKey. Use the Key option to specify how each record in the current database is identified. For example, suppose the current database is a product list database that contains a field named VendorID, and you want to join that with information from a Vendors database.

related({...formula...},"database","Vendors","key","VendorID")

If the Vendors database also has a VendorID field, we’re done. The join will proceed by matching data based on the VendorID field in each database.

If the two databases don’t have an identically named key field, you’ll need to add a SourceKey option to specify how the key is calculated in the source database (in this case, the Key option specifies this for the current database). For example, suppose in the Vendors database the id is kept in a field named ID (instead of VendorID). Adding the SourceKey option will make this work.

related({...formula...},"database","Vendors","key","VendorID","sourcekey","ID"

So far these examples have used a single field as the key, but any formula can be used. Suppose the current database is an order tracking database with a Name field that contains full names like Bob Smith and Elizabeth Pride. This database could be joined with a customer list database that uses separate fields for first and last name, like this:

related({...formula...},"Customer List","key","Name","sourcekey",{First+" "+Last}

As you can see, each key is actually a formula. This formula must be quoted, which in the example above was done with curly braces ({ and ‘}’) so that quotes could be used inside the formula. See Constants to learn about the different ways text can be quoted.

What if the order tracking database also has separate first and last name fields? In that case, the Key option would also be a formula, like this:

related({...formula...},"database","Customer List",
    "key",{First+" "+Last},
    "sourcekey",{First+" "+Last}

The example above creates keys that are case sensitive, so that the name Bill Mazor in the order tracking database would not match BILL MAZOR in the customer list. The formula can be modified so that the keys will match regardless of upper or lower case.

related({...formula...},"database","Customer List",
    "key",{upper(First+" "+Last)},
    "sourcekey",{upper(First+" "+Last)}

If your data might contain accented characters, you could modify the formulas further so that these will be ignored.

related({...formula...},"database","Customer List",
    "key",{stripdiacriticals(upper(First+" "+Last))},
    "sourcekey",{stripdiacriticals(upper(First+" "+Last))}

With this modification, the name Dän will match Dan or Dán when performing the related( function.

Using the Current Field as the Key If no key option is specified, the current field is assumed to be the key. If both the current database and the Vendors database have a field named VendorID, you can use this field as a key like this.

field "VendorID"
related({...formula...},"database","Vendors"

Of course this code will also work, no matter what field is current:

related({...formula...},"database","Vendors","key","VendorID"

Duplicate Keys in the Source Database

The first phase of the related( function operation is to scan the source database and locate a matching record. But what if a key is duplicated? In other words, what if the same key is associated with more than one record in the source database? You can control the behavior of the function in this situation with the DuplicateSourceKey option. The options are to associate the key with the first matching record, the last matching record (this is the default), or to treat this situation as an error.

For example, suppose our Vendors database accidentally had the same vendor entered twice. Which one should the related( function use? If this is not specified, the last one (closest to the bottom) will be considered a match. If you would prefer the first vendor be returned, modify the function like this:

related({...formula...},"database","Vendors","duplicate source key","first")

Another option is to simply return an error in this situation, like this:

related({...formula...},"database","Vendors","duplicate source key","error")

If the result is being displayed in a form, you can combine this with the catcherror( function to display some fixed error message, or nothing. If the function is being used in procedure code, you can also use catcherror(, or you can use Panorama’s code Error Handling techniques.

Including Invisible Records in the Relation

The related( function normally ignores invisible records (records that aren’t selected) when searching the source database for a match. If you want to include invisible records in the source database when looking for a match, set the JoinSourceInvisible option to Yes. Here is an example that includes all records in the source database in the relation, whether the records are currently visible or invisible.

related({...formula...},"database","Orders",
    "key",{First+" "+Last},
    "join source invisible","yes"

Specifying a Subset

The related( function normally includes all visible records when searching for a match. To restrict the search to a subset of the data, use the SourceSubset option. This option is used with a formula that specifies what data to include. This example updates the current record, assumed to be in a customer list database, with phone numbers from the Orders database. However, only orders that occured in the last 90 days will be included, any earlier orders will be ignored by the related( function.

related({...formula...},"database","Orders",
    "key",{First+" "+Last},
    "source subset",{Date>today()-90)}

Note: The subset option can be combined with the invisble option if you want to include invisible data in a subset.

Error Messages

related( failed because the SOURCEDATABASE parameter is not an open database or a valid dictionary. – This parameter is usually a database name. If the specified database isn’t open, this error message will appear. (Note: the SOURCEDATABASE parameter can also be a dictionary, see the *Related( Options(* section above.)

See Also

History

VersionStatusNotes
10.2NewNew in this version.