test

Warning when upgrading to Spotfire 10

TIBCO has deprecated the JQueryUI Javascript library in the latest Spotfire 10.0 release

TIBCO Community pages show us how to use JQuery properly

cascading dropdowns

1. Create a drop-down list Property Control to display unique values from the Region column.

2 Create a Calculated Column called ‘Filtered State’. In the expression we should check if the Region Column value equals the value specified in the property we created earlier. If it does, it will output the value from the State Column.

if ([Region] = "${whichRegion}",[State],null)

Next step is to create the second Property Control drop-down. This one will display unique values in the newly created Filtered State Column.
The final step is to create a second Calculated Column called ‘Filtered City’. In the expression we should check if the State Column value equals the value specified in the property we just created. If it does, it will output the value from the City Column.

if ([State] = "${whichState}",[City],null)

Note: If your want to select multiple values and your document property is associated with a multiple selection listbox, then use the following expression:

if ([State] in ($map("'${whichState}'", ",")) ,[City],null)


12 comments:

Unknown said...

Is there a way to make this functionality work with multi-select List Boxes? I have tried the following:

if ([DISTRICTDESC] ~= "$map("[${selectedDistrict}]", ",")",[ASSETDESC],null)

Bust this returns all of the ASSETDESC's rather than those limited by the selectedDistrict multi select list box document property.

Jose Leviaguirre said...

Try changing the "~=" with "in"

if ([DISTRICTDESC] in "$map("[${selectedDistrict}]", ",")",[ASSETDESC],null)

faheem said...

I have a scenario where in I have 3 different dropdowns and the values of each dropdown should change corresponding to the dropdown selection in any of the dropdown in no particular order.
for eg:if I have dp1 dp2 and dp3.The selection of dp1 should change values in dp2 and dp3,selection of dp3 should filter values in dp1 and dp2 and the selection of dp2 should filter values in dp1 and dp3

Jose Leviaguirre said...

Hello Faheem,

Basically you want to implement the same functionality as the filters right? Not sure how complex this can be. Too bad Drop-down list property control does not have a data limited by filter or marking or there is no dropdown filter type.

A work around is to show hide filter by adding a button next to calculated value. Here is an example (replaced with [tags]):

HTML
----
first selection:
[SpotfireControl id="calculatedvalueSpotfirecontrolIdHere" /]
[span class='dropdown' style='cursor:pointer']▼[/span]
[div class='sFilter' style='display:none']
[SpotfireControl id="SpotfireListboxFilterHere" /]
[/div]

second selection:
[SpotfireControl id="calculatedvalueSpotfirecontrolIdHere" /]
[span class='dropdown' style='cursor:pointer']▼[/span]
[div class='sFilter' style='display:none']
[SpotfireControl id="SpotfireListboxFilterHere" /]
[/div]

Javascript
----------
//show or hide filter when clicking on dropdown arrow
$(".dropdown").on('click',function(){
$(this).next(".sFilter").toggle();
})

//optional auto hide when making a selection
$(".sFilter").on('click',function(){
$(this).hide();
})


I'll post something about this soon

Jyotsna Shrivastawa said...

Hi Jose,
Hope you are having great day
Above script you provided for scenarios asked by Faheem

Could you please let me know is this functionality is possible to implement without script in Spotfire
I mean is their any way to perform above drop down scenario without script
Using custom expression

Thanks
Jyotsna

Jose Leviaguirre said...

hello Jyotsna,

I cannot think of another way of using this alternative without using scripting, but I am sure there are many other ways to achieve the same goal, but it depends in what are is the story you are trying to tell.

Vanice Souza said...

Hi,

I have a scenario with the 3 columns (Country, City and Region) as I make this formula, I tried to use its logic, but it does not work?

Calculated column: if ([Country] = "${pCountry}",[City],[Region])

Another question "null" can not be put in the formula, it gives error.

Is it possible to do that?

Vanice Souza said...

Hi

A question when creating a document properties has content limit for the field? I have a field that has more than 5000 variables, so I'm trying to group it to see if it decreases the result, but there are some that stay above 1000 and do not have all the information.
Do you know if this exists?

Regards,

Jose Leviaguirre said...

Vanice, thanks for your note. Yes, there is a limitation for the number of items a dropdown can handle. It cannot display more than 1K values. However, you should try keep down the number of available values as much as possible in order to make teh control easier to use. This is well written in the excellent documentation TIBCO has under Visualization/Text Area/Details on Property Controls section.

Jose Leviaguirre said...

Vanice, for your other comment, you can try 3 different dropdowns and 2 calculated columns:

1st dropdown holds unique country values in a pCountry doc property
2nd dropdown shows available cities for the selected pCountry
3rd dropdown displays zipcodes for the selected pCity

[country] unique column values are shown in the first dropdown
[dCity] calculated column unique values goes on the second dropdown
[dZipcode] holds unique values from a calculated column on the third dropdown

your corresponding calculated values for your second and third dropdowns are:

if([country]="${pCountry}",[city],null) as [dCity]
if([dCity]="${pCity}",[Zipcode],null) as [dZipcode]

Hope this helps.

Vanice Mora said...

Hi Jose,

One question, I already did an application with this functionality and it was show.

That way my formula was hierarchical, that is, I need to select Country, City and Region.

How do I, in my case I have 3 fields (Country, City and Region), randomly filter without obeying a hierarchy? And also because it will not be hierarchical, I would like the data to appear, instead of "None".

Example:

In some cases, I will have to select Country and Region or City and Region, etc.


Can you help me? It's possible in Spotfire?

Jose Leviaguirre said...

Hello Vanice. In this example, the cascading dropdowns must follow the hierarchy. I recommend using a filter control instead to have that cross functionality. Too bad there are no dropdown filter controls yet.