How Do I Retrieve Data from Multiple Tables in a Single Task? (Magic xpa 3.x)

When you are working with Data sources in Magic xpa, there are two different ways to bring in data from a table:

1. In the Main source

2. In a Linked source

The Main source is the data source that will be looped through in a task. Data from the Main source can be displayed in a table on your form, and you can display one record or the whole table. You can only have one Main source per task.

A Linked source, on the other hand, only displays one record at a time. Linked sources are typically used to display information that is associated with whatever record is currently being viewed. You can have as many Linked sources as you like in one task.

Note: This does not mean you can only display one table on each form to the user. Even though you can have only one Main source per task, you can still display multiple tables on a form, by using Subforms. Although a Subform looks and acts as though it is part of the parent task, it is in fact getting data from a child task which has its own Main source.

You retrieve data using a Linked source by using the Link operation. Let’s see how it’s done.

Using a Link Operation

1. First, you create a Link/End link pair. Press F4 to open up a line, then type “L”. The Link and End Link will both be created. Then, choose the Data source you want to link to. Here we chose Data source 2, the Studios table. You can type in the Data source number, or zoom to select it from a list.

2. Next, tab to the Index. The Index determines the order in which the table will be searched. It is important that the Index match the search criteria you are using to find the record. For instance in this example, if the Index is the Studio Code, then the Locate should also be on the Studio Code.
   
   When you select an Index, any columns that participate in the index will be automatically added to the link.

3. Set up the locate columns (see next section for details on how to do that).

4. Finally, select any other columns you want to include in the link. To do this:

   • Press F4 to open up a line. Column will be automatically entered for the variable type.

   • Tab to the next field.

   • Zoom to select the column you want, or just type in its number.

   • Rename the column, if you want (see below).
     
     You can rename the linked variables to make it more obvious which variable belongs to which data source.
     
     

Setting Up the Link Locate columns

The Locate columns in a Link work something like the “Select Where” clause in SQL. The column marked Locate: is the lower value to match (“locate from”) and the To: column is the upper boundary. If they both point to the same value, as in this example, then the Link will try to get a record that exactly matches.

So in this example, the Link Query will fetch the record where Studios.Code matches Studio Code of DVD Titles.

Kinds of links

There are several different types of Links, and each of them works slightly differently. All of them bring column variables into the current task, but Link Write and Link Create will also create records as they do so. Below are the different types and what they are good for.

Link Category	Description	When to use it
Link Query	Fetches an existing record.	To bring in an existing record, or to check if the record exists or not. For instance, if you want to give an error message if a customer code doesn’t exist in the customer table, you would use Link Query.
Link Write	Tries to fetch an existing record, but if the record doesn’t exist, it will create it.	When you aren’t sure if the record exists yet or not, but you want to create it if it isn’t already there. For instance, if you want to create a phone number record automatically, if the entered phone number doesn’t already exist.
Link Create	Tries to create a record. Doesn’t check to see if it exists first.	When you are sure the record doesn’t exist already, it is faster than a Link Write. For instance, if you want to create a log record whenever the user opens a certain screen, you could save the user id, date and time stamp when the form opens, and use that in a Link Create.
Link Inner Join	If the joined objects are both SQL tables, this implements an SQL Inner Join.	Wherever you would use an Inner Join in SQL. There are times when this is a faster link, for SQL.
Link Outer Join	If the joined objects are both SQL tables, this implements an SQL Outer Join.	Wherever you would use an Outer Join in SQL. There are times when this is a faster link, for SQL.

The Link Success indication property

The link gives a return code to indicate if the link found a matching record or not. This is entered in the Success indication property. The Success indication flag is commonly used to validate data entered by a user.

To enter a Success indication flag:

1. Create a virtual with a logical attribute (numeric will work also, but logical values are more maintainable).

2. Zoom from the Success indication field, and select your virtual.

The Link Condition

You can use the Cnd: property to prevent the Link from occurring. The Cnd: property can be hard-coded to Yes or No, or it can point to an expression. If the value is No or False at runtime, the Link does not occur. In our example, we use the Cnd: property to prevent the link if the Studio Code is blank.

Using Range on Linked columns

There are Range columns in properties for linked columns, just as there are for columns in the Main Source. Using Range on the Main Source columns will restrain the number of records that are brought into the data view. For instance, if a column in the Main Source is set to:

Range From: ‘01/01/2001’DATE Range To: ‘12/31/2001’DATE

the Range is a set of dates between 01/01/2001 and 12/31/2001, you will only see the records between those two dates.

What is not so obvious is that when you use a Range on a linked source, the effect is very similar. That is, if you used a date range for a linked source of

Range From: ‘01/01/2001’DATE Range To: ‘12/31/2001’DATE

then you will only see Main source records where the linked record has values between those dates.

This is a very useful feature. However, it also means you have to be careful to not enter your Locate values in the Range section. The two sets of properties work very differently from each other.

See also

The Online and Rich Client Samples projects (program DV11 and RDV11)