Many-To-Many Wizard

The Many-To-Many Wizard is useful when you are working with many-to-many related tables. A typical example of a many-to-many relation is students registering to classes: a student can register to more classes, while a class is attended by more students.

When dealing with many-to-many relations, another database table must be used: a many-to-many table (linking table). Its role is to link students and classes (for our example). Thus, it stores records formed by associating one student to one class (that he/she registered to). This linking table has two columns, namely the primary keys of the two tables involved in the many-to-many relation. It also prevents duplicated records from occurring: the information about a student is only once stored, in the students table, and not for every class that he/she attends.

In order to better understand  the role of the linking table (many-to-many table) when working with many-to-many related tables, let's consider the following example. Say we are working with the student_std and class_cls tables. If we stored the classes each student registered to in the same table as the students, the student_std table would have the following columns: id_std, name_std, classes_std.

1. In the classes_std column we could store the ID's of the classes that the student registered to. Now since there are more classes, the values will be stored as a string of values or text. For example, the next records could be found in the student_std table:
   
   · 1; Ashley Crain; 1, 2, 3
   · 2; Danny Morrison; 2, 4, 6, 8
   · 3; Loni James; 1, 5
   
   Since students do not attend the same number of classes and since attended classes are stored in one database column, there will clearly be difficulties in retrieving the classes that each student attends. So another storing method should be found.

2. In the classes_std column we could store the ID of a single class attended by the student. So for each class, we would have a corresponding record. Considering the example above, the student_std table would look like this:
   
   · 1; Ashley Crain; 1
   · 1; Ashley Crain; 2
   · 1; Ashley Crain; 3
   · 2; Danny Morrison; 2
   · 2; Danny Morrison; 4
   · 2; Danny Morrison; 6
   · 2; Danny Morrison; 8
   · 3; Loni James; 1
   · 3; Loni James; 5
   
   Duplicated information inevitably occurs and this is not something that we want for our database.

3. By using a linking or many-to-many table that has two columns (the primary keys of the two main tables), all the inconveniences presented above will be gone. The stdtocls_stc table has three columns : : idstd_stc, idcls_stc and level_stc.  Continuing with the example above, the records stored in this table will be the following:
   
   · 1; 1;1
   · 1; 2;1
   · 1; 3;1
   · 2; 2;2
   · 2; 4;1
   · 2; 6;0
   · 2; 8;2
   · 3; 1;2
   · 3; 5;0
   
   As you can see, this is with no doubt the best way to store data in your database when working with many-to-many related tables.

By using the Many-To-Many Wizard, you can insert data in one of the main tables (the master table) taking into account the many-to-many relation to the other table (the detail table). Automatically, the needed records will be inserted in the linking table so that the database logic will not be affected.

The Many-To-Many Wizard can be applied in a page where an Insert, Update or NeXTensio form already exists. The wizard will generate the Many To Many Trigger and a Nested Repeat region:

• The trigger is not editable, so if you want to change any of the settings, undo the last action (if you just applied this wizard) and apply the wizard again. Since with MX Kollection 3 you benefit from user interface persistence, there shouldn't be a problem when changing that one or few settings, while the rest remain unchanged:
  
  
   

• If you do not have MX Looper installed, the Nested Repeat server behavior will not be recognized, so make sure you have that product installed.

The Many-To-Many Wizard is accessible from two locations:

• The MX Kollection tab of the Insert bar.
• The Application panel, Server Behaviors -> + -> MX Kollection -> Forms -> Many-To-Many Wizard.

The Many-to-Many wizard in MX Kollection 3 also allows you to have other fields in the many-to-many table as well. Considering the example above, this wizard will match the case where your linking table also has to store, say the level for each student for a particular class. This way you can differentiate between beginners, advanced or graduates.

The many to many wizard complies with its predecessor restrictions and can be accessed from the same places: the Insert bar > MX Kollection tab and the Server Behaviors tab > Plus (+) > MX Kollection > Forms.

The wizard has two steps: in the first step define the master, many-to-many and detail table. In the second step define any supplemental fields that you want to fill in for the many-to-many table.

To configure the first step options correctly, read the instructions below.

1. In the Master table drop-down menu select the table on which the Insert/Update/NeXTensio form in page is based. This is the table that will have records inserted or updated, taking into account its many-to-many relation with another table.

2. In the Many to many table drop-down menu select the linking table between the two many-to-many related tables. The linking table should contain two columns that store the primary keys of the main tables.

3. In the Key to master table drop-down menu select the table column (of the many-to-many table) that makes reference to the primary key of the master table (the one used in the Insert/Update/NeXTensio form).

4. In the Key to detail table drop-down menu select the table column (of the many-to-many table) that makes reference to the primary key of the detail table. This table's records will be displayed as checkboxes in the form in page. For the example considered, it offers the possibility to specify all the classes that a student registers to.

5. In the Detail table drop-down menu select the detail table, namely the one whose records will be displayed in the form (as checkboxes) and chosen from. The selection of one or more elements will be associated to the master record.

6. In the Primary key column drop-down menu select the primary key column of the detail table.

7. In the Get labels from drop-down menu select the column that stores the labels that you want displayed besides the checkboxes in browser.

8. In the Number of columns text box specify how many columns you want there to be when all the records from the detail table are displayed as checkboxes.

9. The buttons in the lower part of the interface offer the following functionalities:
   · The < Back / Next > buttons are disabled, the wizard having only one step.
   · Click Finish when you are done configuring the wizard.
   · Click Cancel to exit without the new settings to be applied.
   · The Help button brings you to this help page.

To configure the second step options, follow these instructions:

1. In the Form fields grid you can see the list of fields that will be added onto the page. You can add fields by clicking on the Plus (+) button and selecting the field from the list. Also, to remove a field simply select it and click the Minus (-) button. To configure a field select it in the grid and use the controls below the grid to change the options.
2. In the Label text box enter the text to be displayed as header for the field.
3. In the Display as drop-down menu select a form object to serve as the data-entry field for the current selection in the grid. You can choose from the following list: Text field, Text area, Menu, Hidden Field, Check box, Radio group, Password field, Text, File field. To read the detailed description for each of these options, click here.
4. In the Submit as drop-down menu select the column type.
5. In the Default value text box enter the value that the column will use by default. You can either enter static data or use the InterAKT Dynamic Data interface to select a dynamic value.