|
Overview: Working with External Lookups
With Monarch Classic’s External Lookups window, you can perform external lookups to link two or more database tables together or to link a report to one or more database tables. When linking two database tables, both tables must contain one or more fields that can be used to relate the tables to each other; in other words, they must have at least one field whose values are common to both tables. This field is referred to as a link field.
An example of a link field might be a Cust_ID field that holds customer identification numbers. If one database table holds records detailing individual customer transactions, and another database table holds customer contact information, you could link the two tables using a common Cust_ID field as the link field (the field that relates one table to the other). The result would be a database table that includes both the transaction data and the customer contact data.
Monarch Classic supports a single type of lookup called, in technical terms, a left outer join. To illustrate how a lookup works, imagine that you have a list containing the names of ten people you want to call to invite to a party, but you don’t have their telephone numbers. When you open your telephone book and search it to find the number for each person, you are performing a lookup. The result of your lookup is a list of the same ten people along with the telephone number for each person. Thinking about this in database terms, you started with a single column of data, we’ll call this the Name column. You then related this column to the telephone book based upon the same Name column that appears on each page of the telephone book. For each value of your Name column, you found the corresponding value in the telephone book’s Name column, then you pulled the phone number out of the telephone book and added it as a second Phone Number column to your list. You’ve just performed a lookup.
When linking two tables in this manner, the first table may have records that do not have matching counterparts in the second table. For example, one of the 16 names on your party list may not exist in your telephone book (they may have an unlisted number or may live outside the area covered by the telephone book). The same is true of the second table; it may have records that do not have matching counterparts in the first table (the telephone book contains more names than your party list).
When performing a lookup, one table is designated as the table that initiates the lookup, and therefore receives data from the other table, and the other table is designated as the lookup table (the table containing the data that you are looking for). In most database applications, each table can play either role, depending upon the syntax of the link command (thus in the term "left outer join", the word "left" refers to the table on the left side of the command, which is designated as the table that initiates the link). But in Monarch Classic the Monarch Classic table is always designated as the table that initiates the lookup and the external database that you link to is designated as the lookup table. This is necessary, since a lookup is intended to add information from an external database to the Monarch Classic table and not the other way around.
In short, a Monarch Classic lookup is used to add information (fields) from an external database to the Monarch Classic table. Keep in mind that in order to create a lookup between the Monarch Classic table and an external database table, both tables must include one or more fields with values common to both tables. These fields are referred to as link fields and the fields that are imported from the external database are called linked fields. Fields linked from an external database can be used just like any other fields in Monarch Classic. You can use linked fields in filter, sort, calculated field, and summary definitions, hide and rename linked fields, and print, copy, and export linked fields along with other Monarch Classic data.
Monarch Classic supports up to 16 external lookups in a single Monarch Classic session, enabling you to link data from multiple external database tables into the Monarch Classic table. The resulting data set is typically termed a view. Monarch Classic can import views, since a view contains the same logical structure as a table (i.e., it is a flat database table). Using this technique, you can usually import any data set that you need, however, this requires that you have access to, and the necessary skills with, the application that holds your source data.
Note: An external lookup in one model can be linked to an external lookup in another model. In addition, external lookups can also be imported. See Linking Objects and Linking External Lookups for more information.
See the following for additional information: