.gif)
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)
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:
16 comments:
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.
Try changing the "~=" with "in"
if ([DISTRICTDESC] in "$map("[${selectedDistrict}]", ",")",[ASSETDESC],null)
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
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
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
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.
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?
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,
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.
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.
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?
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.
Hi, I am trying to apply this formula "if ([State] = "${whichState}",[City],null)" , but the calculated columns where I put it doesn't allow me execute this formula.
It says that the condition has to be Boolean. Both [State] and and Propertycontrol {whichState} are in string. I don't understand how this should work.
Hello Ha Sai, The first argument on the condition has be a boolean (True or False) or the expression of the first argument has to return a boolean. In the example, the expression of the first argument you mention is: [state]="${whichState}" returns True if the string column [state] matches the string document property whichState. Note that both the column and the doc property in this case are both strings. If the first argument is true, the IF function will return the [City]. Otherwise it will return null.
Good moorning Jose,
I have a cuestion, if a have 2 dropdown list wich are using diferente datatables and i want that the second dropdown list to show me only the options filtered by the selection made in the first dropdown list. For example, we have a data table with States, and another one with counties, each one with their dropdown list, how can i filter the counties options to show me only the ones for the state selected in the first dropdown list.
Thank you very much,
Best regards
Hello Carlos. Thanks for your comments and feedback. It is basically the same principle. This is a good idea for a Dr. Spotfire Quick Tip Video. I will release one soon. We have multiple social media channels where we listen to our customersand try to help them out the best we can. This is one of them. Cheers!
Post a Comment