Search code examples
spotfire

Update property control value using another property control: Spotfire

I am trying to achieve a functionality where I want to update the value of a property control using another property control. Below is the example, I have a dropdown list property control named AsOfDate which holds the date information. Using the value selected in the dropdown list, I would like to display the related quarter information right next to the dropdown list in the form of dropdown or list box (this field will later be used as a column header for my reports).

This is the approach I have:

  1. Created a dropdown list property control.
  2. Using the value from the dropdown list, created a calculated column with Quarter information.
  3. Used the value from the calculated column (step 2) in another property control.

Problem:

The value for step 3 is getting calculated properly but is not getting updated unless there is user input. How do I make the value for property control to update based on just selecting the AsofDate (w/o any further user input).

Calculated Column Code: 

case  
when Quarter(DocumentProperty("AsofDate")) IN (1) then 
Concatenate(Quarter(DocumentProperty("AsofDate")),Year(DocumentProperty("AsofDate"))) 
when Quarter(DocumentProperty("AsofDate")) IN (2, 3, 4) then 
Concatenate(Quarter(DocumentProperty("AsofDate")),Year(DocumentProperty("AsofDate")))
end

In the attached screenshot, as you can see the quarter information is there when we expand the second dropdown but it is not displaying unless selected by user.

enter image description here

Solution

  • let's say your two dropdown controls are linked to a pair of document properties called A and B. the possible values of A are 1-4. the possible values for B are based on unique values in the calculated column [col], which is a concatenation of some string and the value of A.

    when I initially set the value of A to 1, [col] now will contain values like "Something1", "SomethingElse1", "SomeThirdThing1". the dropdown attached to B now contains those three values, and I select "Something1", thereby setting the value of B to "Something1".

    next, I change the value of A to 3. [col] updates to contain "Something3", etc. the dropdown attached to B now shows "---".

    the reason this happens is because the Document Property B is not updated; it's still set to "Something1", even though the acceptable values for its attached Property Control are now "Something3" etc.

    there is no mechanism within Spotfire to account for this; it requires some scripting.

    assuming the example I posed here, add the following script to be executed anytime the value of A changes. it will always update the value of B to the first result in column [col].

    # import the DataValueCursor class
from Spotfire.Dxp.Data import DataValueCursor

# set up the data table reference
dt = Document.Data.Tables["Data Table"]
# set up the column reference
col = DataValueCursor.CreateFormatted(dt.Columns["col"])

# move the cursor to the first row of the column
# (GetRows() returns an iterable, and next() advances it to the first result)
dt.GetRows(col).next()

# update the document property
Document.Properties["B"] = col.CurrentValue

    all that said, while this is an interesting case I wonder what it is you're trying to do and if there isn't a better way. this solution works for the question you posed but I would be curious to know what your end goal is and if there's a more appropriate solution for that problem.