Searching and sorting in Interactive Grids can be a challenge.
When using the search field in the toolbar, this performs a text-only search; it only returns results for Text columns, ignoring all number columns, date columns, etc.
In the example below, searching for 100001 (which is in a Number column N_01) will not give a result, but 100011 (in Text column C_11) will.
On the other hand, sorting numbers only works properly for Number columns - Text columns are sorted alphabetically (e.g. 100 comes before 99).
In the example given here, the default sorting is done on Text column C_11 to show what happens then.
So what if you want to search AND sort number columns properly, and have them look nice?
Let's do some experimenting with Number, Text and Hidden columns:
- N_01: A standard Number field. Searching doesn't work because it is a number, but sorting is OK.
- N_02: Applies a format mask to force a leading 0. Not very nice to look at, and searching doesn't work because it is still a number, but sorting is OK.
- C_11: A standard Text field. Searching works because it is text, but sorting is alphabetically.
- C_12: The same format mask as N_02, but now applied to a Text field. This has no effect however, so not very useful.
- C_13: Same as N_03, but now for a Text field. Same result as N_03 as well.
- C_14: The same format mask as N_02 and C_12, but now applied in the query itself instead of Apex. Searching and sorting both works, but (like N_02) it is quite ugly. So maybe somewhat useful.
- H_21: A Hidden column. This column is not rendered by Apex, so searching doesn't work (try "97021").
- H_22: A Text column that has been hidden in the standard report. Searching does work here (try "97022") so this may be useful if you fill it with a concatenation of all the "unsearchable" columns. However, if the user is allowed to hide/show columns, this column would look pretty weird when shown.
- H_23: A Text column that has been hidden using JavaScript (function hideCol_23). Like H_22, searching does work (try "97023"). The user cannot show or hide the column because that option has been disabled, but it is still visible in the Actions -> Columns menu. So pretty nice, except that the column reappears when the user clicks Reset, because the JavaScript is only executed during page load.
- H_24: Same as H_23 using JavaScript (now using function hideCol_24), but with a Dynamic Action added to handle the Reset issue. Searching works (try "97024"). Like H_23, the column is still visible in the Actions -> Columns menu, but that is the only drawback I think.