27. Auto Population of Fields
Auto Population of Fields
To demonstrate this operation, we will be using the same sample data in PJDATA: the EMPLOYEE and DEPARTMENT tables.
We will be using the DEPTNO field of the DEPARTMENT table as the Target Field to match up with the WORKDEPT field of
the EMPLOYEE table. First, you need to create an operation to display the DEPARTMENT Data, such as:
SELECT * FROM pjdata.department
Next, create a new operation and set the operation type to Auto Populate. An Auto Populate operation
will show up in the list of operations for an application; however, it will not show up in the application itself. Create the SQL
statement that matches up the field with the information to fill the new row.
SELECT WORKDEPT, EMPNO FROM PJDATA.EMPLOYEE WHERE WORKDEPT = ??DEPTNO
The SELECT command retrieves the fields WORKDEPT and EMPNO FROM the EMPLOYEE table. Next, the WHERE statement
specifies which field the association is aligned with. In this example, the WORKDEPT field in the EMPLOYEE table is being
linked with DEPTNO field in the DEPARTMENT table. The link between the two tables is established with the use of the double
question mark (??). This special WOW Builder syntax tells WOW to take the value for DEPTNO from the current row, which is
coming from the DEPARTMENT table. Now the field needs to be notified that it should use the Auto Populate operation. In the
FD Manager, set the Possible Values Operation for the Field that you want to have the retrieve button generated next to. In
this example, this would be the DEPTNO field.
The key to an Auto Populate operation is that it fills in values for other fields in the same row as the field that the operation is
associated with. This is accomplished by specifying a usage ID on the fields that need to be populated. If you are pulling
information from a file that has different fields than the file that your detail Row came from, you must specify the FDs for the
fields that return from the file.
Once you have created these FDs, assign the same usage ids to the fields so they will match up to the fields in the Detail Row
(source Row). Certain usage ids are set aside for special designations such as Email Field (-40), Password Field (-80), and State
Field (-120). Special system usage ids are always negative. You should pick an arbitrary positive # to start your usage ids from.
For this example, the starting value of the usage ID is 5000.
Once again, you must assign a usage ID to each field that is returning from the query, and also assign that same usage ID to the respective FDs in the Detail Row. For instance, if the field name in the Detail Row was WORKNO and the field name in the file that
you are retrieving it from was WORKDEPT, you must assign the Usage ID 5000 to both of those fields so that they will match up
in the copy. The Usage ID is set in the Additional Settings group and the Auto Population operation is set to the possible values
operation in the Possible Value Settings Group (shown below).
Following these steps will generate the "Retrieve" button next to the DEPTNO field on the Insert Screen. Enter a department
number into the DEPTNO field and press "Retrieve".
The query specified in the Auto Populate operation will be executed. Any resulting fields that come back from the query that have corresponding usage IDs in the source Row will be filled in. For example, we used the EMPNO in the SQL statement and it can match up with the MGRNO. The EMPNO and MGRNO fields need to have the same usage ids to match up, we used 501. Now the manager number will fill with employee table data when the retrieve button is pressed with valid department number.
TAGS: auto fill