Efficient multi-field search pattern for finding matching records

Question

We need to build a tool that allows data entry from people to find transactions from an external data source and match them to our transactions. The transactions don't always align by a particular field so it may require searching across multiple fields. We currently have two designs, but would appreciate any other better options.

Both are searchable tables.

The first design is a table with universal search that searches across all fields for a matching term and returns all rows that contain the term. The user can further filter by sorting Ascending or Descending on any particular row.

The second design came from the scenario where the user is searching for "car" hoping to find records where the field "type" is "car" and receives back results that include the "name" "carlos". The solution here was to allow the user to click on any header and enter a search term, which would create a tag and there would be a search performed for that specific term on that column as well as additional filtering for any terms added to other columns on those respective columns.

The counter argument to the second approach is that while theoretically more efficient because it allows the user more fine grain control, it will not actually be more efficient due to the cognitive load of the user needing to first think about which field will they need to enter the search term on and then locating the field (there are 10 columns and some need to be made unhidden due to not being visible by default for lack of on screen space) and then thinking about the next field and locating that field, now multiply that by dozens of transactions the user will need to find per day and they may get mentally fatigued faster than just relying on muscle memory and constantly going to universal search.

What would be the optimal solution, perhaps it's some option not even considered?

Answer 1

I would keep the general search field and add a dropdown menu listing all the columns next to it. By default the dropdown would be set to "in all columns".

If the user wants/ needs to refine their search, they can select one or multiple columns from the dropdown.

Answer 2

I suppose it's a very specific case, and the user knows most of the tables by name. Therefore you could create a more advanced querying system, kind of like gmail does when filtering.

Let the user for example type:

type:car

Now search for all 'car' occurrences in type.

Depending on the technical level of your users, you can extend this to:

car in type, vehicle, transport

You can even provide them with car* to find caravan somewhere in colums type, vehicle or transport.

You can go very far in this, but make shure that a default search for car without any special things keeps working.

Also make sure you don't create an extra layer of difficulty in which the user has to make shure his query is valid, not everyone will insert a collon folowed by a space. Try to hint him if he forgets a collon by validating the input: Does the column vehicle transport exists? Nope, does column vehicle, yes, does column transport, yes, than that's probably what he means.

Add some awesomeness (for a non sql knowing user) and let him type:

car in type, vehicle, transport by speed

And show the results ordered by speed. But that might be overkill in your case, evaluate the value against the cost.

Answer 3

This should support the user in the different scenarios you've mentioned

1. It provides autocomplete
2. It suggests the relevant categories (in your case types)
3. The user is not forced to use it, so he can simply use plain search if desired