- Display Association Value Fields

Display Association Value Fields

Introduction

[PRO] In some cases you might want to see some data and the associations as a comma separated list.

Display Associated Value Fields makes that possible. You can use one or more databases to retrieve info

and display results. This special field class runs the association and then displays the value returned.

Click here to view this sample created.

In this Example the Databases and Tables are from:

• pjdata.department

• pjuser70.sample_department

• pjuser70.sample_employee

Example Application name: Display Association Value Field

Note: This Operation pulls department fields from the pjdata database table.

How to create

Step 1: create a Parent Operation:

• • Operation Type property set to: SQL

  • Label property set to: Derived fields for Dept. Mgr & Employees

  • Operation Code:

SELECT 'Managers' AS D_Managers, 'Manager' AS D_Manager, 'Employee' AS D_Employee_ID, d.*

FROM pjdata.department d

Note: In this SQL, there are three created Derived Fields before displaying all fields from pjdata.department.

Created a table qualifier (d), to display all fields from pjdata.department after derived fields (d.*).

example A:

Step 2: create Child Operation (repeat steps below if needed)

Note: This Operation pulls Employees from pjuser70 database table and finds the Employees for each

Department by first name.

• • Operation Type set to: 1-Many

  • Operation Title: Retrieves Employees

  • Operation Code:

SELECT firstnme FROM pjuser70.sample_employee WHERE workdept = ??deptno

example A:

Step 3: creating more Child Operations

Note: This Association Operation pulls Managers from pjuser70 database table and finds the Managers for

each Department by first name.

• • Operation Type set to: 1-Many

  • Operation Title: Retrieves Managers

  • Operation Code:

SELECT firstnme FROM pjuser70.sample_employee WHERE empno = ??mgrno

• • Display Group property set to: The Display Group is the menu group the Operation will appear under.

example A:

Step 4: creating more Child Operations

Note: This Association Operation pulls Managers from pjdata database table and finds the Managers for

each Department.

• Operation Type set to: 1-Many

• Operation Title: pjdata.department manager

• Operation Code:

SELECT * FROM pjdata.employee WHERE empno = ??mgrno

Display Group property set to:

The Display Group is the menu group the Operation will appear under. This was set manually.

example A:

Outcome of this sample Application:

Summary

Create an SQL Operation and add derived fields here. Set the Display Group as desired to appear in the UI.

Next, create Association Operation(s) as needed. Set the Operation Type to: 1-1 or 1-MANY. The determining factor for

which one you use is based on the value(s) being generated and displayed in the field. If it is a

single field such as a department manager, then set to 1-1, if there are multiple values being generated for a

field, then set to 1-MANY.

Finally, set the derived field(s) (by clicking on the FD icon) and setting the Association Operation

according to what value(s) you want this field to display.

Tags: Cross system join, show additional data within row, show association value