Saturday, January 5, 2013

Always One Selected Value Really

I developed a document a few months ago that provided the user the ability to select which currency (Dollars, Euros, Rupees, etc) to use for presenting financial data. The expressions in the document did not work correctly unless one and only one currency was selected. Originally, I clicked on the Always One Selected Value option available in Listbox and Multibox properties. But there was a problem with the option sometimes turning itself off and the first indication of a problem is when users noticed glaringly incorrect financial quantities!

The problem with the Always One Selected Value option is that it can turn itself off when someone does a Reduce Data operation on the document. My team frequently does that to reduce document file size in order to email a document or store it in the version control system.

Here's an alternative way to accomplish the same thing. In my document, I wanted a single value from the CURRENCY field always selected. So, I set up an action to be executed based on the OnAnySelect or OnOpen events. Go to Settings-Document Properties and click on the Triggers tab. In the Document Event Triggers window click on OnAnySelect. In the Field Event Triggers window, scroll down to CURRENCY (use your field name, of course), click on it and then click the OnSelect Edit Action(s) button.

When the Actions dialog box opens, click the Add button, add a Select In Field action, type CURRENCY (use your field name) into the Field box, and then type this into Search String:


That expression makes sure only one value is selected and, if it isn't, it automatically chooses a default value - in this case it chooses Euro as a default currency value. You could also use a variable or an expression for the default value.

Click OK and then do the same thing for the OnOpen event. If needed for your situation you may also want to do the same thing for the OnPostReload event. Now, the document will always have one selected value for the field and if anyone tries to deselect it or select multiple values the trigger will switch to the default selection. For my document, I also checked off the Always One Selected Value checkbox in the Listbox Presentation tab.

[Author's Note 1Mar2013
Based on a reader comment, here's a slightly different expression that will return the selection to the previous valid selection instead of a default value:


It uses set analysis syntax to get the previous selection and only uses the default if the previous selection is null. 


Unknown said...

Your solution is a good one. An alternative is to use firstsortedvalue(Currency) to pick out one single currency.

Donald Hutchins said...

Great idea.

I applied the same idea for a field where I want at least one selection. In my case, field is YEAR, so expression is:

=If(GetSelectedCount(Year)=0,Year(Today(1)),'('&(Concat(Distinct Year,'|'))&')')

Anonymous said...

Good solution, however is there any way to make the default selection the last element the user selected ? Currently my problem is that I do not want to train the users to clear that listbox and then make a selection (as in my case I always need something selected else all expressions will not work) but if there is already a selection made and they try to switch to a different option it simply defaults back to my hard coded value... I need it to default to the most recent click.

-TB said...

That is a great suggestion! I added it to the text of the blog posting. Thanks.

Anonymous said...

Great post!

Have you tried, how selecting one and only one value is affected by alternate states?

In case in Group1 there should be only one value selected in FieldX, while in Group2 number of selected values should be unlimited in FieldX. Values should be copied between states (on switching sheets) when possible (Group1->Group2 always, Group2->Group1 only if one value is selected).
I ran into an issue that Copy State Contents Group1->Group2 copies "Always One Selected Value" either...

Anonymous said...

Great post, Thank you very much.

Andy said...

OnOpen-triggers does not work when using Ajax-client so there is a problem to have a selected value when starting from scratch...

Anonymous said...

I just tested a OnOpen trigger that selects a field value and sets a variable in AJAX and it worked fine. Maybe it's a recent enhancement or a more nuanced restriction and the documentation is just off.

Anonymous said...

How about this scenario: There is a condition, Dashboard would load data on Monday, not load data on Tuesday. On Tuesday, Dashboard would be empty without anything(It's different with reducing Dashboard. Reducing data would keep Table View in Dashboard. But on Tuesday, it is totally empty.) In this case, "Always would be selected one" will not work here. Do you have any suggestion for me? Thanks very much! I only have an idea to bring this column in the Dashboard when refresh on Tuesday.

Arif said...

Listbox Always Select One Value Option Not working while using expression in the field.
Any suggestions? Please help.

Thanks in Advance

Anonymous said...

It is working great! But is there any way we can disable this in moving to different sheet and enable again when came back to previous sheet? My scenario is i had enable always one selected value in sheet1 and i have to clear this selection in sheet2.