24. Creating Associations

Creating Associations

Creating an association is very similar to creating any other type of Operation. The first thing you need to do is create an Operation. To create an association you change the Operation type from SQL to one of the association operation types. Association operations have the word "Association" in their display name. Then, you just set its operation code. After the operation is set, then you need to modify a field's Field Descriptor to set the association, so when the Field generates, it will have a link to the association. The two examples below show how to create both SQL and HTML associations.

SQL Association Example

For an SQL association, the operation type should be either a 1-1 Association or a 1-Many Association. The screenshot below shows an example an SQL 1-Many association:

The Type and Operation Code are the two most commonly used fields when creating an association. The code used to create an association may vary depending upon the type of association you are creating (for instance, HTML Associations are different from SQL associations). The screenshot below shows an SQL association. You only need to pay attention to the Operation Code. The code shown will link the DEPARTMENT table to the EMPLOYEE table using the similar fields WORKDEPT and DEPTNO:

The operation code used for this association is:

SELECT * FROM pjdata.employee WHERE workdept = ??deptno

Notice the SQL code is similar to a SELECT SQL statement. The first thing you need to notice is the table it is selecting from. This table contains the information which we will link to. Next is the WHERE statement, this statement shows which field the association is being linked from, in this example the WORKDEPT field in EMPLOYEE is being linked with the DEPTNO field which is located in the DEPARTMENT table. The linking of the two fields is done by using an equals (=) sign followed by double question marks (??) and the field the association will be linked from.

The DEPARTMENT table is not mentioned anywhere in the code because the association link will be visible in any query on the DEPARTMENT table. After inserting the Association you will see it listed in the group of other Operations that you have created for your application. The final step to creating an association is to assign your association to a specific field. To do this run an Operation to display the table you are using for your association; in the example above, we are using the DEPARTMENT table so we will run the Operation to display the DEPARTMENT table. Your query should look similar to the screenshot below, substituting the table you are using with the DEPARTMENT table:

Once you have a screen similar to the one above, but without the associations, you can set up the association you previously created. To do this click on the gear icon next to the column you want to use along with your association. In this example, we will click the gear icon directly to the right of the DEPTNO column as shown below:

The 'gear' icon located next to each column is used to edit the Field Descriptors of each field. For now, all you will have to do is locate the association operation field which is found under the Advanced Settings section.

To activate the newly created association, pick the name that you gave your newly created association operation. In this example, we will pick the "WorkDept Assoc" operation as shown below:

Once you have saved your change to the field descriptor, the association is complete. Now, whenever the DEPTNO field of the DEPARTMENT table is displayed, it will have a hyperlink to the employees associated with that department as shown below.

Note: Consider creating a derived field for any Association Operation you wish to use or set on a field. This will greatly save you time in the future should you choose to use these fields for multiple purposes. Using your original fields from your database can and will affect any other types of actions you wish to add to those fields in the future such as Possible Values or Hover as well as just simple queries that will display a different value than desired.

Conditional Associations

The SQL CASE keyword can be used to make an association dependent on a columns value. In the following example:

SELECT

CASE WHEN RunStatusID = 0

THEN '<p><span style="background-color:LimeGreen; color:black;">START Processing</span></p>'

ELSE NULL END AS D_Start_Processing, ............

The derived field "D_Start_Processing" has an association but will only show text if RunStatusID = 0.

TAGS: Link data between tables, linking data