Spotfire script to filter multiple tables without relationships

Question

I was able to approximate the minimum functionality that I'd need with the code below. It's incredibly kludgy. I would really appreciate some feedback on the code to make it better. In particular, I would love to preserve the filter cascading. Thanks.

import Spotfire.Dxp.Application.Filters as filters
from Spotfire.Dxp.Data import DataPropertyClass
from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers

aProp= Document.Properties["DistinctReasons"]

thePanel = Document.ActivePageReference.FilterPanel

theFilter = thePanel.TableGroups[0].GetFilter("Reason")
theFilter2 = thePanel.TableGroups[1].GetFilter("Reason")

theFilter.FilterReference.TypeId = FilterTypeIdentifiers.ListBoxFilter
theFilter2.FilterReference.TypeId = FilterTypeIdentifiers.ListBoxFilter

thelistboxFilter = theFilter.FilterReference.As[filters.ListBoxFilter]()
thelistboxFilter2 = theFilter2.FilterReference.As[filters.ListBoxFilter]()

thelistboxFilter.IncludeAllValues = False
thelistboxFilter2.IncludeAllValues = False

thelistboxFilter.SetSelection(aProp)
thelistboxFilter2.SetSelection(aProp)

I have a dashboard featuring two tables with matching columns. I would like to be able to filter both tables without creating a standard relationship, as this creates artificial parity between the tables.

The code below may address this, but I don't know how to implement it. I found it here. I have two specific questions:

How is the variable myColNames supposed to be populated? And how should I properly assign this to a doc property. This is what I have done:

I created a document property named DocPropMultiList: Property Control and assigned the script. To populate myColNames, I passed a string with a column name, and that appears to do something, but the script still fails ''' Traceback (most recent call last): File "Spotfire.Dxp.Application.ScriptSupport", line unknown, in ExecuteForDebugging File "", line 83, in File "", line 78, in Clear_or_Filter NameError: name 'DocPropMultiList' is not defined '''

Full disclosure: I am at my fourth month of learning Python, which has helped, and I know next to nothing to IronPython implementation in Spotfire. Any help is appreciated. Thanks.

from Spotfire.Dxp.Data import *
import Spotfire.Dxp.Application.Filters as filters
from Spotfire.Dxp.Application.Filters import FilterTypeIdentifiers
from System import String

def ChangeFilterstoListBox():
    #CHANGE ALL FILTERS TO LISTS BOX
    for aPage in Document.Pages:
       aFilterPanel = aPage.FilterPanel
       for aTableGroup in aFilterPanel.TableGroups:
          for aFilterHandle in aTableGroup.FilterHandles:            
             if aFilterHandle.FilterReference.Name == myColName:
                aFilterHandle.FilterReference.TypeId = FilterTypeIdentifiers.ListBoxFilter

def DocPropertyList():
    # Set Values As List from Document Property
    mySelection = list(Document.Properties[DocPropMultiList])   
    return mySelection

def GetCurrentDict():
    d = dict()
    for x in Document.Data.Tables:
        #Check Column Name vs List of Columns from Table
        if myColName in [str(item) for item in x.Columns] :         
            # get filter
            filter = Document.FilteringSchemes[0][x][x.Columns[myColName]]       
            # get current filter type
            filterType = filter.TypeId       
            # change to checkbox to easily access unique value
            filter.TypeId = FilterTypeIdentifiers.CheckBoxFilter         
            # Capture List of Items from CheckBoxFilter
            mycountry_list = [value for value in filter.As[filters.CheckBoxFilter]().Values]     
            # return to old filter type
            filter.TypeId = filterType
            #Append to Dict     
            for item in mycountry_list:
                 d.setdefault(x.Name, []).append(item)
    return d 

def GetMyUpdateDict():
    d = GetCurrentDict()
    mySelection =DocPropertyList()
    for i in d:
        #Combine With DocPoperty List 
        combined = list(set(mySelection) & set([x for x in d[i]]))
        #Update Dictonary With Matching Values Only  
        d.update({i:combined})
    return d

def RunFilters():
    MyDict = GetMyUpdateDict()
    myPanel = Document.ActivePageReference.FilterPanel
    for item in myPanel.TableGroups:    
        #print(item.Name)
        if item.GetFilter(myColName):
            myString = ",".join(MyDict[item.Name] )
            myFilter = item.GetFilter(myColName)
            lbFilter = myFilter.FilterReference.As[filters.ListBoxFilter]()
            lbFilter.IncludeAllValues=False
            strVals = myString
            if strVals!=String.Empty:
              print(myString, item.Name )
              lbFilter.SetSelection(strVals.split(','))
            else:
              print(myString, item.Name )
              lbFilter.IncludeAllValues=False

def ClearFilters():
    myPanel = Document.ActivePageReference.FilterPanel
    for item in myPanel.TableGroups:
        if item.GetFilter(myColName):
            myFilter = item.GetFilter(myColName)
            lbFilter = myFilter.FilterReference.As[filters.ListBoxFilter]()
            lbFilter.Reset()

def Clear_or_Filter():
    ChangeFilterstoListBox()
    if Document.Properties[DocPropMultiList] != None: 
        RunFilters()
    else:       
        ClearFilters()

Clear_or_Filter()

Answer 1

DocPropMultiList property control should be multilist document property with the filtering values.

The MyColName variable should be added in the script parameters as a string (ex:'Column_Name') which is the name of the column you are filtering. Column name being the same for both tables.

I believe I wrote the original script quite long because when trying to set both filters some problems would occur when one value was present in one table but not the other.

Answer 2

Have you considered creating a property control and using data limiting expressions? This would let you filter two visualizations without creating a relationship between them, with no need for a script.