In a previous post, Overcoming Checkbox Shortcomings Part 1 , I wrote about a technique I devised to overcome the limitation of the Xcelsius checkbox where it will not programmatically switch to the value in the cell. I needed 10 yes-no checkboxes – one for each row of a tabular display and to be reset to yes/no depending on user interaction. I replaced them by using dropdowns (called a combo-box as called within Xcelsius) and some cool Xcelsius tricks. However, in this post I will show you the work around I came up with to beat the same limitation, but in this case, the end user still got the check-box, which was very nice because of its intuitiveness.
In the same dashboard, I had the requirement for a “Select All” checkbox that will control the values of the ten dropdowns and also reset itself on certain events as listed below. If checked by user then all the 10 dropdowns need to turn to “Yes” and if unchecked they should all turn to “No”.
- Should default to un-checked, that is, all the dropdowns initialized to “No”
- Must reset to un-checked when navigated to a new page
- Must reset to un-checked when the warehouse selection is changed (which changes the data in the table).
The third part of the requirement is what called for the work-around that I will describe in this post. This was not achieved easily, for the same shortcoming that check-boxes does not allow binding the “Selected Item” property to a cell that I can code to alter at run time.
For this I used a push button component and two check-boxes:
The Source Data property of both the check-boxes is set to Control!$L$6:$M$6
Control!$L$6 is hard coded to “No” and Control!$M$6 is hard coded to “Yes”.
Destination is blank for both.
The Selected Item of SELECT ALL Un-Checked is set to “Unchecked”.
The Selected Item of Select All CHECKED is set to “Checked”
The dynamic visibility “Status” of both the check-boxes is Control!$N$5
Key for SELECT ALL Un-Checked = “NC”
And
Key for “Select All CHECKED” = “C”
The value of Control!$N$5 is calculated as
=IF(ISERROR(MATCH(“No”, Control!$N$8:$N$17,0)),”C”,”NC”)
What is Control!$N$8:$N$17? It is the range to which the “Selected Item” Property of each dropdown is bound to.
The selected item property of first combo-box (Row 1) = Control!$N$8
The selected item property of second combo-box (Row 2) = Control!$N$9 and so on until the 10th combo-box (Row 10) = Control!$N$17
They are also the “Insert Selected Item” mapped cell for the same combo-box. That means whenever the user chooses a value manually the default cell value changes to the selected value. This is done to facilitate the reset of the cells to be recognized as a change of value so the combo-box picks up the new value programmatically too.
(Because if the “Selected Item” cell is always “Yes”, combo-box will default to “Yes” initially but after it is manually changed to “No” by user the default cell is still “Yes” and when you try to reset it to “Yes” the combo-box does not get triggered to change the selection).
Control!$N$8** is calculated based on Control!$N$6 which is the destination of “Select All” control. If the destination cell Control!$N$6 is null (default) or “No” (when manually unchecked) then N8** will also be “No”
If Control!$N$6 is “Yes” then N8 will be “Yes” too.
This allows the combo boxes to change to Yes or No based on the “Select All” selection.
Control!$N$8:$N$17 is also reset to “No” by the following events
-When a user navigates to a new page – this achieved by a hidden filter that drops “No” to every row in this range as soon as a new page is activated.
-When a user selects a new warehouse – this is achieved by another hidden filter that resets the Control!$N$8:$O$8 through Control!$N$17:$O$17 by “No” and “Yes” with source range = Control!$I$8:$J$57 which is a hard-coded range with “No” on column I and “Yes” on column J and added as an additional columns with the warehouse list. So there is a pair of
“No” and “Yes” for every unique value of a warehouse. As you select a warehouse each row within Control!$N$8:$O$8 through Control!$N$17:$O$17 gets replaced with No and Yes respectively no matter what they were before. Hence the Scanned combo-boxes resets to No as you select a new warehouse.
This results Control!$N$5 to change to “NC” and so “SELECT ALL Un-Checked” which is Unchecked be seen.
The next most important part is setup of the Push Button component which I added to move the data programmatically. However, I set it to transparent so that is not seen by the user and user think they are clicking on check-box. The push button moves data from Control!$P$5 to Control!$N$6
Cell Control!$N$6 is initialized to NULL and so Control!$N$8: Control!$N$17 are to “No”.
Below is the formula at Control!$P$5
=IF(OR(N6=”No”,N6=””),”Yes”,IF(AND(N6=”Yes”,ISERROR(MATCH(“Yes”,$N$8:$N$17,0))),”Yes”,”No”))
So Control!$P$5 will default to “Yes” at load.
The goal of the formula is to make P5 Yes when N6 is null, when N6 is “No”.
When N6 is Yes P5 should be No expect when all the combo-boxes are No or at least the range N8:N17 is all No.
The below formula at Control!$P$5 will turn its value to “Yes” as a result of =IF(OR(N6=”No”,N6=””),”Yes”,IF(AND(N6=”Yes”,ISERROR(MATCH(“Yes”,$N$8:$N$17,0))),”Yes”,”No”))
- This is the source of the transparent push button – so when user checks the “Select All” control they essentially clicks this button to move “Yes” to cell Control!$N$6
- As soon as Control!$N$6 switches to “Yes” , all the cells Control!$N$17 through Control!$O$17 will also switch to “Yes
- As a result all the Scanned combo-boxes will now turn to “Yes”
The Warehouse filter combo box setup is already described in the previous post I mentioned.
Please try the above tricks and let us know your scenario where it was useful in the comments. Also, please share with others so everyone can benefit and save some trouble and time!!
Be on the lookout for more tips and tricks in the future!
Great solution to the problem. Another option would be to use the checkbox component included in the Flat UI suite which can have its selected property bound to a cell and be set programmatically.