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|
- If they enter any invalid decimal character an alert appears next to the cell.
- Empty cells do not show alerts even though the data is not a valid decimal number.
- Rows show the total annual income and total annual expenses.
- Column show the net income for each quarter and the annual net income in the bottom right cell of the table.
|Figure 2: Alert shown on invalid decimal value|
Allowing empty initial valuesOne 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
decimalOrNullthat 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.
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.
<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>
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:
And here is our binding rule for our custom data type:
<xf:instance xmlns="" id="save-data"> <data> <income> <q1/> <q2/> <q3/> <q4/> </income> <expenses> <q1/> <q2/> <q3/> <q4/> </expenses> </data> </xf:instance>
or alternatively, with the built-in XForms data type for decimal values:
<xf:bind id="decimal-or-null" ref="instance('save-data')/*/*" type="decimalOrNull"/>
Note that the prefix is
<xf:bind ref="instance('save-data')/*/*" type="xf:decimal"/>
xsfor 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 calculationsNow 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:
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:instance xmlns="" id="calculations"> <data> <net-income> <q1/> <q2/> <q3/> <q4/> </net-income> <income-total/> <expenses-total/> <net-annual/> </data> </xf:instance>
<xf:bind ref="instance('calculations')/income-total" calculate="sum( (instance('save-data')/income/*) [string() castable as xs:decimal], 0)"/>
[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.
In this bind rule the calculation for each quarter is done by selecting all the net-income calculation sub-elements (
<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] "/>
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!
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:inputcontrol with both the format and unformat attributes.
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.
<xf:input ref="myNumber" xxf:format="format-number(.[string() castable as xs:decimal], '$#,###')" xxf:unformat="replace(replace(., '\$', ''), ',', '')"/>
A full listing of the form is available here.