In earlier posts we have discussed various strategies for manipulating Property Controls. One common request which we have not covered yet is to have a mechanism to easily select all values in a property control.
Let�s assume we have a Property Control that displays all the unique values in a given column. Below shows an example of a Property Control which displays all unique values from a column named 'Approver'.
This list could get rather long if the column has lots of unique values. You can use CTRL and SHIFT to select multiple values but cannot use SELECT + A (Select All is not supported). It would save time for consumers to add a link underneath which allows them to select all, like shown below.
One solution to accomplish this is to use a script to loop through all the values in a column and add them to an array. We can then later set the property (that is attached to the property control) to the array value, which will have the effect of selecting all values in the control.
Assuming our column is called �Approver�, below is the script:
from System import Array
from Spotfire.Dxp.Data import IndexSet
from Spotfire.Dxp.Data import DataValueCursor
#Get access to the Column from which we want to get the values from
myCol = Document.ActiveDataTableReference.Columns["Approver"]
rowCount = Document.ActiveDataTableReference.RowCount
rowsToInclude = IndexSet(rowCount,True)
#Create a cursor to the Column we wish to get the values from
cursor1 = DataValueCursor.CreateFormatted(Document.ActiveDataTableReference.Columns["Approver"])
strArray = Array.CreateInstance(str,rowCount)
#Loop through all rows, retrieve value for specific column, and add value into array
for row in Document.ActiveDataTableReference.GetRows(rowsToInclude,cursor1):
rowIndex = row.Index
value1 = cursor1.CurrentValue
strArray[rowIndex-1] = value1
#Set property to array created above
myCol.Properties.SetProperty("whichApprovers",strArray)
One downside of this script is that it loops through all rows in the column, and this can be very inefficient if there are a lot of values. If you want to avoid having to loop through rows, you can use the solution shown next.
After you create the property control, you will create another multi-select list box property control, which is attached to a different property. Then, after exiting edit mode, select all the values manually in the property control, as shown below ( in the second property control on the right).
Once you do this, the property you just created will have a list of all unique values in that column.
You can then create a script control which sets the property associated with the first list box to the value of the property you just created above. Assuming our first property is called whichApprovers and the property we create which contains all unique values is called allApprovers, the script would look like the following:
myCol = Document.ActiveDataTableReference.Columns["Approver"]
allVals = myCol.Properties.GetProperty("allApprovers")
myCol.Properties.SetProperty("whichApprovers",allVals)
Once you do this, you can then delete the second property control. The property it was attached to remains in the analysis file and will continue to hold a list of all unique values.
While this is more work up front for the author, it works faster on large datasets as the script does not have to loop through all rows.
Interested in learning more about what you can do with Script Controls and/or Property Controls? Consider taking our Script Controls blended training course or our Authoring Bootcamp (starting this week).
