25. Associated Inserts

Associated Inserts

[EE] An Associated Insert will insert a row or collection of rows into the database, using one or more values from a row in an associated table. It is possible to insert a row where some of the row’s values are dynamically entered by the user and other values are retrieved from a row in an associated table.

Creating an Associated Insert is very similar to doing basic 1-1 or 1-many associations. To create an association, you change the Operation Type from SQL to either the 1-1 Association or 1-Many Association Type. Then you need to set its operation code. After the operation code has been set, you need to modify a Field’s Field Descriptor to set the association, so when the field is generated it will have a link to insert in its associated row or rows. This process is described in detail below:

SQL Associated Insert Example

To create an associated insert, select the Create Operation link from the TOC, and 1-Many for Operation Type. You next have to enter the Operation Code for the associated insert. This is very similar to the code for a normal SQL insert, except that you must specify where to retrieve the associated value for the insert from. For each associated value you wish to insert, you use two question marks followed by the name of the column containing the data in the associated table (not the table where the row is being inserted).

The Code shown below will link the Department table to the Employee table allowing you to insert into the Employee table using the similar fields WORKDEPT and DEPTNO:

The operation code used for the Insert:

INSERT INTO PJDATA.EMPLOYEE (WORKDEPT, EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO, HIREDATE,

JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) VALUES (??DEPTNO,?,?,?,?,?,?,?,?,?,?,?,?,?)

Notice the SQL code is similar to the normal INSERT SQL statement. In this example, PJDATA.EMPLOYEE is the table into which data is inserted. The parentheses hold all the fields which are going to have information inserted. The VALUES clause tells where the values for this row, with columns specified, will come from. In this example, the WORKDEPT field, in EMPLOYEE, is being linked with DEPTNO field, which is from the DEPARTMENT table. The linking is done with the double question marks (??) and the field where the associated data is being retrieved from. The other single question marks in the parenthesis will take user input for the new row.

After creating the Associated Insert, you will see it in your list of operations but will not see it in your application. This is because your operation cannot be directly run. It can only be initiated once an associated row is available. Now you have to assign your association to a specific Field in the associated table (in our example, this is the DEPTNO field). To do this. run an operation that displays 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. Then we will edit the field descriptor of the field in the table to be associated with the insert operation. In our example, we will edit the field descriptor of the DEPTNO field in the DEPARTMENT table.

Once you have a screen similar to the one above, (but without the hyperlinks), you can connect the association you previously created. To do this click the gear symbol next to the field you want to link to your insert operation. In the Field Descriptor Screen scroll down to the Advanced Setting area (shown below) and set the Association Operation to your previously created Operation.

Once you have saved your changes 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 insert into the employees table with the selected department number and asking the user for the other values.