In this step you will enable the user to insert new data into the xDepartments table.
You will add an Insert push button to the form along with the field STD_NUM. The STD_NUM field is not used in the Department table (xDepartments) but will used to demonstrate program level validation rules. You will also use a GROUP_BY statement to simplify the code in your form.
1. Drag the field STD_NUM field to the form.
a. Give it an Alignment of Top Left and Flow of Down.
b. Give it a margin Top of 10 and margin Left of 20.
2. Drag a push button onto row 1, column 2.
a. Give it an Alignment of Bottom Center and Flow of Up.
b. Give it a margin Bottom of 10.
c. Set the button Name and Caption to Insert.
d. Create a Click event routine for the INSERT button.
Note: The controls on a form (fields, push buttons etc) have a TabPosition property. As you add additional controls to the form you should ensure their TabPosition is set to an appropriate value. For example, the three fields should have a TabPosition property of 1,2 and 3
3. Your form should appear like this:
4. For each of the database commands, you will be including a Fields() parameter to fetch, insert, update the required fields. To simplify your I/O statements, add a GROUP_BY command after your DEFINE_COM statements so that you can refer to all the fields by the group name:
group_by Name(#formdata) Fields(#xDepartmentCode #xDepartmentDescription #STD_NUM)
Once added, you should change the FETCH command as follows:
FETCH FIELDS(#FORMDATA) FROM_FILE(xDepartments) WITH_KEY(#xDepartmentCode)
Note: The STD_NUM field can be included in the GROUP_BY used by the FETCH even though it is not a field in the xDepartments table. In database operations the STD_NUM field will be ignored, but used in other operations where the Group_By is used.
5. In the INSERT.Click event routine, add an INSERT command to add a new record to the table.
Remember to add the appropriate error checking. When the INSERT completes successfully, all fields on the form should be reset to their repository defaults.
Your finished code should appear as follows:
Evtroutine Handling(#INSERT.Click)
Insert Fields(#formdata) To_File(xDepartments) Val_Error(*next)
If_Status Is(*OKAY)
Message Msgtxt('Department inserted successfully')
#formdata := *default
Else
If_Status Is(*ERROR)
Message Msgtxt('Error inserting department')
Endif
Endif
Endroutine
Note: The INSERT command has other parameters that are not shown in the editor when they have a default value. The INSERT command you have created looks like this with default parameter values shown:
INSERT FIELDS(#FORMDATA) TO_FILE(xDepartments) IO_STATUS(*STATUS) IO_ERROR(*ABORT)
VAL_ERROR(*LASTDIS) ISSUE_MSG(*NO) RETURN_RRN(*NONE) CHECK_ONLY(*NO)
AUTOCOMMIT(*FILEDEF)
6. Compile and execute the form.
a. Leave the Department Code and Description blank and press the Insert button.
Notice that fields in error are highlighted and error messages are displayed.
b. Review the error messages. They are caused by field and table level validation rules in the Repository. When the INSERT fails due to these repository errors, the program automatically returns to the last display. In a component, this means on a validation error, the program goes to the Endroutine statement. This behaviour controlled by the default VAL_ERROR(*LASTDIS) parameter on the INSERT command.
c. Enter a Department Code of 1000 and press the Fetch button to retrieve an existing record.
d. Press the Insert button to try to duplicate this data in the database.
Notice that no fields are highlighted in error as the repository validation rules have been satisfied. The error message is automatically generated by LANSA for a duplicate key error.
e. Enter a Department Code of 50 and enter a description of Dummy. Enter the number 999 into Standard Number. Press the Insert button.
(Remember STD_NUM is not used in the xDepartments table.)
The new record should be inserted into the database, a message displayed, and all fields reset to their default.
7. Close the form.
