Searching (Simple & Advanced)
dbFront has two main options for searching: Simple and Advanced. You can view the video Searching and Filtering to see a walkthrough of searching in action.
Simple Searches
Finding data in dbFront is very straightforward because every table has a search box at the top left corner. Just type the values or keywords you are looking for and dbFront will find it. The values or keywords should be separated by spaces and can be entered in any order.
If the table that you are looking at is small enough then dbFront will load it into the browser's memory and do the filtering as you type each character. This makes the process of finding specific records very fast and intuitive.
If the table is to be too large to load into memory, dbFront will not respond until you hit the enter key in the search text box. Once you hit enter, then dbFront will send the search request to the server and return the filtered results. This is done to avoid overloading the server.
This is fairly intuitive. Start typing and if the search responds immediately then all is good. If not hit enter to send your search to the server.
Advanced Searching (optional)
In addition to the Simple Search, dbFront also allows you to run advanced searches. Below every table, you will see a collapsible panel that will show a list of fields that you can search on. Click this panel to open it and show the search fields. This list of fields can be managed or disabled by the administrator. For more details see: Search Fields
Numeric or Date fields will automatically allow you to specify a range. You can specify both the beginning and end of the range or only the beginning or end of the range.
dbFront will automatically update the search results as you add fields to the criteria. dbFront will also add a widget to the Advanced Search title bar showing what you are searching on. To remove the field you can simply click on the close button.
Default Wildcard Text Search
By default, dbFront searches for the entered text anywhere in the selected database fields. dbFront does this by surrounding all text searches with the % wildcard character and using the SQL LIKE operator. e.g. LIKE '%keyword%'.
Performance
Searching using the LIKE operator and an initial wildcard (%) causes performance issues because the database server is forced to scan the entire table to perform the search.
If the table exceeds 100,000 rows then dbFront will revert to a LIKE 'keyword%' search. Removing the initial wildcard (%) allows the database server to use an index to find all rows that start with the specified keyword.
Administrators can change the default 100,000 breakpoint. The setting FullLikeRowLimit is found in the [Help] / [Settings] dialog under the service tab.
Custom Wildcard Text Search
If dbFront spots a quote or percent (" or %) in the input or if the search string starts with "L:", then it will disable its default processing for that keyword or string and assume the user understands how to use custom wildcards.
| Wildcard character | Description | Supported Servers | Example |
|---|---|---|---|
| % | The percent matches zero or more characters | All | "Color%" matches any string that starts with "Color" including [Color, Colors, "Coloring Book", etc..] |
| _ | The underscore matches any single character or none. | All | "Colo_r" matches [color, colour, coloZr] |
| [ ] | The underscore can be replaced with the square brackets containing a list [abc] or range [a-c] of characters to match. | SQL Server | "Colo[u]r" matches [color or colour] |
| [^] | A caret symbol beginning the contents of the square brackets negates the list or range of characters to match. | SQL Server | 'dea[^d]%' matches [dear, dearest, dean, etc..] but not [dead, deadly, etc..] |
Full-Text Search
dbFront Supports Full-Text search in both the Simple and Advanced modes. Any fields that have been configured for Full-Text search will automatically be queried using the Full-Text indexes. For more details see: Full-Text Search.