Search Results Question

[scneophyte]

I have 10 records with an Amount value of $2.60; however, only seven are returned if I type “2.60” into the quick search field. All 10 are returned if I type “2.6” into the field.

The attached screenshot is of one of the records that is returned only with the latter search term.

Can someone explain this behavior?

Attachments:

You must be logged in to view attached files.

[Brendan]

Most likely because the values are indexed with 2.6 instead of 2.60. The trailing 0 is probably just because you have the Decimal Places set to 2.

[Tom Banks]

It seems to me that Brendan’s explanation might logically go further and say how to avoid this problem. I for one do not know what indexing a value means (or how to do it or not do it), and of course I am listing monetary values with two decimal places, so I would be liable to this peculiar problem. I would naturally want searches to simply work

[scneophyte]

I am no database expert but, as far as I know, indexing is a database’s means of speeding up searches.

I went back and re-entered a value that was not returned in the search as “2.60” to be sure it wasn’t being stored as 2.6 but it did not then appear in the search results for 2.60.

Any suggestion for a resolution or workaround is appreciated.

[Brendan]

There are 2 issues at play here. When the full text search index indexes things, it ignores punctuation. So the . will be ignored. So 2.6 and 2 6 will return the same results.

The second issue is that when you enter a value into a Number field, Whatever value you enter is stripped down to its basic numeric value. So 2.6 is the same numerically as 2.60. And it’s 2.6 that gets stored in the database. Now, things might be slightly different if you had imported values from a CSV file.

If you were to type 2.60 into a Text field, then the 0 is significant because it’s just part of the text value and 2.60 will be stored in the index and it’ll be found by typing either 2.6 or 2.60 or 2 60.

[scneophyte]

Now you’ve hit the nail on the head, Brendan! I went back and checked and the records with values of 2.60 that are returned by a search for 2.60 were imported from CSV and the records with values of 2.60 that are NOT returned by a search for 2.60 were entered manually.

[Mike Schwartz]

Instead of using the Quick Search feature, what happens if you specify a filter: [Amount] [is] 2.6 ? Does that find all the records?

Should the Quick Search field be recharacterized as “Quick Text Search” ?

Currently, Search Rules in the filter tool have the general structure of [selected field] [selected relationship] [entered criterion]. And the [selected relationship] portion is context-sensitive to the selected field (e.g., if the selected field is a numerical field, then the relationship pop-up contains mathematical relationships). It might be useful to expand the [selected field] pop-up to include the choice “Any Field”, with the corresponding [selected relationship] pop-up showing all possible choices.

Thanks
Mike

[Author]

Posts