Filter Criteria Text field using "greater than or equal to" and case sensitivity.

[stonebranch1]

On a Text field, we were expecting the operator "greater than or equal to" to behave similar to "starts with" but that it would also match the data greater than as well. The "starts with" is case insensitive but the >= is not.

For example, I have the following data "abc", "Abc", "ABc", "ABC", "cba", "Cba", "CBa", "CBA".

If I filter with "starts with" and value of "a", I get the 4 abc rows as expected. However if I use "greater than or equal to" with the same value of "a", I only get rows "abc" and "cba".

Attached are screen shots.

Code to reproduce this:

public class Sandbox1 implements EntryPoint {
    DataSource dataSource;
    ListGrid grid;
    int counter = 0;
    ListGridRecord[] cacheData = new ListGridRecord[8];
    public static final String fpk = "fieldPK";
    public static final String f1 = "field1";
    public static final String f2 = "field2";
    public static final String f3 = "field3";

    @Override
    public void onModuleLoad() {

        int i = 0;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "abc");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "Abc");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "ABc");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "ABC");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "cba");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "Cba");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "CBa");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());
        i++;
        cacheData[i] = new ListGridRecord();
        cacheData[i].setAttribute(fpk, i);
        cacheData[i].setAttribute(f1, "CBA");
        cacheData[i].setAttribute(f2, i * 10);
        cacheData[i].setAttribute(f3, new Date());

        final VLayout appLayout = new VLayout();
        appLayout.setWidth100();
        appLayout.setHeight100();

        buildDataSource();
        buildGrid();

        final FilterBuilder fb = new FilterBuilder();
        fb.setTopOperatorAppearance(TopOperatorAppearance.RADIO);
        fb.setTopOperator(LogicalOperator.AND);
        fb.setShowModeSwitcher(Boolean.TRUE);
        fb.setDataSource(dataSource);

        IButton filterButton = new IButton("Filter");
        filterButton.addClickHandler(new ClickHandler() {
            @Override
            public void onClick(ClickEvent event) {
                grid.filterData(fb.getCriteria());
            }
        });

        appLayout.setMargin(5);
        appLayout.setMembersMargin(5);
        appLayout.addMembers(fb, filterButton, grid);
        appLayout.draw();
    }

    private void buildGrid() {
        grid = new ListGrid();
        grid.setWidth100();
        grid.setHeight100();
        grid.setDataFetchMode(FetchMode.PAGED);
        grid.setTitle("Test Grid");
        grid.setAutoFetchData(true);
        ListGridField gfld1 = new ListGridField(f1, "Field 1");
        ListGridField gfld2 = new ListGridField(f2, "Field 2");
        ListGridField gfld3 = new ListGridField(f3, "Field 3");
        grid.setFields(gfld1, gfld2, gfld3);
        grid.setDataSource(dataSource, grid.getAllFields());
    }

    private void buildDataSource() {
        dataSource = new DataSource();
        dataSource.setClientOnly(true);
        // dataSource.setDataFormat(DSDataFormat.JSON);
        DataSourceField fldId = new DataSourceField(fpk, FieldType.INTEGER);
        fldId.setPrimaryKey(true);
        DataSourceField fld1 = new DataSourceField(f1, FieldType.TEXT);
        DataSourceField fld2 = new DataSourceField(f2, FieldType.INTEGER);
        DataSourceField fld3 = new DataSourceField(f3, FieldType.DATE);
        dataSource.setFields(fldId, fld1, fld2, fld3);
        dataSource.setCacheData(cacheData);
    }
}

Using SmartClient Version: v9.1p_2014-11-09/Pro Deployment (built 2014-11-09)

[Isomorphic]

Natively, some databases will treat greater than case-sensitively (Oracle), most others will treat it case-insensitively but with subtle differences about what that means, and with several DBs you can also configure which way it works when you create the schema.

We don't currently try to force consistency across databases in this area, which would have performance consequences and inevitably one choice or the other would surprise one set of users or another.

Also note that we have two versions of "starts with": "iStartsWith" which is case insensitive and "startsWith" which is case sensitive. Since case insensitive is the more commonly desired option, it's end-user-visible description is just "Starts with" and the case sensitive one has the longer "Starts with (match case)".

[stonebranch1]

We are fine with the "starts with" and we want it case insensitive and that all works fine and as expected.

It is the greater than or equal to the is giving us a problem. It does not match the ordering. For example the unfiltered list sorts little "a" before big "A" and then "c" followed by "C". One would expect that saying >= "a" would then show all the same rows but it does not.

This example is not making use of any database and I understand the case-sensitivity w/r to the databases. We have set ours up to be case insensitive.

[Blama]

Hi stonebranch1,

please see this mildly related thread.

Also, if your problem is mainly sorting, you could specify your own easy SortSpecifier via ListGridField.setSortSpecifier().

Regarding the filtering: If it is the case that a clientside-filtering on (completely loaded?) dataset results in unwanted results for you, ensuring that a Server-fetch happens for every filter action might help you (as you say the DB reacts as wanted in your case).
Generally speaking I don't think this is a good idea, but perhaps(?) it can help.

Best regards,
Blama

[Isomorphic]

Just for completeness: we don't really agree that all users would be surprised by greaterThan eliminating (or even not eliminating) particular rows when they are looking at a sort that happens to demonstrate a particular ordering.

After all the same argument could be made for any other search operator whose case sensitivity happens to differ from the case sensitivity of the sort, and we just don't see other complaints of this kind.

But if you really think this could be a source of user confusion, then yes, you'd want to use a normalizer to make the sort match the filtering in the way you seem to expect.

[stonebranch1]

In our scenario, if a fetch is performed using the >= the data is returned from the server and displayed in the ListGrid.

If however no server-side fetch is executed and only client-side filtering performed, then the rows disappear and are not shown.

The results are not consistent and we are looking for a way to avoid this. I only brought up the ordering as an example of how it looks when data displayed after a row no long qualifies even though you use >= of the value in that row.

Should we turn off client-side filtering? (even though that is nice to have)

[stonebranch1]

What collation are you using for sorting vs client-side filtering because they are obviously not the same?

Typically the same collation is used for both unless specifically overridden in some manner. Otherwise inconsistent results will be had.

So you really believe that users would not be surprised that if they have a column that is sorted in the ListGrid showing in sorted order "a", "A", "c", "C" and then applied a filter saying >= "a" and then only "a", "c" were displayed?

[Blama]

In our scenario, if a fetch is performed using the >= the data is returned from the server and displayed in the ListGrid.

Hi stonebranch1,

out of curiosity I'm interested in the answer as well.
Regarding your use-case:
Wouldn't it be possible for you to limit the FilterBuilder's operator-list for text fields to the "startsWith"/"iStartsWith" etc. options, leaving out the "<"/">" comparators? I think I read a thread touching this topic here in the forums. See DataSourceField.setValidOperators().

Best regards,
Blama

[Isomorphic]

Client-side, the sorting is the browser built-in localeCompare and the comparison is just the JavaScript >= operator (for Strings).

Note that localeCompare may differ across locales on this point (we're not sure), also, similar to how greaterThan behavior differs across DBs, localeCompare has some subtle differences across browsers too.

So you really believe that users would not be surprised that if they have a column that is sorted in the ListGrid showing in sorted order "a", "A", "c", "C" and then applied a filter saying >= "a" and then only "a", "c" were displayed?

Well, we have zero other complaints over the ~9 year existence of the AdvancedCriteria system. Certainly, this would not be a reason to turn off client filtering, which has a giant impact on usability that end users definitely notice.

If you really feel a need to tackle this (we would classify this as too minor to address in most apps, with some rare exceptions), some options:

1. switch your DB to case-sensitive comparison to match the client-side

2. use Feature Sponsorship to have iGreaterThan et al operators added, or perhaps just for a feature that would allow you to make the default greaterThan operator either case insensitive or not to match your DB behavior

3. create and maintain (or submit) a JavaScript patch equivalent to the above Feature Sponsorship