Search for displayed value

Tags: #<Tag:0x00007f8b1d799e60>
#1

We recently added custom search functionality to our table using the Search Plugin, pretty similar to the demo.

One issue we noticed is that the search query only checks against the cell value, not the displayed value. This is kind of counter-intuitive, I would expect it to compare with the displayed string value.

For example if you have a numeric column with format 0,0.00 and a cell has a value of 1000 then it displays 1,000.00, however if you do a search for 1,000 you get 0 results, or if you do a search for 0.0 you also get 0 results. Only if you search for the raw source number 1000 (or a subset of that, like 100) do you get results. This is counter-intuitive to users, they expect a search to be what you see is what you search for at the least. Ideally it should search both the source value and the rendered value.

Is this a known issue? Is there a setting to search against rendered value instead of the just source value?

Next, I tried using a custom search.queryMethod. However, the same basic problem exists here: the callback only gives you each cell’s source value, not the actual displayed value. It also doesn’t give you the cell properties, so it’s basically impossible to determine what the displayed value may be.

Is there a solution?

#2

Hey Aaron,

I got one tip

Maybe it would work if you call the numbro.unformat on queryStringu in queryMethod. We should get 1000 from 1,000.00.
It would also be faster than the default search cause we unformat data only once.
https://jsfiddle.net/hyr0osq7/

We had a similar discussion about the Filtering plugin, which also doesn’t allow us to use formatting, not to tell about the custom values (td.innerHTML). It is pretty obvious that we should get what we see, so I get your point.

#3

@aleksandra_budnik That’s a great idea! I think that’ll sufficiently fix numeric searches, I like it.

However we have a similar but more complicated problem with other cells. For example in some cases we render 0 as “Invalid”, or we add some text to a number, or we conditionally render a number based on another cell in the same row, etc. Basically those values can’t be searched… I think the only way to make this work would be to create custom queryMethod for each column that applies the same formatting logic to the value as the renderer… however the queryMethod doesn’t give us the cellProperties so it’s not possible to, for example, determine how a cell is formatted that depends on a value from another cell, or if the formatting is not the same across the whole column. To do that we need to know the coordinates meta data of the cell being queried, but we only get the value.

#4

You’re right currently it is pretty limited.
Usually, when we add some option they have a minimal API and closed UI. We haven’t made any bigger changed for undoRedo plugin. You know, t is still not a registered plugin - you cannot access it via getPlugin method.
Jeremy, who’s one of our active users some time ago had a similar idea on exposing the indexes in the queryMethod but we haven’t had time/priority to schedule it.

#5

I added a PR to pass in the cellProperties to the queryMethod: https://github.com/handsontable/handsontable/pull/5980

Basically I just changed this line:

const testResult = cellQueryMethod(queryStr, cellData);

To this:

const testResult = cellQueryMethod(queryStr, cellData, cellProperties);

This will allow the query method to implement more detailed query logic. Hopefully the PR gets approved. :slight_smile:

#6

Sounds reasonable :thumbsup: it doesn’t change the default settings.

I’ve already marked the PR as ‘ready for review’ and I see you’ve created a test scenario so it should go smooth :slight_smile: