Introduction to SubTables

A SubTable provides an edit table view for the users of your model, but which displays only a subset of a larger edit table, and may display the rows or columns in a different order from the original. In this mini-tutorial, you'll create a SubTable view.

Preliminaries

Begin by downloading the following model as a starting point: media:California Power Plants2.ANA

After loading the model, you'll should see the following diagram:

Click on the Edit Table button for "Power Plant Data". This model contains data on 34 power plants in California of various types, a portion of which is shown here:

A user can edit this large table, but you may have certain users that need to worry only about Natural Gas plants. For these users, we can provide a SubTable containing only the rows corresponding to Natural Gas plants. We can also sort the table for them, and eliminate the Plant Type column, since it will always beset to Natural Gas.

Defining the SubTable

Steps: 1. Press to enter edit mode. 2. Create a decision node titled Sort By, defined as

Choice(Plant_fields, 1, false)

3. Create an index named Gas Plants, defined as

Index J := Subset(Power_plant_data[Plant_fields='Plant Type']='Natural Gas');

SortIndex(Power_plant_data[Plant_fields=Sort_by, Plant_id = J])

This expression extracts the subset of Plant_IDs in which Plant Type is "Natural Gas". It then sorts by whichever column we've selected in Sort_by.

4. Create an index, Gas_fields, defined as a list of labels:

Plant Name
County
Cogen
MegaWatts

These are the columns we want to include in the SubTable, in the order we wish for them to appear. Notice that we have excluded Plant Type.

5. Finally, create a variable node titled Gas Plant Data, defined as

SubTable( Power_plant_data[ Plant_id=Gas_plants, Plant_fields=Gas_fields ] )

Your screen should now look like (just before you press Enter to accept the definition):

6. Press the green check button to accept the definition. Notice that the definition panel changes to a SubTable button.

7. Return to browse mode by pressing . (This is so the Choice cells will appear as drop downs when we view the edit table.

8. Press the SubTable button to bring up the SubTable edit view. You should see:

Trying it out

9. In the edit table for Gas Plant Data, change the MegaWatts value for 'UCSD' to 2.83K. Press the green check to accept the change.

10. Examine the original (large) edit table for Power Plant Data. Find the 'UCSD' entry, which should be the top row. Notice that the value in the master table has changed.

11. Try changing other values in each table, pressing the green check in the table where the change is made each time, and watch how the corresponding value changes in the master- or sub-table.

12. Temporarily enter edit mode so that you can change Sort By. Select MetaWatts, for example, and note how the rows of the SubTable change their order. (You may want to switch back to browse mode).

13. In the Subtable, while Sort_By='MegaWatts', change values in the MegaWatts column so that the order changing. Press the green check and again see how the table adjusts.

Understanding the SubTable definition

In Step 5, you entered the following definition for Gas Plant Data:

SubTable(Power_plant_data[ Plant_id = Gas_plants, Plant_fields = Gas_fields])

Notice that without the SubTable function call, the definition is just a Subscript operator:

Power_plant_data[ Plant_id=Gas_plants, Plant_fields=Gas_fields ]

As an Analytica modeler, Subscript operators should be familiar to you. This example re-indexes both dimensions by the indexes that were defined in previous steps. The result of this subscript operator is exactly the subset of the table that we were interested in. The SubTable will present an edit table view when it appears at the top-level of a definition, and when its parameter contains a Subscript or Slice operation (or combination of Subscript/Slice operations).

Input Nodes

Only variables containing input nodes can be edited from browse mode. In the case of a SubTable, it can be edited from browse mode if it contains an input node, or if its parent can be edited in browse mode.

If a parent does not contain an input node, but the SubTable does, then only the subtable data can be edited. This means that your end-user would only be able to edit portions of the master table.

Try it:

1. Change to edit mode by pressing .

2. Selete the input node for Power Plant Data.

3. Select the Gas Plant Data node.

4. Select Object → Make Input Node from the menu. Reposition the input node as desired.

5. Switch back to browse mode by pressing

6. Try making changes (while remaining in browse mode) to Power Plant Data. Notice that you cannot without entering edit mode (users with a browse-only version, or using Player, do not have the option of changing to edit mode).

7. Try making changes (while still in browse mode) to Gas Plant Data. Notice this is possible, and notice that the master table changes accordingly.

When SubTable reduces all the way to a single cell, the cell value appears directly in an input node. Again, try it:

8. Switch to edit mode .

9. Create a variable titled Moss Landing MegaWatts, defined as:

SubTable( Power_Plant_Data[Plant_ID='G0372',Plant_fields='MegaWatts'] )

10. Select Object → Make Input Node and position accordingly, so you have an input node for the variable just created. Notice that the input node contains the single value:

11. Change the value, then view the tables for Power Plant Data and Gas Plant Data. Observe that the value for that cell is the same in all three places.

Summary

You can view a SubTable as a view into a larger database table -- the master table, in which you can focus your users on the subset of data that they need to worry about, while still maintaining the entire table together. SubTable is about as flexible as Subscript and Slice, which are themselves extremely powerful and flexible functions, making a large number of variations possible.

Exercises

1. Create a subtable view based on an index named Plant_Name, where the labels are the actual plant name (such as 'Moss Landing' rather than the ID ('G0372'). The plant name should not appear as a column in the table and thus cannot be changed.

2. Depending on how you implemented #1, you may have a problem if two plants have identical names. Can you implement #1 in a way that works even if plant names are duplicated? (Hint: Use a Slice operation rather than Subscript).