B.4 Cell of Any Table
There are several ways to reference a cell in a table. To simply reference a cell in the current row of the current table, see Appendix B.1, Cell on the Current Row.
Referencing by Row Index
To reference an arbitrary cell in any table, you can specify the row location with a numeric index:
worksheet['Table Name']['columnheader'][row_ix]
If the table has no spaces in its name, you can use the simpler syntax below:
Tablename['columnheader'][row_ix]
For example, the reference
Customers['CompanyName'][5]
specifies the value in the 5th row (using 0-based indexing) of the 'CompanyName' column in the 'Customers' table.
Referencing by Value Lookup
Since most Data Tables are dynamic, referencing a cell by row index (see above) should only be used when a table contains a single row, or when rows are ranked. It is generally more useful to reference a cell by lookup using the table's primary key, as shown here:
Tablename["columnheader? primary_key == key_value"]
The “?” should be read as “where,” so the above expression can be read as follows: “Look in the table named 'Tablename', and return the value in the column named 'columnheader' where the primary key is equal to 'key_value'.”
For example, if a table named 'Customers' has 'customer_id' as its primary key, you can reference the cell in the 'CompanyName' column having key 'customer_id' by using the following expression:
Customers["CompanyName? customer_id == 123"]
Instead of using a fixed key value such as “123” for comparison, you can also use the field value of the current row in the current table. (See Appendix B.1, Cell on the Current Row, for the basic field syntax.) For example, consider a table named 'Customers', which contains columns named 'CompanyName' and 'customer_id' (the primary key). You can access values in the 'CompanyName' column as follows:
Customers["CompanyName? customer_id == field.local_id"]
This formula says: “Look in the 'Customers' table, and return the value in the column 'CompanyName' where the 'customer_id' key is equal to the value of the current cell in the 'local_id' column.”
This example illustrates how you can reference the cells of another table from within a formula column. You will use the tables in the 'TABLE' > 'SA' folder of the 'Orders' data source to create the table below, which has columns for product ID, product name, category ID, and category name.

1. Drag the following fields from the 'TABLE' > 'SA' > 'products' table to an empty cell on the Worksheet. (Option-click the field names to select them all, and then drag them together.)
product_id
product_name
category_id

This creates a new table named 'products1'.
2. Rename the 'category_id' column as 'cat_id'. This will help to distinguish it from the table you will add next.
3. Drag the 'TABLE' > 'SA' > 'categories' table to the Worksheet. This creates a new table named 'categories1'.

You now want to add a column to the 'products1' table that will contain the category name corresponding to each entry in the 'cat_id' column. The best way to do this is by joining the tables (see the Joining Tables section), but to practice using cell references, you will create this new column as a formula column.
4. Click the 'fx' button in the 'products1' table title row. This opens the 'Expression' dialog box. (See Creating an Expression/Formula Column for more information.)
5. Enter 'cat_name' as the name of the new expression column, and click 'OK'. This opens the Formula Editor.
6. In the Formula Editor, enter the following formula:
CATEGORIES1["CATEGORY_NAME? CATEGORY_ID==field['CAT_ID']"]
This formula says: “Look in the 'categories1' table, and return the value in the column 'category_name' where the 'category_id' key is equal to the value of the current cell in the 'cat_id' column.”
7. Uncheck the 'SQL' option at the top-right of the Editor.

8. Click 'OK' to close the Formula Editor. The two tables are now connected by a graphical link to indicate that the 'products1' table references the 'categories1' table.

9. For each table, press the 'Change View' button in the table title bar, and select 'Live Preview' from the menu. Expand the table borders as needed to view the results.
As desired, the formula column in the 'products1' table now displays the category name corresponding to each entry in the 'cat_id' column.

| << B.3 Column of Any Table | © 1996-2013 InetSoft Technology Corporation (v11.5) | Appendix C: Built-in Ranges and Groupings >> |