Standard (Any) Joins

In this topic you will learn how to create a Standard (Any) Join.

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

Query Manager allows the creation of a join between two records (any record join) by selecting your initial base record, then returning to the Record page to select the second record.

Unlike Hierarchy Joins, Standard (Any) joins are not predefined in the system. You must manually join the tables/records when using a Standard (any) Join.

NOTE: When creating a Standard Join, the records you wish to join must have a common key field for joining. Query will attempt to automatically join the records based on the keys in each record.

Click the Search button.

In this example, you will join the PO_HDR record and the PO_LINE_DISTRIB record to retrieve Purchase orders between a date range for a specific business unit, and find the merchandise amount and the department ID number.

Click the Records tab to select the other record to join to the PO_HDR record.

In the Join Type section, the system defaults to the "Join to filter and get additional fields (Standard Join)". This should remain as defaulted.

Click the Add Criteria button.

Click the Run tab to preview your query results.

Click the Save As link to save your query.

Click the OK button.

The results for the Standard Join query displays.

Enter the following date range: 01/01/15 - 01/31/15

NOTE: When creating queries it is optimal to select a smaller data sample for testing, such as 01/01/15-01/31/15. This will result in a quicker run time, and make it easier to test and troubleshoot your query.

Click the Fields tab.

Enter the name of your query and if desired, enter a longer description of the query in the Query Definition box.

Standard (Any) Joins

This completes Standard (Any) Joins.

Click the Create New Query link.

Click the Search button.

Click the PO_HDR - Purchase Order Header Add Record link.

Click the BUSINESS_UNIT - Business Unit Select option.

Click the PO_ID - PO Number Select option.

Click the PO_DT - Purchase Order Date Select option.

Click the Fields tab.

Click the BUSINESS_UNIT Add Criteria button.

Enter the desired information into the Constant field. Enter

a valid value

e.g.

"LSUNO"

Click the OK button.

Click the PO_ID - PO Number Add Criteria button.

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

Click the Records tab.

Enter the desired information into the Description field. Enter

a valid value

e.g.

"PO_LINE_DISTRIB"

Click the Search button.

Click the PO_LINE_DISTRIB - PO Line Accounting Entries Join Record link.

Click the A = PO_HDR - Purchase Order Header link.

Click the Add Criteria button.

Click the MERCHANDISE_AMT - Merchandise Amount Select option.

Click the Save As link.

Click the OK button.

Enter the desired information into the Description field. Enter

a valid value

e.g.

"PO_HDR"

Enter the desired information into the Date field. Enter

a valid value

e.g.

"010115"

Enter the desired information into the Date 2 field. Enter

a valid value

e.g.

"013115"

Click the OK button.

Click the Fields tab.

Click the between list item.

Click the DEPTID - Department Select option.

Enter the desired information into the Query field. Enter

a valid value

e.g.

"STANDARD_JOIN"

Enter the desired information into the Description field. Enter

a valid value

e.g.

"TEST FOR CLASS"