Objectsheet Tutorial, Page 3: Scatch Sections and Advanced Table Features

The Objectsheet A casual, object-oriented data analysis tool

Function Reference

Objectsheet Tutorial Page 3: Scatch Sections and Advanced Table Features

In tutorial 1, we covered basic navigation, cell references, sorting, and template formulas. Tutorial 2 covered formats, styles, and display types. Here, we cover advanced functions like cross-table referencing, scalars, and introspection. In tutorial 4, we will cover HTML sections.

Page Contents:

10. Scratch Section Introduction
11. Scratch Sections and Introspection
12. Cross Table References
13. Named Table Rows
14. Calculations down Columns

10. Scratch Section Introduction

All the data and attributes within the Objectsheet, including the cells within tables, are Javascript data. Tables and other Objectsheet sections can thus be referenced, inspected, and manipulated from any other Objectsheet section. This allows you to monitor, analyze, and change values across Sections via formula.

Before we show interactions between sections, we'll introduce the Scratch section. Scratch sections are another construct within the Objectsheet application. They allow line-by-line, free-form calculations. They provide a scratchpad-like way to do simple calculations and inspect and change data in tables.

As with the display of values within table cell in the prior tutorials, the Scratch section adds the result after the expression you type (separated by ":="). To change the size of the Scratch section "window", drag one of the gray bars at the bottom or right-hand edges of the Section. The lower right corner can be dragged in any direction.

Things to try:

Add your own calculations anywhere within a scratch section.

Refer to the result of the line above using "_" and of two lines above using "__".

11. Scratch Sections and Introspection

As we mentioned above, all of the values and formulas within Tables (both in the instance section and in the Templates) are Javascript data and can therefore be referenced in formulas anywhere in the Objectsheet application. We'll demonstrate this below using the table from part 6 on the previous page.

All sections are live. When you make changes to a value, all references to that value are updated as well.*

The instance area in an Objectsheet table is a Javascript array called "us"; each element of that array is a Javascript object that represents the data in a particular row of the table. The data in the cells of an Objectsheet table can be referenced in the following way:

The table itself is represented by its name, table6 in this case.
The rows of the instance section are represented by the us[] array. Specific cells are then referenced by column name, e.g., us[1].cost.
Cells values can also be accessed by the column function. This function is automatically created for each column in a table. In the example below, area(2) refers to the value in row 2 (the third row down) of the area column. With two arguments (area(1,3)) , the values between those indices are returned. Use negative arguments to start counting the index from the end of the array instead of the beginning. Finally, without any argument, (area()) all values in that column are returned.

In Scratch sections results, object and array values are automatically "unfolded"-- all enclosed elements are shown, and in the case of nested arrays and objects, further unraveled.

Things to try:

Modify a value in table6 (for instance row 2, column a; or anywhere in row zero) and watch the values in the scratch section update instantly.
Notice how the format formula e.g. dol() also works in the scratch section.
In the second line, change table6.area(2) into table6.area(1,3), press control-enter to calculate, and see how that portion of the column values is returned.

Notes:

* There is an order to calculation. In the table/scratch section combination above, the table section is calculated first, then the scratch section. In the Objectsheet application, you can control the order that sections are calculated, but here, the order is fixed.
In addition to cell values in the body of the table, you can also refer to expressions in the Template:
- In the property template, you select a column by name using "prop" (e.g., table6.prop.area) or by index using the "_iprop" array (e.g., table6._iprop[1]), then refer to the specific attribute ("action", "prep", "format", "style", "display", "formula", or "name"). For example, the format formula of the area column can be referenced by name using table6.prop.area.format or by index table6._iprop[1].format.
- To refer to the expressions in the section template, simply use the name of the row. For instance, the display property of table6 can be accessed as table6.display.

12. Cross-table references

You can refer to data between tables to do several useful things:

Copy, summarize, or filter values of a column to another table
Synchronize the number of rows or columns between tables (using setObjectCount() or setPropCount())
Synchronize property names between tables (using setPropNames())
Synchronize the entire template (using mirror())

The table below shows four different ways to refer to data between tables. Remember, within a table, the us[] array refers to the collection of rows that contain the cell values.

Column "a": Refer to the object that represents the row (t1.us[]) then select the appropriate property (.a).
Column "area": Refer to the object in that row (t1.us[]) then select the current column's name ([pname]). For any cell, pname holds the name of the current column.
Column "cost": Refer to a column function (t1.a()) then select the appropriate row number.
Column "d": Use the lookup() function. The lookup function returns the first row that the supplied expression matches. In the case below, we are looking for a row where its value in column "a" matches the value of column "a" in table t2. In the formula 'a=='+a, the first "a" refers to the a in table t1 and the second "a" refers to the value in table t2.
Column "e": Use a replica of the other table's formula using the Javascript eval() function. The format expression in column "e" uses the same approach to copy column "a"'s format expression.

These are just ways of referencing data; once you have a reference, you can modify the value as needed.

Things to try:

Change the number of rows in table t1 by dragging or clicking the gray bar at the bottom. Watch the rows of table t2 stay in sync. The misc formula setObjectCount() does this.
Try and change the number of rows in table t2 by similar fashion. The setObjectCount() formula overrides the manual attempt, therefore no change takes place.

13. Named table rows

In all of the tables we've shown so far, the rows are accessed as an array (called us[]). To simplify references between Sections, we can also name rows. Simply prefixing a column name with an asterisk (*) will cause the values of that column to also represent the name of an object.

In the "states" table below, rows are named by the "abbrev" column. You can refer to row 0 either as state.us[0] (the usual method), or by its name, as state.CA. The expression state.CA.name returns "California". In the products table, the tax formula extracts the tax rate using the state's abbreviation, states[ship_state].tax ( = states.CA.tax in row 0), instead of a more complicated lookup function.

Things to try:

In the products section, change one of the "ship state"s via the drop-down box. The tax calculation is automatically updated.
Change the tax of one of the states within the "state" table, watch the change propagate.
Add another state in the "states" table by clicking the grey horizontal bar at the bottom of that table or by dragging it down a little. Give the state and abbreviation and tax rate. The new value immediately becomes available in the "products" table.

14. Naming Cells and Calculations Down Columns

In the example above, we set the table so that rows could be named. You can also name individual cells in a table. This is handy for convenience calculations or to set a group of variables.

Simply by suffixing a column name with an equals sign, the values in the column become names for the cells to their right. In the example below, table13.inches = 1000 (row zero) and table13.m = 25.4 (row 6). Changing a value in column "a" renames that variable.

In this example, column "a" gets its values from column "b" because of the single "=" suffix. You can have a column refer to any other column to the left by using the number of "="s as a suffix to point to the target column. You can have variables point to values to the left by prefixing the column name with the appropriate number of "="s.

In the example below, we set column "a" to have its name define the value of the row immediately to its right. In the scratch section below, we've asked for the entire data structure of table13. If you scroll down, you can see the property attributes (in prop:{...}), then the cell values (in us:[...]), then at the bottom, all of the variables set by the names in column "a".

Things to try:

Change the value of the inches variable in row 1 and watch the new number propagate through the rows.

Objectsheet provides a powerful way to refer to values between sections. Here, we've only scratched the surface.

The last tutorial (#4) covers HTML sections.