Tuesday, October 1, 2013

Spreadsheet-like forms

Many of us are familiar with using spreadsheets with rows and column of cells. Sometimes we also want to gather data in a form that behaves similar to a spreadsheet with data categorized in two dimensions using regular rows and columns. In some cases, the default behavior of XForms may not work exactly the way users expect in a spreadsheet.

This example form walks you through the steps to make XForms work like a spreadsheet. We gather typical income and expense information for an organization for a year, with both row and column totals. We add features such as allowing for empty (“null”) values but still check for valid data in each cell.

The initial form is shown in figure 1:

Figure 1: Income and expenses for four quarters
It gives users several points of feedback:
  1. If they enter any invalid decimal character an alert appears next to the cell.
  2. Empty cells do not show alerts even though the data is not a valid decimal number.
  3. Rows show the total annual income and total annual expenses.
  4. Column show the net income for each quarter and the annual net income in the bottom right cell of the table.
Figure 2 shows a version of the form with the data filled in and an error in one of the input fields.
Figure 2: Alert shown on invalid decimal value
In Q4 Expenses the user typed the letter “L” instead of the number “1”. There are several features of the form that we have added that to get this functionality.

Allowing empty initial values

One of the problems is that empty fields are not valid decimal numbers. To get around this we have two options. The first option is to create a custom data type called for example decimalOrNull that allows empty values in each of the cells. We do this by using an custom XML Schema data type and put it in our XForms model.
<xs:simpleType name="decimalOrNull">
    <xs:restriction base="listOfDecimals">
        <xs:maxLength value="1"/>
    </xs:restriction>
</xs:simpleType>
<xs:simpleType name="listOfDecimals">
    <xs:list itemType="xs:decimal"/>
</xs:simpleType>
Our other option is to use the XForms implementation of the decimal data type which also allows empty values. Many people, like me until recently, are not aware that XForms has its own version of standard data numeric data types which allow empty values. This is useful in cases like like ours, in particular so that forms does not show errors when the form is first rendered to the user.

We use the XForms <xf:bind> element to create binding rules that applies to all the input fields. Here is what the instance that holds the saved data looks like:
<xf:instance xmlns="" id="save-data">
    <data>
        <income>
            <q1/>
            <q2/>
            <q3/>
            <q4/>
        </income>
        <expenses>
            <q1/>
            <q2/>
            <q3/>
            <q4/>
        </expenses>
    </data>
</xf:instance>
And here is our binding rule for our custom data type:
<xf:bind id="decimal-or-null"
         ref="instance('save-data')/*/*" type="decimalOrNull"/>
or alternatively, with the built-in XForms data type for decimal values:
<xf:bind ref="instance('save-data')/*/*" type="xf:decimal"/>
Note that the prefix is xf not xs for this data type!

Because we use wildcards in our path, these binds apply to all quarters in both the income and expenses in our save-data instance.

Adding calculations

Now we take a look at some options for showing calculations. One way to do this is to put <xf:output value=""/> elements in the table cells. But this means that each output needs to have its own formula, and we don’t take advantage of the regularity of our data.

As an alternative design we can do the calculations directly in bind rules. First we separate the data that is to be saved from the calculations. We put all our calculations in a separate instance:
<xf:instance xmlns="" id="calculations">
    <data>
        <net-income>
          <q1/>
          <q2/>
          <q3/>
          <q4/>
        </net-income>
        <income-total/>
        <expenses-total/>
        <net-annual/>
    </data>
</xf:instance>
The remaining binding rules are for creating the totals in the right column and the differences in the last row. We use a predicate to only include the non-empty values in our total calculations. For example here is the total of income:
<xf:bind ref="instance('calculations')/income-total"
         calculate="sum(
            (instance('save-data')/income/*)
            [string() castable as xs:decimal], 0)"/>
Note that [string() castable as xs:decimal] removes all empty and invalid values from the total.

The quarterly net income calculations can be done using four distinct formulas. But if we use the element name consistently in the income, expense and calculation areas we can write just a single bind rule to make all four calculations.
<xf:bind ref="instance('calculations')/net-income/*"
    calculate="
    for $quarter in name()
    return
       instance('save-data')/income/*
         [name() = $quarter and string() castable as xs:decimal] -
       instance('save-data')/expenses/*
         [name() = $quarter and string() castable as xs:decimal]
    "/>
In this bind rule the calculation for each quarter is done by selecting all the net-income calculation sub-elements (q1, q2, q3 and q4). The rule then subtracts the expense for that quarter from the income to get the net-income for that quarter. It does this by adding the predicate to return only the current quarter from all the elements returned by the wildcard expression. As long as the three data structures have the same quarters a single bind rule does all the work of four distinct formulas!

What is beautiful about this example is that you don’t have to write a large amount of JavaScript code to detect when one of the cells change. All the dependency calculations are created from the binding rules for you. This is a good example of how the built-in XForms dependency graph keeps your XForms code short and easy to maintain.

Note that this example uses the custom Orbeon currency control. We decided that since the users know that all the data is currency, we did not need to show the `$` prefix. You can get a similar behavior by using the xf:input control with both the format and unformat attributes.
<xf:input ref="myNumber"
  xxf:format="format-number(.[string() castable as xs:decimal], '$#,###')"
  xxf:unformat="replace(replace(., '\$', ''), ',', '')"/>
What you can see is that with a little work, we can make forms work much like a spreadsheet. But unlike a typical spreadsheet, we can also use XForms binding rules to get field-by-field data checks and warnings right when you tab out of each cell. This instant feedback makes is easier for users to get their data right before they save their data.

A full listing of the form is available here.

Formulas for Summing Done Right

References

W3C Specification on XForms Custom Data Types

No comments:

Post a Comment