Joining Data on Blocks

Joins are used to restrict the data displayed on the child block according to a comparison between a column on the child block and a column on the parent block.  When you are creating entities with multiple blocks, often the information on the child block is dependent on the data on the parent block.  This dependency works for both displaying existing records on the child block and entering new records on the child block, as those new records should also be joined to the parent record.

• For example, if you were creating a block that displayed a customer record, and you had another block attached to the customer record block that displayed the contacts for that customer record, want the contacts displayed would be dependent on the customer number.  

This is handled in Xephr® by using joins .  The join is defined on the child block and defines the parent block and column that is joined to one of its own columns.  There are several kinds of joins, although equal joins are the most commonly used.

When creating blocks on an entity within the wizard, equal joins are created automatically for child blocks that have the Key Field property set to true for a column that is named the same as a key column on the parent block.  The join is created with the name the same as the column.  This join is created with an equal operator.

• For example, in our customer/contact example, the cust_no column on the parent block is a key field, and when the child block was added to the parent block, its cust_no column was also marked as a key field.  When the entity was created, the join created was called cust_no and defined the join between the customers block and the contacts block.

When an equal join is created for the child block, the data displayed on the child block will be restricted to that which has the same value in the from column on the child block as the value in the to block and to column.  Also, when new records are created, the from column in the child block will be automatically populated with the value from the to block and to column.  

• For example, in our customer/contact example, only contacts with the same customer number as the record in the customer block will be displayed, and new records will be assigned the customer number from the parent block automatically.

Any block that is displayed above the child block can be joined to the child block.  Blocks on the same level as the child block cannot be joined to the child block.

Joins with Null Values

Joins with null values work as follows:

• If the parent and detail values to be joined in all join columns are null, then nothing is returned.

• Suppose that some of the join values are null, some of the join values are not null, and the join key is assigned the = operator.  When the parent record's value is null in the join column, it will join to the detail records that have a null value in the same join column.  
  
  For example, suppose the parent record join columns were item and control number both with operator =, and the detail record was displaying item locations by item and control number. If the parent record's control number is null, it will display all of the item location records for which the control number is null for that item.  If the parent record's control number is not null, it will display all of the item location records that have a control number that matches the control number in the parent record.

• Suppose that some of the join values are not null, some of the join values are null, and the join key is assigned the != operator.  When the parent record's value is null in the join column, it will join to the detail records that do not have a null value in the same join column.
  
  For example, suppose the parent record join columns were item with operator = and control number with operator !=, and the detail record is displaying item locations by item and control number.  If the parent record's control number is null, it will display all of the item location records for which the control number is not null for that item.  If the parent record's control number is not null, it will display all of the item location records for which the control number is null for that item.

• For an other join operator, no records will be returned if some of the join values are not null and some are null.

Creating Joins

1. In the Explorer Tree, locate the object to which you wish to add a join.  

• Expand the Template Information object, locate the desired template and expand it, and then locate the block to which you wish to add a join..

• Expand the Group Information object and any sub-groups until you can select the desired screen and expand it.  Then locate the block to which you wish to add a join.

2. Right-click on the block, hold your cursor over the New option, and when the sub-menu is displayed, drag your cursor over the Wizard option.  The final sub-menu will be displayed.  Left-click on the Join option.  

3. The Join Creation Wizard will be displayed.

4. In the From Column property, select the column on the child block that will be joined to the parent block.

5. In the To Block property, select the block that contains the column to which the from column will be joined.

6. In the To Column property, select the column to which you are joining the from column.

7. The join will be created and added to the block.  Joins are created as equal joins automatically.

Modifying Joins

1. In the Explorer Tree, locate the join that you wish to modify.

• Expand the Template Information object, locate the desired template and expand it.  Locate the block on which the join exists and expand it.  Select the join that you wish to modify and display the Property Sheet for it.

• Expand the Group Information object and any sub-groups until you can select the desired screen and expand it.  Locate the block on which the join exists and expand it.  Select the join that you wish to modify and display the Property Sheet for it.

2. Modify the join properties.  These will either be displayed in Alphabetical or Logically, depending on the setting of the [Sort Alphabetically/Logically] button.  The following instructions are assuming that the properties are sorted logically.

3. In the Name field, enter the name for the join.  The default is the name of the from column.

4. In the From Block field, the name of the child block is displayed.  This value cannot be changed.

5. In the From Column property, select the column on the child block that will be joined to the parent block.

6. In the To Block property, select the block that contains the column to which the from column will be joined.

7. In the To Column property, select the column to which you are joining the from column.

8. In the Operator property, select the join operator.  The selection here determines how the data on the child block will be restricted for this join.  Choices are:
   
   Equal - where a = b
   Not Equal - where a != b
   Less Than - where a < b
   Less Than or Equal - where a <= b
   Greater Than - where a > b
   Greater Than or Equal - where a >= b
   Like - where a like b
   Not Like - where a not like b

9. Click on the Save button in the main toolbar to save the changes.

Xephr is a registered trademark of NDS Systems, LC.

Copyright © 2007 NDS Systems LC.