Single filter for multiple data tables
I need to filter two data tables simultaneously using a common set of fields. While I can achieve this for cases when there's only one field to filter by, I can't do it for two or more fields.
There are two data tables, T1 and T2. Each of the tables has fields: A, B, C, D, etc... . There are 6 visualizations based on both of the tables on two different pages (displaying the same data in different ways).
I need the following filters to be present on both pages and be synchronized (i.e. changing filter settings on one page must be propagated to filter settings on the other page):
1. By field A: a list of check boxes (the number of unique values in T1.A + T2.A is less than 10);
2. By field B: a multiselect list box (the number of unique values in T1.B + T2.B is about 100);
There are values that only present in one of the tables but not both, that's why I need the filters to display the set union of T1.A + T2.A and T1.B + T2.B.
When a set X of values is selected in the filter 1, both of the tables must be filtered to rows that have A in X.
When a set Y of values is selected in the filter 2, both of the tables must be filtered to rows that have B in Y.
I tried to extract unique values from T1.A + T2.A into a reference table RA and use it in two relations to both T1 and T2. Then filtering in RA caused filtering in both T1 and T2. That's working. But I also need the same for field B. I tried to add another reference table RB for T1.B + T2.B and use it in two relations to both T1 and T2. When I add the second relation (to T2), Spotfire doesn't allow the relation saying it would cause cyclic dependency.
I understand the goal can be achieved using two document properties (e.g. SelectedA and SelectedB) mapped to two multiselect list boxes in a text area on each of the pages. The values of the properties can then be used in the filter expression of each of the visualizations. But the list boxes don't seem to have the substring search feature as filters do, which I'd like to have. Moreover, when real filters are used and one of them is set to a specific value then the second filter displays only the rows that haven't been filtered out by the first filter, which allows the user to narrow down the set of rows by applying filters one by one.
Are there any other approaches to this task besides document properties?