Active Columns

Active Columns are columns in tables that show the results of a script function. Active Columns are used to show automatically computed values in each cell of the column, similar to how a spreadsheet is used to automatically show computed values for a formula in a cell. This makes it easy to create columns in tables where the values are, for example, computed from other columns in the table.

 

Active columns might be used in a simple way, for example, to show values given in English height measurements within a table as Metric height measurements (using a simple multiplication by a conversion factor), or they could involve considerably more sophisticated computations using table look-ups or even specialized programming objects. Use Table - Add - Active Column to add an active column to a table. Creating an active column creates a new, special script component that is bound to the table.

 

Each active column is assigned a script function within the new script component. The script component can contain other functions and subprocedures that are not displayed in any active column. For example, common subprocedures used by more than one active column could be written into the script component. Global variables and other elements of scripts can also be written into the script component for use by any active column in that table. The type and size (for types like fixed-length ANSI strings that require a size) of an active column are set by the script/active column author.

 

Use of active columns requires the ability to write functions in an ActiveX scripting or .NET language. For example, simple functions are very easy to write in Visual Basic Scripting and may be created by non-programmers. For more advanced use, any ActiveX scripting language or any .NET language supported on the system may be used. For example, the script that powers an active column may be written in Jscript, the Microsoft implementation of Javascript.

 

To create an Active Column:

 

1. Open the table.

2. Right click on a column head and choose Add - Active Column or choose Table - Add - Active Column from the main menu.

3. Specify the function name, the name to appear on the column head and the data type returned by the function.

4. Make sure the Compute option is not set to on the fly. Press OK.

5. A script component will be created under the table and opened for editing. Write the function to be used by the Active Column. Close the script component.

6. Once the script and active column are verified to work correctly, if desired the computation mode may be changed to on the fly. Use of on the fly mode will prevent most script editing operations in the script window.

 

Active Columns can contain programs written in any ActiveX scripting language or any .NET language available in your system.

 

Add Active Column Dialog

 

Function

Name of the function called by this Active Column.

Language

Choose an ActiveX or .NET language in which the Active Column script will be written.

Add references for standard .NET modules

Adds references to standard .NET modules for active columns written in .NET languages.

Name

Name of the Active Column in the table.

Type

Data type returned by the function and hence the type of the column in the table. If the called function returns a type other than this, Manifold will try to convert the data into this type.

Size

Specifies size for data types (such as fixed length text types) requiring a size.

Compute

Computation method to be used for this Active Column:

 

on demand - Compute the column when the table is first opened and cache the values. Thereafter, recompute values on a user Recompute command.

 

on demand, recompute after changes to script - Same as on demand, but also recompute values if the script is edited.

 

on the fly (no cache) - Always recompute cells dynamically whenever necessary without ever caching values. The slowest mode, but always up-to-date. Use of this mode will not allow most editing of scripts. To edit a script associated with an active column, first change the Compute mode to some other mode, edit the script as desired, verify it runs and then change the Compute mode back to on the fly.

 

on user request - Recompute cell values only when the user issues a Recompute command. The fastest mode and the default.

 

Once formula results are computed for the table, the Active Column may be used like any other column. It may be used for thematic formatting or in an SQL expression, for example. The table may be sorted by clicking on the Active Column head.

 

Computation Controls

 

Manifold provides a Compute control to specify how the Active Column should be computed. The Compute parameter is specified when the Active Column is created. It may be changed at any time by right clicking on the Active Column head and choosing Edit to launch the Active Column dialog again. Different Compute modes are provided to allow user control over when the script in the Active Column is executed to update the values in the column.

 

Although Active Columns are conceptually similar to spreadsheet cells in that the values are intended to be automatically computed, the formulas used within Active Columns can often be very large and complex scripts (large programs consisting of many subroutines). In the case of drawings containing many objects, and thus many records in the drawing's table, it could take a long time to recompute the Active Column for all records. Accordingly, unlike spreadsheets that are set by default to automatically recompute every cell when anything in the spreadsheet is changed, the default behavior of an Active Column is to recompute the values in the column only when the Recompute command is issued by the user, the on user request setting.

 

When an Active Column is recomputed using the on user request or the on demand settings the values in the Active Column will be cached, that is, held unmodified in the column, even if values upon which they depend from other columns have been changed. This can be changed by using the on the fly setting, which results in behavior similar to that of spreadsheets where cells are always automatically recomputed.

 

If caching is turned off for on the fly recomputation, any such action using the Active Column will cause a dynamic recomputation of the values in the column. For simple formulas the difference between on the fly and other modes is very slight; however, for more complex functions or major programs written into an Active Column the difference is substantial. In such cases the on user request mode should be used.

 

The Recompute command is highly optimized to avoid unnecessary context switches when refreshing values in cacheable Active Columns. This makes the Recompute command the fastest way to compute values in Active Columns. Invoking the Recompute command on an Active Column whose function can not be found will display an error message when the script cannot be executed.

 

Tech Tip

 

Why does on the fly mode not allow editing of the active column's script? On the fly mode causes the active column, and thus the script, to respond to any changes. Suppose we attempt to edit a script's text or even to enter the initial text: when we make an edit, the change in text (even just one character) will be detected by the system as a change. In response the table window will attempt to compute the values for any active column cells that are visible; however, it is quite likely that the script as changed is not yet functional. The script engine therefore signals an error and the script window selects the text it thinks is incorrect (which might be just a single character the user has just typed).

 

Avoid this restriction on editing by using one of the following techniques:

 

§      Create an active column with another computation mode, make sure it works and then switch computation mode to "on the fly."

§      Avoid execution of active column code by making the active column invisible, that is, by hiding it in the table window or by closing the table window itself for the editing period.

 

Example

 

We will open the Order Details table from the Nwind.mdb sample database and add an active column to it.

 

images\eg_active_columns_01.gif

 

We've opened the table and have hidden all fields except Order ID, Unit Price and Discount.

 

images\eg_active_columns_02.gif

 

To create an Active Column we choose Table - Add - Active Column from the main menu.

 

images\eg_active_columns_03.gif

 

In the Add Active Column dialog we enter Discounted for the name of the function to use, Price with Discount as the name of the column and we choose Floating-point (double) for the type of our column. For this example we will choose on user request for the computation method. Press OK.

 

images\eg_active_columns_04.gif

 

A script component called Order Details Script is created in the project pane underneath the Order Details table. If this is the first Active Column, the script component is popped open for editing in a script window using default template code.

 

images\eg_active_columns_05a.gif

 

The template code is not a functioning Active Column script - it simply reminds us what the structure of a Active Column script should be.

 

images\eg_active_columns_05.gif

 

We edit the script component to add the above text. Note how script windows in Manifold will color the different parts of a script to help us keep track in our coding. Advice to newbies: pay careful attention to details and check for keyboarding errors. "Unit Price" is not the same thing as "UnitPrice".

 

images\eg_active_columns_06.gif

 

When we close the script window an Active Column appears. Right click on the Price with Discount column head and choose Recompute. The Active Column uses a different background color to indicate it contains computed values. Foreground and background colors for Active Columns may be set in the Tools - Options dialog. The values it shows are those computed using the function we wrote.

 

images\eg_active_columns_07.gif

 

We can widen the column to see the full name we specified. We can right click on the column and choose Format to format the columns to only two digits after the decimal point. To change the column so that it re-computes automatically, we right click on the Price with Discount column head, choose Edit and change the Compute value to on the fly.

 

Syntax and Conventions for Active Columns

 

When used within an Active Column script, 'Record' refers to the context record that has the following properties:

 

ID

The ID of the drawing object. Only for drawing tables.

SequentialNumber

Sequential number of the record within the table.

Data

A collection of values within a given record.

DataText

A collection of values with a given record represented as formatted strings.

 

Active Column scripts can reference other components and can even reach any other scriptable objects installed within the system such as Microsoft Office applications.

 

Context Menu Commands for Active Columns

 

Right click on an Active Column head to call up a context menu of commands. In addition to the usual table column commands, Active Columns have:

 

Edit

Edit the definition of the Active Column using the Edit Active Column dialog (functionally equivalent to the Add Active Column dialog).

Edit Script

Enabled for Active Columns. Edit the script associated with this column.

Flatten

Enabled for Active Columns, Rank Columns and Relations. Convert the column to a regular column by using the current values in the cells.

Recompute

Recompute the values in an Active Column.

 

Debugging

 

If you have purchased the Debugger option for Professional Edition or if you have licensed Enterprise Edition (which includes the Debugger) you can use the Debugger to debug Active Column scripts written in ActiveX languages that support Microsoft specifications for debugger interfaces. Scripts associated with Active Columns can run in the debugger. To debug an Active Column script, place one or more breakpoints within the script, right click on an Active Column cell and choose Run under Debugger from the pop up menu. Doing this runs the function while generating values for the Active Column in the context of the clicked cell. The resulting value will be discarded. All regular runs of Active Column functions are done without the debugger.

 

Converting to Regular Columns

 

Right click on an Active Column head and choose Flatten to convert the column to a regular data column. The current value of each cell will be used to populate the regular column.

 

Notes

 

Active columns can only reliably refer to non-active columns. Using one active column from another will not force re-computation and will either return the last cached value or no value at all. A "no value" return is the value with a 'no value' flag that is supported by all ActiveX scripting hosts.

 

Choosing an unexpected type for the column in the Add Active Column dialog will force a conversion to that type if possible and will return no values if a conversion is not possible.

 

We can manipulate drawings from Active columns. The following Active column operates on a drawing's table and returns the number of points in the first branch of the object.

 

Function PointsInFirstBranch

Set objSet = Table.Owner.ObjectSet

Set obj = objSet(objSet.ItemByID(Record.Data("ID")))

PointsInFirstBranch = obj.Geom.BranchSet(0).PointSet.Count

End Function

 

IronPython scripts intended to be used for active columns should only include the body for a single script function.

 

See Also

 

See the Scripts topic for information on scripting. See Active Columns using VBScript and Active Columns using Jscript for examples using active columns and script functions written in VBScript and Jscript.