In this example we will create an application that updates tables in a many to many relationship using checkbox object, observe the image below. It describes an application form that uses three tables simultaneously an N-N relationship in a database.
1. The checkbox form field will be powered by the Project table.
2. The form data will be stored in two tables: Employees and EmployeeProjects.
Data referring employees will be recorded in the Employees table and the data selected in the checkbox will be recorded in EmployeeProjects table (employeeid, projectid).
Creating the Employees form.
1. Create a new application form based on the employees table.
2. Table width property inform the value 350 and the Table Width Unit property select Pixel.
3. In the application menu click in Fields Positioning and remove all fields except firstname.
4. Now We will assign value to a field that is out of view of the form. In the application menu click in Edit Fields.
5. In employeeid field select the Auto Increment (Automatic) option.
6. In the application menu to access the Layout folder and click on the item Blocks.
7. Change the following properties of the existing block:
Creating Many-to-Many relationship
8. In the application menu go to N-N Relations folder and click the New Relationship item.
9. Starting wizard to create the Relationship field. In the property field inform projects for Data Type choice number and inform Projects for Label. Click the Next button.
10. Click on the 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 the Next button.
12. On the property object select the checkbox option. Also change the Columns property to 1.
After selecting the checkbox is enabled Mark and Uncheck All option.
13. Selecting the relationship table. In Table Name property choice employeeprojects.
14. Relating the fields of tables, employees and employeeprojects. To the field employeeid select employeeid and the pvalue field choose Auto Increment.
We define the field as pvalue Auto Increment so it is not passed any value for this field in employeeprojects table.
15. This step will be related the Foreign Key field. Select the projectid field and click Next.
16. Display Configuration. Change the Vertical Alignment property to Top, and click the Next button.
17. Click the Finish button to complete the Wizard.
18. In ScriptCase toolbar click in Generate Source Code button.
Creating the Employees grid.
19. Create a new grid application based on the employees table.
20. In Table Width property inform the value 600 and the Table Width Unit property select Pixel.
21. In the application menu click in Fields Positioning and remove all fields except employeeid and firstname.
Making a connection between the grid and the form
22. In the application menu go to Links folder and click the New Link item.
23. Select the link Application and click the Next button.
24. Select the employees form created in the previous steps and click Next.
25. Passing parameters. Give the field employeeid form the grid employeeid field.
26. Link properties. Change the properties according to the image below, and click the Save button to finish the wizard.