In this example, we will create an application that updates the tables in a many-to-many relationship using the checkbox object, see the image below. Describes a request form that uses three tables simultaneously an NN relationship in a database.
1. The check box form field will be fed by the Project table.
2. The form data will be stored in two tables: Employees and EmployeeProjects.
The data referring to employees will be recorded in the Employees table and the data selected in the check box will be recorded in the EmployeeProjects table (employeeid, projectid).
Creation of the Employees form.
1. Create a new application form based on the employee table.
2. The table width property reports the value 350 and the Unit table width property selects Pixel.
3. In the application menu, click Field Positioning and delete all fields except the firstname.
4. We will now assign value to a field that is out of the form view. In the application menu, click Edit Fields.
5. In the employeeid field, select the option Automatic increment (automatic).
6. In the application menu to access the Design folder and click on the Blocks item.
7. Change the following properties of the existing block:
Creating a many-to-many relationship
8. On the application menu, go to the NN Relationships folder and click on the New Relationship item.
9. Start the wizard to create the relationship field. In the property field, report projects for the data type, and report Projects for label. Click on the Next button.
10. Click Choose Table and select the project table, and then click the Next button.
11. Check the generated SQL command, if necessary you can modify it. To continue, click on the Next button.
12. In the property object, select the checkbox option. It also changes the Columns property to 1.
After selecting the checkbox, the Mark and uncheck all option is enabled.
13. Selecting the relationship table. In the Table Name option, use projects
14. Relate the fields of tables, employees and employment projects. For the employeeid field select employeeid and the pvalue field choose Auto Increment.
We define the field as pvalue Auto Increment so no value is passed for this field in the Employeeprojects table.
15. This step will be related to the foreign key field. Select the projectid field and click Next.
16. Screen settings. Change the Vertical alignment property to Superior and click the Next button.
17. Click the Finish button to complete the Wizard.
18. On the ScriptCase toolbar, click the Generate Source Code button.
Creating the grid of employees.
19. Create a new grid application based on the employee table.
20. In the Table Width property, enter the value 600 and the Unit Table Width property select Pixel.
21. On the application menu, click Field Positioning and delete all fields except employeeid and firstname.
Making a connection between the grid and the form
22. On the application menu, go to the Links folder and click on the New Link item.
23. Select the link application and click on the Next button.
24. Select the employee form created in the previous steps and click Next.
25. Passing parameters. Enter the employeeid field of the grid employeeid field.
26. Link properties. Change the properties according to the image below and click on the Save button to finish the wizard.