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 Layout 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 Connection Table. In the Table Name property, choose employeeprojects
14. Relating the fields of the employees and employeeprojects tables. For the EmployeeID field select EmployeeID and for 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 finish the Table List.
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. Click the Create New Connection button to start the connection settings
24. Select the Field Connection and use the EmployeeID field and then click the Proceed button
25. Select the employee form created in the previous steps and click Proceed.
26. Passing of parameters. Assign the form’s EmployeeID field to the query’s EmployeeID field
27. Connection properties. Change the properties according to the image below, and click the Save button to finish the connection settings.
28. Run the application
Comment this post