Excel-Friendly CSV Files

Xataface has supported CSV export since the beginning, but I have recently been made aware that the CSV files that Xataface produces haven't been "MS Excel" friendly when it comes to UTF-8 special characters.  You may have noticed that if you export a data set as CSV from Xataface where some records include special characters (like é and å), and try to open it with MS Excel, the characters become garbled.  These CSV files work fine with Open Office, Google Docs, and pretty much every other spreadsheet program out there.  It's just that MS Excel doesn't seem to support UTF-8 encoded CSV files.

(Note: You can still import them into Excel using its import features, where you can specify the encoding explicitly, but if you try to simply open the CSV file with Excel, it will assume that the encoding is Mac Roman or Windows 1252 depending on whether you're on Mac or Windows).

Many solutions were tried to try to make these CSV files work nicely with Excel, but it seems that Excel simply can't handle UTF-8!

Today I implemented an experimental feature in Xataface to be able to export data to CSV that Excel can handle.  It works by using UTF-16 encoding instead of UTF-8 as well as some other settings specifically designed to cater to Excel (e.g. tab delimiter instead of commas, and a different mimetype).

You can enable this feature by adding the following to your application's conf.ini file:

[export_csv]
    format=excel

Then you can just use the export CSV action as normal (e.g. from list view click "Export" > "CSV" if using the g2 theme, or click the "Export CSV" icon in the upper right of the list view if using the default theme).

This change is currently in the master repository on GitHub and will be included in the 2.0.3 release.

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
grid	Yes	Yes
checkbox	No	Yes
tagger	No	Yes

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.

View the source for this example on Github

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.