Related Record Joins

In this topic you will learn how to create Related Record Joins.

Navigation: Click the Main Menu link > Reporting Tools > Query > Query Manager

In a related join, you can automatically join two records based on a relationship that has been predefined in the record designer. The Related Record Joins displays as a link to the right of the field. The criteria relationship between the fields has already been identified, so no criterion needs to be added.

In this example you will create a query to retrieve Inventory item ID, Manufacturer's ID, and the Manufacturer's name.

In order to retrieve our data, we will need to join two records, the ITEM_MFG and the MANUFACTURER.

Click the Run tab to preview your query results.

Click the Query tab to join the MANUFACTURER - Manufacturer Table record.

Click the Join MANUFACTURER - Manufacturer Table link

NOTE: Like Hierarchy Joins, Related Record Joins are predefined in the system. The relationship between the fields has already been identified, so no criterion needs to be added.

In the Join Type box, the "Join to filter and get additional fields (Standard Joins) option defaults and should remain as defaulted.

Click the OK button.

NOTE: The (B) alias record has been assigned to the MANUFACTURER record.

Click the Run tab to preview your query results.

Click the OK button.

NOTE: Add Criteria to the SETID and INV_ITEM_ID fields to retrieve a small data sample for testing.

If the query produced the desired results, you would save and name your query.

Click the Save As link.

Enter the constants for Inventory items. In this example you are looking for inventory item ID's between: 102000 - 102500.

Related Record Joins

This completes Related Record Joins.

Enter the desired information into the Description field. Enter

a valid value

e.g.

"ITEM_MFG"

Click the Search button.

Click the ITEM_MFG - Item Manufacturer Table Add Record link.

Click the Fields tab.

Click the Query tab.

Click the Join MANUFACTURER - Manufacturer Table link.

Click the OK button.

Click the DESCR60 - DESCRIPTION Select option.

Click the OK button.

Click the SETID - SetID Add Criteria button.

Enter the desired information into the Constant field. Enter

a valid value

e.g.

"SHARE"

Click the OK button.

Click the INV_ITEM_ID - Item ID Add Criteria button.

Click the button to the right of the Condition Type field.

Click the Fields tab.

Click the Save As link.

Click the Create New Query link.

Click the SETID - SetID Select option.

Click the INV_ITEM_ID - Item ID Select option.

Click the MFG_ID - Manufacturer ID Select option.

Click the between list item.

Enter the desired information into the Constant field. Enter

a valid value

e.g.

"102000"

Enter the desired information into the Constant 2 field. Enter

a valid value

e.g.

"102500"

Click the OK button.

Click the Fields tab.

Enter the desired information into the Query field. Enter

a valid value

e.g.

"RELATED_RECORD_JOIN"

Enter the desired information into the Description field. Enter

a valid value

e.g.

"TEST FOR CLASS"