Friday, 6 September 2013

Making Adding/Removing Related Records Easier


Xataface has supported 1:n (i.e. one-to-many) and n:m (i.e. many-to-many) relationships from the beginning.  By default, when a relationship is defined, it causes a corresponding tab to appear in the UI for each record of the originating table.  E.g. If I define a relationship named "Courses" on the `Students` table that represents all of the courses for which a student is registered (i.e. a 1:n relationship from the `Students` table to the `Courses` table), it causes a "Courses" tab to appear inside the details view of a Student record.

The relationship tab will include a list of all records in the relationship as well as buttons to add or remove related records.
The "Courses" tab showing the records in the courses relationship for a particular student.

View the source for this example application on GitHub

Editing Relationships from the Edit/New Record Form

Adding related records one at a time using the "Add New Related Record" form can be cumbersome, especially in cases where you routinely need to add multiple records to the relationship for every record in the originating table.  Xataface provides the ability to add and remove related records directly on the "edit" and "new" forms of the originating table.  In cases where the size of the relationship is small, this makes a lot of sense, as it puts all of the "editing" functionality in one place for the user.

The following widget types can be used to edit relationships:

Widget Type Supports 1:n Supports n:m
gridYesYes
checkboxNoYes
taggerNoYes

Using the grid Widget

The grid widget allows you to add, remove, and edit related records in a grid with one row per record.    The configuration happens in the originating table of the relationship, inside its fields.ini file.  The fields.ini definition a grid widget looks like:


[Courses]
    transient=1
    widget:type=grid
    relationship=Courses
    widget:columns="subject,course_number,course_title"


The key elements of this definition are:

  • The "transient" directive tells Xataface that this field is not backed by an actual column in the Students table.  (It is sort of a way of saying to XF "No, I'm not loopy!  I realize that there is no such column, but I want this anyways!)
  • The "relationship" directive specifies the relationship that should be used to populate the grid.  It happens to be the same as the column name we've chosen for the transient field, but it doesn't necessarily have to.  We could have named this field anything we like.
  • The "widget:columns" directive is a comma-delimited list of the columns in the related table that should appear in the grid.
(Please note that the example here using a 1:n relationship from students to courses is somewhat unrealistic - it assumes that the course records are not entities themselves, but just value records that are entirely owned by a student.  In the real world you would probably have a much more complex table structure - and most certainly would be using a n:m relationship between students and courses).

After adding this configuration, let's visit the "Edit" form of a Student record.  It should display our grid widget as follows:

Using the grid widget to edit the Courses relationship.  In this case the courses relationship is a 1:n relationship so it doesn't allow you to add existing records.


When you enter a value into any field of the last row, it will create a new empty row at the end of the widget for you to add a new record.  In this way, you can add as many related records as you like.

Using Checkboxes

If you are using a n:m relationship and you want to provide a simple way for the user to indicate which records from table A are related to which records in table B, you can appropriate the checkbox widget to generate a simple checkbox grid from which the user can select the related records.  The configuration for this might look as follows (assuming that the "Courses" relationship is now an n:m relationship):


[Courses]
    transient=1
    widget:type=checkbox
    relationship=Courses


Notice that this definition uses the same key elements as the grid widget, but it doesn't require a "widget:columns" directive because it is only displaying the "title" of each related record - no need to specify columns.

After adding this configuration, we can visit the "Edit" form for a Student record, and it should display our widget as follows:

Using the checkbox widget to edit the courses in the Student's courses relationship.

Now you can specify which courses a user is registered in by simply checking the courses' checkboxes on the Student edit form.

View the source for this example on GitHub

Using the Tagger Widget

The tagger widget can also be used to manage n:m relationships.   It provides an intuitive "Tagging" interface for the user.  To use it, you will need to install the tagger module.   For more information about the tagger widget see this post.

Caveats

This article glossed over a few items that you will likely face if you set up your relationships to be edited by a widget.  Some of these include:

  • Transient widgets are displayed as one of the first fields on the form by default.  You may want to adjust the field order to move it to a more appropriate place.
  • Now that you are providing a way for users to add and remove related records on the edit form, you may want to disable the standard way of adding records in the related tab.
  • For some relationships, it is sufficient to just have it displayed in the edit form.  You may want to hide the default relationship tab altogether, or to move the related list inside the "View" tab as a section.
You can find information on most of these issues in the Xataface forum and wiki.  And I hope to write more articles on these in the future so stay tuned.

Source Code

All of the source code for this article is available on Github.  The repository includes 4 branches:

  1. Master - The default application with a single 1:n relationship from Students to Courses with standard relationship management.
  2. grid_widget_1_to_n : Version of the application that uses a grid widget to edit the Students->Courses relationship on the Student edit form.  Still using a 1:n relationship.
  3. grid_widget_n_to_m : Changes the table structure and relationship to n:m; still using grid widget for editing the relationship.
  4. checkbox_widget : Same as the grid_widget_n_to_m branch except using a checkbox widget for adding/removing related records on the Student edit form.


2 comments:

  1. If you want your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you must watch this video
    right away...

    (VIDEO) Get your ex back with TEXT messages?

    ReplyDelete