Tagging comes naturally to Internet users. We tag posts, images, and videos to stay organized. We follow tags to stay up-to-date with the endless information stream. Surprisingly, this common feature is a challenge for developers to implement. Tagging requires many-to-many relationships in the application database and a significant amount of server and client-side programming. Code On Time has an unfair advantage over the competing tools and frameworks since the support for tagging is built into the backend framework and the frontend of applications!
User Experience
Users can filter and search for the data items with the desired combination of tags. A read-only field in a form is rendering tags as a simple comma-separated list. An editable field provides two kinds of tagging input - a dynamic tag list with a dedicated text input or a static checkbox list.
Data Input
Here is the tags input in the reading pane of the employee record shown in the edit mode. Individual tags associated with the employee are outlined in the Territories field. Users can type ahead to assign a sales territory to an employee. Tags are selectable with a tap, click, or with the “arrow” keys on the keyboard. Pressing of the Delete or Backspace key will remove the selected tag from the list.
This is the same data input on a device with a small form factor. The field will expand and shrink vertically to accommodate the selected tags. Users may select all items visible in the dropdown attached to the tag input with a single tap of the Select All option.
Editable tags are outlined and do not provide any controls unless selected. Tapping or clicking on the item will show its context menu. Option Delete will remove the tag from the list. Option View will display a form with the tag details.
Searching and Filtering
Tags are rendered as the static comma-separated list of values when the many-to-many field of a data item is presented in the List view style. The server-side framework is optimized to fetch the tags of the visible data items in a single database request. The tags are sorted in the order specified in the controller, which is responsible for the lookup of the tags.
Data items are frequently presented in the Grid view style. Tags in the cell of the many-to-many field column are also rendered as the comma-separated list.
A tap on the column header will display the context menu that includes the full set of available tags. Users may choose one or more tags to locate the matching set of data items in the current view. Displayed items are guaranteed to have all tags that were selected in the menu. Data items that are not tagged with all selected tags are excluded from the presentation with the help of the smart queries composed by the application framework on the server.
The built-in Advanced Search form is available as an alternative to the Quick Find in every data controller. The form is displayed when users tap the “more” menu on the right-hand side of the standard search input. It allows querying in the many-to-many fields with the sample set of tags. Only the data items that are tagged with the specified values will be displayed in the search output. Users may also search for the tagged data items by the partial tag value with the contains, starts with, or end with options.
Implementing Many-To-Many Relationships
Typical tags input requires a many-to-many relationship in the database. Tables Employees and Territories are linked with the junction table EmployeeTerritories in the database diagram shown below. If a sales territory is assigned to an employee, then a row is created in the EmployeeTerritories table binding together the primary keys of the corresponding parent rows in Employees and Territories. In this particular example the EmployeeID and TerritoryID are also a part of the composite primary key of the EmployeeTerritories table, which prevents the duplicate assignment of territories to the same employee.
The diagram reveals the complexity of the searching and filtering with tags. Tag values in the filer come from the Territories table. Users expect to see the Employees table rows that are linked to multiple rows in the EmployeeTerritories table. The application framework injects the SQL expressions referencing the EmployeeTerritories into the WHERE clause when fetching the Employees data items.
Data models are the building blocks of applications created with Code On TIme. A model defines the set of fields and the source of data. Data may come from the database or external API.
The app builder transforms each data model into a metadata file called “controller”. The data controller provides information used by the frameworks of the application backend and frontend at runtime. The metadata instructs the framework how to fetch and process data on the server. The same metadata tells the frontend how to perform the data presentation. Developers customize the various aspects of the data controllers in the Project Designer. The data controllers produced by the app builder are functional out-of-the-box and may be used frequently without any customization.
It is easy to set up a many-to-many relationship in the app built with Code On Time. Create a data model for each database entity. Then define the many-to-many field in the Project Designer.
“Employees” Model
Set the sort type of the LastName field to Ascending. Borrow the LastName, FirstName, and Title from the self-referring ReportsTo relationship.
“EmployeeTerritories” Model
Make the employee territories sorted by EmployeeLastName and TerritoryDescription. This will improve presentation of data when the available combinations of employees and territories are viewed by users. The data controller based on this model will help the framework to fetch the territory tags associated with the employees. The application framework will use this controller when creating the filtering expressions in response to the search and filter commands initiated by users in the frontend. .
“Region” Model
Follow up with the app builder suggestion to create the Region model. Keep all settings in their default state.
“Territories" Model
Finally, define the Territories data model with the TerritoryDescription sorted in the ascending order. The list of available tags will be handled by the data controller based on this model.
If the territories are not sorted, then the list of territory tags will appear to users without order.
“Territories” Many-To-Many Field
The app builder transforms the models into the data controllers. The fields at the top of a model will migrate to the corresponding data controller. Additional field properties can be defined in the Project Designer. Each controller is provided with the grid1, editForm1, and createForm1 views. A data field is the binding of a controller field to a view. Most fields have an explicit binding in each view. Primary key fields and the lookup aliases are bound to the views at runtime. A collection of actions are helping the controller to orchestrate the runtime behavior of the views.
The virtual field Territories needs to be defined in the Employees data controller and bound to its views.
Start the app generator and select your project, choose the Design option. Select the Controllers tab in the Project Designer, right-click the Employees / Fields node, choose “New Field”, enter Territories in the Name field, set the Type to String, mark the field as “virtual”. Next set the Items Style to Check Box List in the Lookup section, set Items Data Controller to Territories, and the Target Controller to EmployeeTerritories. Save the new field.
Next, bind the new field to the views. Drag the Employees / Fields / Territories node onto grid1, editForm1, and createForm1 views. Drag each instance of the Territories data field in the views to be positioned after the Title data field.
Note that only one instance of a field can be bound to a view.
Presentation Styles
If the total number of tags is not significant, then it may be beneficial to have all tags presented to users in the edit mode. Otherwise only the selected tags must be displayed and an option to quickly assign additional tags must be provided.
Check Box List
A collection of checkboxes is displayed, if the Items Style of the Territories field is set to Check Box List in the data controller. Users will see the horizontally scrollable set of tags for this configuration.
Auto Complete
A simple change of the Items Style value to Auto Complete will transform the field presentation to the compact list of territory tags assigned to an employee. Users can typeahead in the attached text box to search and assign the territories. The input field will exclude the duplicate assignments automatically. Users will see the available tags only when they start typing. The top 200 tags are displayed when the dropdown arrow is tapped or clicked.
Drop Down List
Changing of the Items Style in the definition of the virtual field Territories will introduce a slight change in the behavior of the frontend. All available tags will remain visible in the dropdown as users search for the territories. The frontend will fetch the full set of territories when the form is displayed. In contrast, the auto complete configuration will allow the app to fetch the matching tags on-demand.
Lookup
If the tags have additional meaningful properties beside the tag name, then the Lookup is recommended as the Items Style of a many-to-many field. From the user perspective, the input behavior is identical to the Auto Complete. There is also the See All option at the bottom of the dropdown menu.
The form with the list of available tags is displayed when the See All option is chosen. Tag properties Territory ID (zip or postal code), Territory Description, and Region Description will be visible in the grid. Users may locate tags with Quick Find or Advanced Search forms specific to the Territories controller. For example, users may locate tags of the Southern region.
If the New Data View property of the Territories field in the Employees controller is set to createForm1, then users will be able to create new territories in the See All form or do so directly in the many-to-many input when they choose the Create New option in the dropdown menu of tags.
The form with the details of the new territory will allow users to enter the postal code, description, and region of the new tag. The new territory will be immediately assigned to the employee.
Master-Detail vs Many-To-Many
Tags input may be added to a data controller form view with a relatively minor effort. Tags are perfect when a single word or phrase can convey to users the meaning of the data association. For example, the New York and Providence in the Territories field will clearly indicate to users that the employee is responsible for these territories.
If more than one attribute is required to describe the relationship of the data item and the tag, then implement it as the simple DataView field instead. The master-detail relationship will be a better choice than the many-to-many association.
Select your project on the start page of the app generator and choose the Model option. Select the Employees data model. Click the master-detail option on the toolbar of the model builder. The list of suggestions will have the EmployeeTerritories field. The suggestion is prompted by EmployeeTerritories.EmployeeID foreign key column in the corresponding table.
Click on the EmployeeTerritories suggestion and the prompt will appear to confirm that the detail relationship will be created in the Employees data model.
The suggestion will migrate to the Details list in the Master-Detail sidebar of the model. The EmployeeTerritories field is based on the EmployeeTerritories model. Click on the field name in the details to configure some of its properties. Click the base model to see the underlying data model.
Save the Employees model and press Finish. Proceed to generate the app. The Employees data controller will have the EmployeeTerritories field with the DataView type added to the list of fields. Views createForm1 and editForm1 will have the binding to this field.
Run the app and select an employee. The list of linked territories
Inspect the EmployeeTerritories data field in editForm1 view. The app generator places the DataView field into the dedicated category d1. Developers may drag other fields to this category or move the data field to the default category c1 as needed. The position of the data field in the categories will be reflected in the user interface when the app is re-generated.
Model Builder provides the hassle-free method of adding the DataView field to a controller. Another alternative is to create the DataView field in the Project Designer.