Panorama > Relational
 
relatedarray(
relatedarray(
SEPARATOR
,
FORMULA
,
SOURCEDATABASE
)

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

Parameters

This function has three parameters:

separator – is the separator character or characters for the output array.

formula – is the formula that will be used to extract data from each matching source database record. 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 *RelatedArray( Options* section below.)

Description

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

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 relatedaray( function, or can be set up in advance using the Database Options>Relational panel. Setting up the keys in advance makes the relatedarray( function super easy to use.

To illustrate this we’ll use two databases, a vendor database and a checkbook. These databases can be relationally linked with the key field Vendor in the Vendor database and PayTo in the Checkbook database. As this diagram shows, there may be many individual checks that match each vendor.

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 relatedarray( function. Since the keys are included in the relation itself, they don’t have to be included in the relatedarray( function. For example, this formula will retrieve the check numbers for every check written to the the current vendor.

relatedarray(", ",{Check},"Checkbook")

In this example, if the current vendor is City Services, the result will be:

108, 137, 197, 221, 269, 301, 343, 390, 428, 448, 487, 533

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. For example, if I click to Costco, the result will be:

133, 160, 253, 473, 500

The relatedarray( 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 information about all the checks associated with the current vendor.

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.

The relatedarray( function also works well with Text List Objects and Matrix Objects. This example shows how a Text List can be used to display multiple columns of information retrieved by the relatedarray( function. The Text List object needs a tab separator character between each column, so the formula uses the tab() function to generate that.

If you’ve used a Text List before you’ll be tempted to fill in the Database item in the properties panel. You should not do that – the database is already specified in the relatedarray( function. If you also specify it in the panel, it won’t work.

If you’ve never set up a multiple column Text List before, the column names and layout are specified in the Multiple Columns area at the bottom of the properties panel. See Text List Multiple Columns to learn more about how to set up multiple columns.

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 retrieve checks written to the current vendor, but only if they were written in the past 180 days. Any older checks will be excluded.

relatedarray(", ",{Check},"Checkbook","source subset",{Date>today()-180)})

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

let extraRelatedOptions = initializedictionary("source subset",{Date>today()-180)})
relatedarray(", ",{Check},"Checkbook",extraRelatedOptions)

The rest of this page describes all of the options that are available to configure a relatedarray( 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 relatedarray( function itself. For clarity, all of the examples below will explicity include the parameters directly in the relatedarray( function.

RelatedArray( Options

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

You can also 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:

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

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

relatedarray(separator,{...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).

Keys

The relatedarray( 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.

relatedarray(separator,{...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.

relatedarray(separator,{...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:

relatedarray(separator,{...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:

relatedarray(separator,{...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.

relatedarray(separator,{...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.

rrelatedarray(separator,{...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 relatedarray( 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"
relatedarray(separator,{...formula...},"database","Vendors"

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

relatedarray(separator,{...formula...},"database","Vendors","key","VendorID"

Including Invisible Records in the Relation

The relatedarray( 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.

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

Specifying a Subset

The relatedarray( 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 relatedarray( function.

relatedarray(separator,{...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 *RelatedArray( Options(* section above.)

See Also

History

VersionStatusNotes
10.2NewNew in this version.