Help executing both a server-side search and sort on a list

[ecalan]

I have a UX with both a keyword search field and a sort dropdown box which selects the field to sort on. This is a fairly common configuration but I have not found a solution. The list is a paginated freeform list which is long, has embedded images and is mobile, so both the search and sort have to be server-side.

Problem 1 was setting the sort to the selected column. This I solved, though probably not the best solution, by using a series of "if" statements to test for the dropdown value and then executing a sort on that field.

Problem 2 I have not solved and that is how to execute the server-side sort without losing (or by re-applying) the search field. If the search returns 20 rows, applying a server-side sort returns all rows.

- The "Filter Records in a List Control" action JS would be great but, though it says it is a "filter/sort" (and the video on it's use shows "order" in the debug info) I don't see the sort option. Am I just missing something here?

- I also tried {dialog.object}._filterList which does have a sort parameter but it needs to test for "contained in" and not just "=." Is there a syntax for that which works for this method?

Is there an easier way to accomplish this common configuration?

[ecalan]

Re: Help executing both a server-side search and sort on a list

Update: I did resolve this issue, after several dead ends, with a fairly complex use of the filters in the sql builder, arguments, placeholder fields and {dialog.object}._filterList. It works great but feels quite "rigged." There still must be a simpler solution so a list can have a search part, like a grid, with sort and search executed server-side together. I'm guessing this is a technological issue with lists as the "Filter records in list control" action does not have the order control it says it has. Again, it feels like I'm just not finding it. Please, somebody confirm or deny if I am crazy.

Would still love to hear a better solution and, if anyone wants me to post my cumbersome solution, I will.

[frankbicknell]

Re: Help executing both a server-side search and sort on a list

Please post your cumbersome solution.

Ps I don't know you well enough to determine if you are crazy.

[ecalan]

Re: Help executing both a server-side search and sort on a list

Thanks for asking Frank. (haha there is an hour I won't get back. ) Here it is:

Remember, I am trying to do three things at once: 1. Keyword search three fields, 2. Filter on a status field and 3. Order on a selected column (field since it is a freeform list).

First, I used the sql builder to set a filter using "LIKE" and "or" to accomplish a multiple field keyword search AND apply the status filter. The bracketing of the OR statements was tricky and the rendered sql does not look like the settings in the builder but it works.

WHERE (table.field1 LIKE :search OR table.field2 LIKE :search OR table.field3 LIKE :search) AND (table.Status LIKE :filter)

However, since the "LIKE" function requires the format %term% I created a dummy field, hidden behind another element with a client side computed value of '%'+{search}+'%' and set the :search argument to be set at runtime to the value in the dummy field.

For setting the field to sort on, I used this (named "sortaction"):

var a ={dialog.object}.getValue('Sort');

if (a=="User"){
{dialog.object}._filterList('vidlistb4','1=1','User','');

}
if (a=="Popular"){

{dialog.object}._filterList('vidlistb4','1=1','rank','');
}
if (a=="Recent"){

{dialog.object}._filterList('vidlistb4','1=1','DateAdded:-1','');


}

Notice that the filter parameter is set to all by 1=1. The base SQL is still filtered by the value set in the dummy field as the :search argument. I used "filterList" to do the sort because I could not find another way to do a server side sort with either Action JS or a UX method.

For the filter I also used a dummy field (filter2) and it looks something like this:

var a ={dialog.object}.getValue('Filter');
var b =('%'+a+'%');

{dialog.object}.setValue('Filter2',b);
{dialog.object}.runAction('Sortaction');

Note: Like search, the :filter argument is set to filter2. And, by using "sortaction" for the final search it picks up all parameters (search, sort and filter). I also have the search button set to just execute "sortaction."

You can see why I call this "rigged." I am still way open to the easier method I must have missed. I do wish I was crazy and the "Filter Records in a List Control" action JS had the sort option it says it has. I must be blind I guess.