Softimage SDK Programmer's Guide > Customizing Softimage using the SDK > Spreadsheet Queries >

# Scripting for the Spreadsheet Queries

## Single-level Query

### Example of Showing Local SRT Values for Selected Objects

#### Example: Line script

The first line of this script returns the list of selected items, making sure that branch-selected items are turned into node selections (SelectChildNodes). The spreadsheet scripts must return arrays, so this is why the collection returned by GetValue("SelectionList") and then SelectChildNodes must be transferred to an array in a loop. The array must be dimensioned with the right number of elements.

Note

If you have errors in your script as you run the query, the errors will appear in the Log window in the script editor. You can use logmessage calls to help debug scripts.

    Function Current_Selection ()
    	Dim R(), SelList

    	Set SelList = GetValue( "SelectionList" )
    	if SelList.Count = 0 then
    		Current_Selection = R
    		Exit Function
    	end if

    	Set SelList = SelectChildNodes( SelList, False )
    	Dim i
    	ReDim R(SelList.Count - 1)
    	for i = 0 to SelList.Count - 1
    		R(i) = SelList(i)
    	next
    	Current_Selection = R
    End Function

#### Example: Column script

Column scripts also return arrays, but given that they do not return a list of scene elements, but rather a description of which parameter each column must show, a special syntax is required. As this example shows, the syntax is relatively simple because all columns display standard Softimage parameters. Each element in the array is a string composed of three values:

- The parameter to display (for example, kine.local.posx, which will be appended to each item from each line).

- The type of cell. In this case 'Default' is sufficient because Softimage knows what the type of the parameter is (float, integer, boolean, string. etc.)

- The label to display at the top of the column. If left empty, the column title will use the name of the parameter. This is why for the first column containing the name of object (see example) we can omit the type and label information.

Note

Be careful when dimensioning the array because VBScript deals with this a bit strangely: Dim(9) will declare an array that goes from 0 to 9, thus containing 10 items.

    Function Object_Xfo ()
    	Dim R(9)
    	R(0) = "Name"
    	R(1) = "kine.local.posx:Default:Local posx"
    	R(2) = "kine.local.posy:Default:Local posy"
    	R(3) = "kine.local.posz:Default:Local posz"
    	R(4) = "kine.local.rotx:Default:Local rotx"
    	R(5) = "kine.local.roty:Default:Local roty"
    	R(6) = "kine.local.rotz:Default:Local rotz"
    	R(7) = "kine.local.sclx:Default:Local sclx"
    	R(8) = "kine.local.scly:Default:Local scly"
    	R(9) = "kine.local.sclz:Default:Local sclz"
    	Object_Transformations = R
    End Function

#### Example: Query file

The query file is a simple file that can be loaded in the Softimage spreadsheet to tell it which scripts to use. It can be stored anywhere.

- The row script is defined with the tag <line_def>.

- The column script with the tag <col_def>.

For each tag, you supply the scripting language being used, the file where the script will be found, and the name of the function to call.

The script file can be any name, but it must be placed under the ..\Application\DSScripts folder (could be an absolute path too if needed).

    Ex: Object_Xfo_Selected.query
    <version>{1}
    <line_def>
    {
    	vbscript
    	spreadsheet.vbs
    	Current_Selection
    }
    <col_def>
    {
    	vbscript
    	spreadsheet.vbs
    	Object_Xfo
    }

## Single-level Queries with Custom Information

### Example of Showing Geometry Information for All Scene Objects

This example query counts the number of triangles, points, edges (segments) and polygons in the scene. This is the Geometry query used by the spreadsheet in Softimage. Given that there is no specific scene parameter that holds the number of each component, the spreadsheet must be given functions that return these values. These functions can be used to calculate any type of measure, such as the distance of an object to the camera, its the volume, etc.

#### Example: Row Script

Since you want all of the elements in the scene, the row script will be like the one shown below. The SIFilter command ensures we get only the 3D objects from the selection "*".

    Function All_SceneObject ()
    	Dim R(), sceneObjectList
    	set sceneObjectList =  SIFilter( "*", "sceneobject",,siQuickSearch)

    	if sceneObjectList.Count = 0 then
    		All_SceneObject = R
    		Exit Function
    	end if
    End Function

#### Example: Column Script

The columns for cells that will contain custom information are defined using the following syntax:

- Name string that will be used as the label of the column.

- Script as the type of the cell.

- <Script Language>~<script filename>~<name of the function to call>

    Function Object_Geometry ()
    	Dim R(7)
    	R(0) = "Name"
    	R(1) = "Type:Script:vbscript~spreadsheet.vbs~GetObjType"
    	R(2) = "Triangles:Script:vbscript~spreadsheet.vbs~GetNbrTriangles"
    	R(3) = "Points:Script:vbscript~spreadsheet.vbs~GetNbPoints"
    	R(4) = "Segments:Script:vbscript~spreadsheet.vbs~GetNbSegments"
    	R(5) = "Facets:Script:vbscript~spreadsheet.vbs~GetNbFacets"
    	R(6) = "Particles:Script:vbscript~spreadsheet.vbs~GetNbParticles"
    	R(7) = "geomapprox.gapproxmosl:Default:Subdiv"
    	Object_Geometry = R
    End Function

The following script functions are the ones used by the script column query above. These functions are called for each script cell and for each row. Results are cached for operations like sorting.

Note

The function receives a string that corresponds to the object for each line. It must be converted into an object for the object model. The example here is using the GetObject function below, but other methods to do this exists.

    '----------------------------------------------------------------------
    ' GetObject
    ' Function that converts an object string into a scripting object
    ' (for the object model)
    '----------------------------------------------------------------------
    Function GetObject (in_obj)
    	GetObject = "Nothing"
    	if IsEmpty(in_obj) Then
    		exit function
    	end if

    	dim l_coll

    	set l_coll = CreateObject("XSI.Collection")
    	l_coll.Add in_obj
    	set GetObject = l_coll(0)  ' returns a X3DObject
    End Function

    Function GetNbrTriangles( in_obj )
    	GetNbrTriangles = 0
    	Dim l_obj
    	set l_obj = GetObject( in_obj )
    	if l_obj.type = "polymsh" Or l_obj.type = "surfmsh" Or l_obj.type = "crvlist" then
    		GetNbrTriangles = GetNbTriangles( in_obj )
    	end if
    End Function

    Function GetNbPoints( in_obj )
    	GetNbPoints = 0
    	Dim l_obj, l_geom
    	set l_obj = GetObject( in_obj )
    	if l_obj.type = "polymsh" Or l_obj.type = "surfmsh" Or l_obj.type = "crvlist" then
    		set l_geom = l_obj.obj
    		GetNbPoints = l_geom.nb0D
    	end if
    End Function

    Function GetNbSegments( in_obj )
    	GetNbSegments = 0
    	Dim l_obj, l_geom
    	set l_obj = GetObject( in_obj )
    	if l_obj.type = "polymsh" then
    		set l_geom = l_obj.obj
    		GetNbSegments = l_geom.nb1D
    	end if
    End Function

    Function GetNbFacets( in_obj )
    	GetNbFacets = 0
    	Dim l_obj, l_geom
    	set l_obj = GetObject( in_obj )
    	if l_obj.type = "polymsh" Or l_obj.type = "surfmsh" then
    		set l_geom = l_obj.obj
    		GetNbFacets = l_geom.nb2D
    	end if
    End Function

    Function GetNbParticles( in_obj )
    	Dim myType, l_obj, l_part

    	GetNbParticles = 0
    	myType = GetObjType( in_obj )
    	if myType = "cloud" then
    		set l_obj = GetObject( in_obj )
    		set l_part = l_obj.particles
    		GetNbParticles = l_part.count
    	end if
    End Function

#### Example: Query file

Finally, the query file, as you may have already guessed, looks like this:

    Object_Geometry_All.query
    <version>{1}
    <line_def>
    {
    	vbscript
    	spreadsheet.vbs
    	All_SceneObject
    }
    <col_def>
    {
    	vbscript
    	spreadsheet.vbs
    	Object_Geometry
    }
    <sort_order>
    {
    	asc:-1
    }

## Relational Queries

### Example of Showing Cluster List s for All Selected Objects

This example shows a two-level query. The first level lists all of the selected objects, which also display some visibility information. For each of these objects, their clusters will appear, if any. For each of these clusters, their name, their type and number of elements will appear.

#### Example: Query file

This time, start with the query file. As you can see, there are three additional tags:

- <relation>. This gives the name of the relation that you want to appear in the spreadsheet.

- <relation_object_def>. This provides a script which will, for each item provided by the <line_def> script, define the row of second-level items to display in the spreadsheet

- <relation_param_def>. This provides a script which will define the columns we want to display for the second-level items. Nothing prevents you from using the same script as the <col_def> tag; it just depends on the items you want to appear in each row and what columns you want to see.

    <version>{1}
    <line_def>
    {
    	vbscript
    	spreadsheet.vbs
    	Current_Selection
    }
    <col_def>
    {
    	vbscript
    	spreadsheet.vbs
    	Object_Visibility
    }
    <relation>
    {
    	Clusters
    }
    <relation_object_def>
    {
    	vbscript
    	spreadsheet.vbs
    	ObjectClusters
    }
    <relation_param_def>
    {
    	vbscript
    	spreadsheet.vbs
    	Cluster_Info
    }
    <sort_order>
    {
    	asc:-1
    }

#### Example: Relation Object script

This script receives the objects for each row and must return an array that contains the second-level items. Other than that, the script can pretty much do anything that regular scripting in Softimage can do, so you can define a wide variety of relations (for example, objects that are close to the camera, objects that are blue, clusters that have properties, etc.).

In the example below, the script (using the object model and not a command) accesses the geometry of the object, upon which we can find the list of clusters. It excludes texture support objects because these have a geometry but do not hold a cluster container.

    Function ObjectClusters( in_obj )
    	Dim l_obj, i, l_geom, l_clusters, l_cluster

    	set l_obj = GetObject( in_obj )

    	Dim l_cArray()
    	if typename(l_obj) = "X3DObject" and not (l_obj.type = "Texture Support") then

    		set l_geom = l_obj.activeprimitive.geometry
    		set l_clusters = l_geom.clusters

    		if ( l_clusters.count > 0 ) then
    			ReDim l_cArray( l_clusters.count - 1 )
    			for i = 0 to l_clusters.count - 1
    				l_cArray(i) = l_clusters.Item(i)
    			next
    		else
    			ReDim l_cArray(0)
    		end if
    	else
    		ReDim l_cArray(0)
    	end if

    	ObjectClusters = l_cArray
    end function

#### Example: Second-level column script

The script displays the name, type, and number of components for each cluster. There's nothing specifically new here, so here are the needed scripts.

    Function Cluster_Info ()
    	Dim R(2)
    	R(0) = "Name"
    	R(1) = "Type:Script:vbscript~spreadsheet.vbs~GetObjType"
    	R(2) = "Components:Script:vbscript~spreadsheet.vbs~GetNbClsComponents"

    	Cluster_Info = R
    End Function

    Function GetObjType (in_obj)
    	Dim l_obj

    	GetObjType = " "
    	if IsEmpty(in_obj) Then
    		exit function
    	end if

    	set l_obj = GetObject( in_obj )
    	GetObjType = l_obj.Type
    End Function

    Function GetNbClsComponents( in_cls )
    	Dim l_cls, l_elems

    	GetNbClsComponents = 0
    	set l_cls = GetObject( in_cls )
    	if typename(l_cls) = "Cluster" then
    		set l_elems = l_cls.elements
    		GetNbClsComponents = l_elems.count
    	end if
    End Function

#### Example: First-level Column Script

Finally, the first-level columns use the following script. It uses an alternate syntax for the cell: instead of giving a label explicitly for the column, we use the GUID identification of the parameter (which can be found in the appropriate SPDL file). This seeks and displays the appropriate parameter name for you.

    Function Object_Visibility ()
    	Dim R(4)
    	R(0) = "Name"
    	R(1) = "Type:Script:vbscript~spreadsheet.vbs~GetObjType"
    	R(2) = "visibility.viewvis:Default:{D636F4D0-D94B-11D1-B0ED-00A024C79287}"
    	R(3) = "visibility.rendvis:Default:{A9D242E0-D948-11D1-B0ED-00A024C79287}"
    	R(4) = "visibility.selectability:Default:{4256027B-C691-11d2-B740-0008C7A011A6}"
    	Object_Visibility = R
    End Function

## Sorting Queries

One final thing we haven't covered is how to sort the results that are presented to the user when the query gets executed.

By default you can use the following information for the Sort tag. This will sort based on the line header. Use asc for ascendant sort and des for descendent.

    <sort_order>
    {
    	asc:-1
    }

However you can have the query sorted on specific columns. In the example below, the query sorts in ascending order the eighth column, then the third in ascending order, and so on. Of course, the user can then sort the spreadsheet using the contextual menu sort command on any column (only one column sort can be sorted for now).

    <sort_order>
    {
    	asc: 8
    	des: 3
    	asc:-1
    }

## Script Column Syntax

Here is a summary of the currently available syntax for the script columns, which we have seen in the examples in this case study.

A cell description can be given as one of the following options:

- "<param name>"

- "<param name>:<cell type>"

- "<param name>:<cell type>:<column label>"

- "<param name>:<cell type>:<param guid>" (automatic column labeling)

- "<param name>:Script:<script language>~<script filename>~<name of the function to call>"

<param name> | Name of the parameter as you would see in the script log, without the leading dot (ex: kine.posx).
<cell type> | "Default" or "Script". The are other types but we suggest not using them for now...
<column label> | String that will be used as the column header
<param guid> | GUID associated with the parameter, as found in SPDL files.

## Installing the Spreadsheet Queries

After you have written the queries and the query handler support scripts, perform these steps:

1. Put the scripts in the following location:

   $factory/Application/DSScripts

2. Put the queries in the following location:

   $factory/Application/Queries