Objectsheet Tutorial, Page 2: Table Presentation

In tutorial 1, we covered basic navigation, cell references, sorting, and template formulas. Here, we cover formats, styles, and display types.

Templates are a part of Objectsheet Tables. Within a Template, you place Javascript expressions that can affect specific columns or the entire table. One part of the Template, the formula row, was introduced in tutorial 1. On this page, we'll introduce the other types of rules.

There are two parts to the Template: the Property Template and the Section Template. The Property template has a cell for each column while the Section Template affects all columns. We'll cover the Property Template first. Show the Property Template by clicking on the "P" button on the upper right of a table. We'll describe how each row in the Property Template works in turn.

Note: You can get quick help on each Template row by holding your mouse over the template row name on the left. Also, you can get general help via the Quick Reference Page.

Page Contents:

5. Text Formatting
6. The prep row
7. The style row
8. The display and action rows
9. The Section Template

5. Text Formatting

Below is an example table with some values and formulas. As in the other tutorials, the Table below is live— you can modify it to learn how it works.

The values displayed here are kind of messy; they could use a little cleaning up. The format row helps with formatting text values. Show the Property Template ("P" button). You'll find the format row in the middle (see below).

Format row cells contain Javascript expressions that convert the internal value to the one actually shown. Use the variable self for the cell's raw (internal) value. For instance, expression fix(self,n) limits the number of decimal places to n (2 is default). Likewise, dol(self) fixes the number of decimal places (default is 2 places) and adds a (US) dollar sign and commas as appropriate.

Format expressions are flexible; you can append a suffix (see the "area" format expression below), convert units (e.g., fix(self/10.76,2)+' sq m'), use values from other cells or variables in the Objectsheet, or define your own format function in a Scratch Section and use that in a format expression.

When working with very large or small numbers, the engu() function displays values with the appropriate engineering unit suffix: "k" for kilo (x 1,000), "u" for micro (/1,000,000), etc. engu(), and its counterpart for data input, prepEngu(). This can make such calculations substantially easier, removing the need to keep track of such multipliers and the errors due to improper conversions.

Things to try:

• Cut a formatting formula to remove it, then press control-enter to calculate. This shows the original (unformatted) values. Then paste the formula back in, again pressing control-enter to recalculate.*
• You could specify any kind of manipulation in format rows. For instance, in the area column, you could display the results in square inches by changing the format formula to (self*144)+" sq in". Or you can show a result in binary using self.toString(2).
• Change or add a second argument to a dol(), engu(), or fix() function to change the number of decimal places.
• Try other formatting helpers, like:
  • pct(self): show value as percentage
  • comma(self): show value with thousands separated by commas
  • fixz(self,2). This function is like fix(), but retains trailing zeros.
  • sci(self): show value in scientific notation
  • eng(self): show value in engineering notation (exponent divisible by 3)
  • engu(self): show value using engineering units suffix ("k" for 1,000, M for 1,000,000, etc.)
  • There are more Format helper formulas in the Objectsheet Reference in the Calc:Format section.
  • toDate(self, pattern) shows a value in configurable date format. Pattern can be "MM/DD/YY", YYYY-MMM-DD, etc.
• There are some helpful keyboard shortcuts (currently Internet Explorer only): while in any cell, hitting Ctrl-n, where n is 0 through 9, fixes the number of decimal places and right-aligns the number. Ctrl-period removes the decimal place formatting.

* Undo is not fully implemented and does not work in these examples; use cut and paste instead.

Notes:

• A syntax or other error in a format formula is displayed in the cell itself.
• Most format functions are only a few lines, for example here's the source of the engu function.

6. The prep row

The prep row prepares values typed directly into cells. This comes in handy, for instance, when you expect a number or a percent to type in, as "$" and "%" are not understood by Javascript. The function prepDate()converts an entered text date (e.g., "3/18/2006") to the internal representation. You could also allow somebody to type in long lists of financial figures without using the decimal (the prep formula would be simply self/100).

An important use of the prep row is to validate data entered by a user. You can use the prep row to ignore or change values that don't meet criteria you set and even alert the user when that happens. This is one way to ensure that only allowable values are entered into cells.

The most frequently used prep formulas are prepNum(self) and prepDate(self).

Things to try:

• Enter a negative number for area. The prep formula for area turns negative numbers into zero.
• You can turn a column into percentages (both prep and format formulas) using the keyboard shortcut Ctrl-% (Ctrl-Shift-5). Click into a cell in column a and press Ctrl-%. Note the resulting prep formula. Click Ctrl-% again to remove that format.
• Override one of the values in the "sq in" column. Feel free to use engineering suffixes- the prepEnug() function correctly interprets them.
• Enter or change one of the values in the date column. The Objectsheet uses Javascript's flexible Date class to interpret the date format you type.
• You can alert whenever a negative area is entered. The prep formula for this is "self=prepNum(self);self<0?(alert("area can't be negative"), 0):self". While this is a bit lengthy, it's still basic Javascript.

Notes:

• Prep formulas do not affect values generate from a template formula, only those typed in.

7. The style row

The style row controls the style used to display a value. This includes boldface, italics, underlines, text font family, text size and color, and cell borders and background colors. Specify styling using Cascading Style Sheet (CSS) syntax, the standard language for styling on the web.

Styles can be simple values or can be expressions. You can enter CSS text directly; for instance, to bold all displayed values in a column, the style expression is font-weight:bold (see column "a" below). Style expressions are preceeded by an equals sign. They provide a powerful conditional formatting capability (see the style formula in the "area" column).

Like with the prep row, the style row can be used for data validation. The helper function constrain(expr), as shown in the area column below, visually flags numbers where expr evaluates to false. It does not change or remove the value as the prep function did above. In the area column below, the constrain() function flags any value larger than 100 sq ft.

Things to try:

• Change some of the area numbers to be greater than 100 and then back and watch the color change accordingly; also change numbers in the "cost" column and watch the borders change.
• Add rows to the table: the color of cells in column "d" automatically alternate with each successive row.
• Change the style expression in column a to display:none. when you click the "P" icon to hide the Property Template, that column disappears. When you click "P" again, you can see it again.

Notes:

• Ways to color stripe entire rows for readability are shown below in Tutorial part 9.

Again, Objectsheet styling cells is both simpler (via CSS) and more powerful (via conditional style expressions) than spreadsheets.

8. The display and action rows

The display row controls the HTML element used to show a value. Beyond the (default) single line text box, there is a variety of ways to show and enter data, including checkboxes, multiline textareas, and drop-down select boxes. A toggleButton that cycles through a set of values is also available. For multi-value display types (select, radio, and toggleButton), you set the possible values using a Javascript array. There are about a dozen display types currently defined for the Objectsheet; there is a simple API to extend them.

The select box in the "day" column below is defined on the fly using an action formula. The updateDisplay() formula forces redisplay of a given column or individual cell*. In the example below, it recalculates the display formula for the "day" column, only showing the values appropriate for the selected month. While this is not necessarily the most efficient way to enter dates, it shows dynamics possible with the Objectsheet. In fact, a Javascript-based calendar widget could be easily integrated for entering dates.

Things to try:

• Select a favorite color. The style formula in that column uses the selected value to color the cell.
• Select a month in the "month" column. months is a built-in Objectsheet array that holds the names of the months in English (months_long similarly holds the full English spelling of the months).
• The "day" column is defined by a select box as well, but it constrains the selection to the number of days in the selected month (leap years are not implemented). Select a month before selecting a day.
• The display element used in the "dir" column (toggleButton()) shows buttons that cycle through a set of values (in this case, "north", "east", "south", and "west"). Click on one of these buttons to cycle through the directions.
• We use another helper function, qw(), that takes the words in the supplied text and creates an array from them. The result of qw('north east south west') is ['north', 'east', 'south', 'west']; using qw() saves a little typing.
• Add some values to one of the columns, then remove the display formula by selecting then cutting it. All of the values are retained-- just the display mechanism changes.
• Set some values in a column, then remove that column's display formula, and tab or click out of the cell. The display type changes back to the default text box but any values remain.

Notes:

• If there is a syntax or other error in a style formula, the cell's background color becomes red.
• * All attributes of Objectsheet cells except display are automatically recalculated each time the section is calculated. For performance reasons, the display attribute must be recalculated manually using an updateDisplay() action formula.

9. The Section Template

As mentioned at the top of this page, the Template is in 2 parts. Above, we covered the Property Template, which controls individual columns. Here, with the Section Template, we'll use single expressions to that apply across all columns.

Within the Section template, the format, style, display, and formula rows are equivalent to their Property Template counterparts, except that they apply to the entire table at once. For format and display, the Property Template will override a Section template expression for that particular column. For style, any values in the Property Template are added to the style defined in the Section Template.

The remaining two rows are unique to the Section Template. The misc row formula is executed before the entire table is calculated. It is for preparatory calculations or to synchronize the table before it is calculated. Some expressions commonly used in the misc row are:

• us.length=n fixes the number of rows in the table at n. You can force the number of rows of a table to track that of another table with us.length=othertable.us.length.
• setPropCount(n) fixes the number of columns in the table at n
• setPropNames(array) fixes the number and names of the properties to the values of array. You can force a table's property names to follow those of another with the expression setPropNames(otherTable.props())
• mirror(otherTable) synchronizes the entire template of multiple tables. To break this synchronization, use unmirror() in the misc row. Note: just erasing mirror() does not break the synchronization.
• cellWidth=n sets the width of all columns to n pixels.

The filter row allows a formula to dictate which rows are shown. You can test the value in any column (or combination of them), or base it on row number (to, for instance, only show the n^th row. Filtering affects just which rows are shown; values in all rows are always calculated. When filtering, the row number changes to a\b, where a is the original (unfiltered) row number and b is the row number currently visible.

Things to try:

• Open up the Property template and add color:green; to one of the columns' style cell. The text will show both the green text color and the background color from the section template.
• In the filter row, enter the formula a>0.5. When you hit control-enter or move out of the cell, only rows 2 and 3 will be displayed since, in column a, only 0.67 and 1 are greater than 0.5. On the left, you'll see both the original row numbers to the left of the backslash, then the current row numbers.
• You can also filter by row. To show every other row, try row%2 as the filter
• You can add objects while filtering. With the row%2 filter, drag the grey bar at the bottom of the table.
• Add the following formula to the misc row: setPropNames(qw("ee mm uu rr nn")). This will rename the columns and add the additional column for "nn".
• In the style formula, replace row%2 with (row+col)%2 (remember the parentheses) for a funky look.

• If there is a syntax or other error in the global style formula, the row's background color becomes red.

Next, in tutorial 3, we cover advanced functions like cross-table referencing, scalars, and introspection and in tutorial 4, we'll cover HTML sections. Tutorial 3 shows how to make tables reference each other and looks at the action row of the Property Template.