What's the best way for "users" to search records?

[beetlejuice321]

I have created a simple "employee database", complete with a form for Employees to use to add/edit data accordingly.

What is the best method to allow staff to search this database in order to pull up a specific person?

Note: The form consists of only a persons name and employee information on a single page.


Is there a way to create a search box within the existing form to pull up a record? If there were more then one matching record will just the first one show based on the PK? Or is there a way to create a search form only, then allow users to see the list of matching results and select a record from there (which could open up into the employee form, etc.).

[Arineckaig]

What is the best method to allow staff to search this database in order to pull up a specific person?

I hesitate to suggest what might be the "best" method as it depends on several factors including amongst others the database engine, the size of the data source, the available indices and the complexity of your form. I would suggest, however, the "simplest" (and initially often the best) method is use of the binocular icon on the form navigation tool bar.

Alternatively the filter icon(s) on the same tool bar can be used to "filter" (as opposed to search) the form to show only the required records. The Base Help file indicates how these can be used.

[Villeroy]

The filter dialog of a grid view (including a form's grid view) can apply up to 3 criteria easily.
The form based filter tools give most freedom to the user to search for arbitrary things at any level of complexity (nested ANDs and ORs) . The designer of a form can declare some text controls as indexable (property "Filter Proposal") so it shows a list box with all entries in ascending order to pick from.
The "magic stick" filter comes handy at times.

A cascade of subforms is very easy to create. Then you pick a continent, a state, a region, a city, a quarter, a street,...

Advanced filters with optional criteria through a filtering table storing filter criteria for parameter substitution:
http://user.services.openoffice.org/en/ ... hp?id=4212
http://user.services.openoffice.org/en/ ... hp?id=7063
http://user.services.openoffice.org/en/ ... hp?id=6849 (list box and date fields)
http://user.services.openoffice.org/en/ ... hp?id=8622 (check boxes instead of list box)
http://user.services.openoffice.org/en/ ... hp?id=6442 (combo box)
http://user.services.openoffice.org/en/ ... 427#p96427 (Filtering Base form on a spreadsheet)

Countless Base macros try to do the same thing (filter by form control) more or less efficiently.

[Yumi]

I downloaded the samples and am playing around with the "Berlin Streets" Database. Trying to understand it.
Without changing anything it works fine. Then I open "Query_Filter". in edit-mode, try to save without touching anything the error is "Syntax error in SQL statement at ./dbaccess/source/core/api/SingleSelectQueryComposer.cxx:110"

Here I only delete the SQL statement in "criterion" "LIKE UPPER ( :StartsWith ) || '%'" and save the Query, no error but the Form is not working.
Back editing the "Query_Filter" and past the SQL statement back in the field "Criterion" it gives me an error "Warning at ./dbaccess/source/ui/dlg/sqlmessage.cxx:571 Like can not be used in this field".
How can that be? Have not changed anything else but this one SQL statement.

[Mountaineer]

Cross post: https://ask.libreoffice.org/t/filter-re ... ol/61570/5

[UnklDonald418]

Yumi
Even if your question seems similar, it is better to ask your question in a thread that YOU started. You can reference the other thread but don't hijack the thread someone else started.
Cross posting the same question on both the AOO and LO forums can also a sin but not all volunteers monitor both forums, so you might in some cases get different answers
Here is another link with detailed explanations of Filter/Search with Forms (leveraging SubForms)

[MINTHETNAUNG]

You could consider adding a search box to your existing form or creating a separate search form. For the search box on the existing form, you can use a simple VBA script to filter records based on the input, displaying all matches. Alternatively, a dedicated search form can be more user-friendly: include a text box for search terms and a list box to show matching records. Users can select from the list, which opens the main employee form with the selected record's details