Skip to content

Instantly share code, notes, and snippets.

@dmccreary

dmccreary/xforms-income-expense-totals

Last active August 23, 2017 19:23
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dmccreary/6393540 to your computer and use it in GitHub Desktop.
Save dmccreary/6393540 to your computer and use it in GitHub Desktop.
Download ZIP
Example of using Orbeon forms to create a spreadsheet like behavior to correctly calculate sums and differences even when nulls are present.
Raw
xforms-income-expense-totals
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:ev="http://www.w3.org/2001/xml-events" xmlns:fr="http://orbeon.org/oxf/xml/form-runner" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xf="http://www.w3.org/2002/xforms" xmlns:xxforms="http://orbeon.org/oxf/xml/xforms">
<head>
<title>Sample Annual Income and Expenses Report in Table</title>
<xf:model>
<xs:schema>
<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>
</xs:schema>
<!-- the actual data that we save -->
<xf:instance xmlns="" id="save-data">
<data>
<income>
<q1></q1>
<q2></q2>
<q3></q3>
<q4></q4>
</income>
<expenses>
<q1></q1>
<q2></q2>
<q3></q3>
<q4></q4>
</expenses>
</data>
</xf:instance>
<!-- calculated values are used to verify data entry but do not need to be saved. -->
<xf:instance xmlns="" id="calculations">
<data>
<net-income>
<q1/>
<q2/>
<q3/>
<q4/>
</net-income>
<income-total/>
<expenses-total/>
<net-annual/>
</data>
</xf:instance>
<!-- all the values that we input and save -->
<xf:bind id="decimal-or-null" ref="instance('save-data')/*/*" type="decimalOrNull"/>
<!-- or you can use this
<xf:bind id="decimal-or-null" ref="instance('save-data')/*/*" type="xf:decimal"/> -->
<!-- row totals -->
<xf:bind ref="instance('calculations')/income-total" calculate="sum( (instance('save-data')/income/*)[string() castable as xs:decimal], 0)"/>
<xf:bind ref="instance('calculations')/expenses-total" calculate="sum( (instance('save-data')/expenses/*)[string() castable as xs:decimal], 0)"/>
<!-- quarterly column totals - this uses the name of the element to get the right value -->
<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] "/>
<xf:bind ref="instance('calculations')/net-annual" calculate=" instance('calculations')/income-total[string() castable as xs:decimal] - instance('calculations')/expenses-total[string() castable as xs:decimal] "/>
</xf:model>
<style type="text/css">
/* the table headers are gray with white centered text */
table thead tr th {
background-color:gray;
text-align:center !important;
color:white;
font-weight:bold !important;
}
table tbody tr th {
background-color:gray !important;
text-align:center !important;
vertical-align:middle !important;
color:black;
font-weight:bold !important;
}
/* by default, all table cells are inputs and have this width of 15 exs */
.xforms-input-input{
width:15ex !important;
}
/* this right aligns the numbers */
.xbl-fr-number-visible-input {
text-align:right;
}
/* we add this class to all numeric output table data cells */
.number-cell{
text-align:right!important;
}
}</style>
</head>
<body class="orbeon">
<table class="table table-striped table-bordered">
<thead>
<tr>
<th/>
<th>Q1</th>
<th>Q2</th>
<th>Q3</th>
<th>Q4</th>
<th>Annual Totals</th>
</tr>
</thead>
<tbody>
<tr>
<th>Income</th>
<td class="number-cell">
<fr:currency ref="instance('save-data')/income/q1" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('save-data')/income/q2" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('save-data')/income/q3" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('save-data')/income/q4" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('calculations')/income-total" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
</tr>
<tr>
<th>Expenses</th>
<td class="number-cell">
<fr:currency ref="instance('save-data')/expenses/q1" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('save-data')/expenses/q2" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('save-data')/expenses/q3" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('save-data')/expenses/q4" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
<td class="number-cell">
<fr:currency ref="instance('calculations')/expenses-total" prefix="">
<xf:alert>Error</xf:alert>
</fr:currency>
</td>
</tr>
<tr>
<th>Net Income</th>
<td class="number-cell">
<fr:currency ref="instance('calculations')/net-income/q1" prefix=""/>
</td>
<td class="number-cell">
<fr:currency ref="instance('calculations')/net-income/q2" prefix=""/>
</td>
<td class="number-cell">
<fr:currency ref="instance('calculations')/net-income/q3" prefix=""/>
</td>
<td class="number-cell">
<fr:currency ref="instance('calculations')/net-income/q4" prefix=""/>
</td>
<td class="number-cell">
<fr:currency ref="instance('calculations')/net-annual" prefix=""/>
</td>
</tr>
</tbody>
</table>
<fr:xforms-inspector/>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment