This example demonstrates the use of a List Box selection to filter data in a SubForm. This has wide application. For instance, we can use a List Box to select a contact-name while using a SubForm to display the contact's details including address and photo as available. Or we can select an item/product and display the details including description and price, while also adding calculated fields such as Quantity*Price.
In this example, we'll be selecting a client using a List Box, and displaying the client's visit-history using a SubForm. This example is based on another thread which asked...
Overview:alisona5 wrote:...trying to create a database for our local food pantry...We need to be able to search for the client...continually add dates of service for the same person...and [display the client's visit history by date].
Assuming a "Clients" (contact information) table, we'll need another table to record the client-visits by date. This dedicated "Dates" table will be used to record the client-ID and date of each visit. A List Box will allow client-selection by name from the "Clients" table while storing the client-ID to the "Dates" table. A date-field with drop-down calendar will be used to select the visit date, while defaulting to the current date if left blank. Upon recording the visit, a linked SubForm will display the client's visit-history by date (in reverse-sort order; latest visit shown first).
Some Review and Considerations:
Since one client from the "Clients" table can have many visits recorded in the "Dates" table, we have what's known as a one-to-many relationship (1:n). Such relationships serve as the backbone of a relational database. Even many-to-many relationships (n:m) are implemented as multiple 1:n relationships combined through an intermediate "junction table." We can also extend/split a Table in special circumstances using a one-to-one (1:1) relationship, such as to minimize redundancy with subclasses, or to maintain performance when storing large files inside a database (such as photos, eBooks, etc).
Base Forms are optimized for data relationships using a hidden structure of MainForms & SubForms, collectively known as "data-Forms.” This sub-structure is revealed in tree-view by selecting: View > Toolbars > Form Design > Form Navigator [5th button] (see image). Each data-Form is based on its own Table, Query, or SQL command. The latter two options (Query and SQL-command) are identical, except that 'SQL-commands' are queries stored within the Form document itself, which allows export as a self-contained, standalone Writer Form. These options (Table, Query, or SQL command) allow us to build powerful Forms incorporating information across multiple Tables, for the purposes of data-entry and/or retrieval. SubForms are special data-Forms because they're linked to a parent data-Form by one or more fields in common. These links normally reflect the key fields (primary/foreign) between related Tables (such as in one-to-many [1:n] relationship), but we can also link SubForms based on the same Table as the MainForm for filtering purposes (such as with a flat-file, spreadsheet-like, table design). So the primary effect of a SubForm 'link' is to filter data, but these links can also be used to carry data into new records across Tables (effectively 'cloning' or pre-filling data to new records) as we'll examine in more detail below...
- SubForm links serve two functions:
(1) FILTERING SubForm links effectively filter the records available to the SubForm based on the current values of the linked field(s), as determined by the current record selection in the associated main/parent data-Form. This filtering function is very similar to a WHERE clause in SQL. Filtering, then, becomes foundational to 'searching.' These terms are often used synonymously in the context of Forms, but 'filtering' is the broader category and the natural function of SubForms. To build a dedicated search function, on the other hand, typically involves a dedicated filter-table (or record) which is used to store user-input (search criteria) temporarily, since storage is necessary for SubForm link function. This technique avoids the creation or destruction of critical record data in the process of establishing search criteria. Either way, whether filtering or searching, SubForms are designed to present the results through various Form Controls (Table grids, Text Boxes, Images, etc.). Hence the name of this thread: "Filter/Search with Forms (leveraging SubForms)."
(2) AUTO-FILL SubForm links can also be used to transfer the values from multiple fields between tables, as necessary, but only when creating new records. The value of the master-link field from the parent-Form table is copied automatically to the slave-link field in the SubForm's table, when creating a new record in the SubForm's table. SubForms support multiple master/slave fields (intended primarily for filtering purposes), so we can auto-fill multiple fields (example1 / example2 / example 3). This auto-fill function is quite useful in data-entry tasks across related tables, similar to the function of a List Box, but it also finds utility in record cloning tasks.
In any case, it's all transparent to the end-user, who is presented with a single, contiguous Form with various Controls (Text Boxes, List Boxes, Grids, etc.). But under-the-hood, those Controls are actually placed strategically on the underlying data-Form structure as necessary to reflect/leverage data-relationships.
Speaking of Form 'Controls,' these interactive devices are visible on the Form and offer various input/display options. We'll be employing a List Box to select our filter/search criteria, but lets take a look at the options below:
- The Base Form wizard can produce a single MainForm and linked SubForm, complete with
basic Form Controls leveraging the Nav-bar for sequential record (filter) selection,
or the same Form Wizard will generate a MainForm/SubForm combo using a
Table Grid for random, point-&-click record (filter) selection.
Likewise, we can replace basic Controls with
List/Combo Boxes to select filter-criteria from a drop-down list, featuring a type-in auto-complete/search of the list using the keyboard.
Additionally,
the Nav-bar offers powerful built-in filtering functions through various icons like the search-Binoculars and filter-Funnels.
And then there's the ability to employ
"parameter queries" which filter/search on variable user-inputs from popup Input-Boxes or from basic Form-Controls (such as Text Boxes or Combo Boxes).
Unfortunately, some Form Controls don't automatically update SubForm results through normal user-interaction. Apparently, SubForm updates are triggered only by changing the parent Form's record-pointer, or by an explicit Form 'refresh' action. This is a problem for Base List/Combo Boxes, particularly since users often prefer drop-down lists in this filtering role. So in those cases we find ourselves adding dedicated 'refresh' Push-Buttons or Macros as necessary to update the SubForm filter/results. At first glance, the Nav-bar has the necessary 'refresh' icons for this chore, but this option is un-intuitive and disorienting as the refresh also causes the Form to jump to the first record in sequence. These and other considerations are explained below and demonstrated by the attached examples.
More information:
- [Example] Arineckaig's Form filtering examples (including a definitive guide to Base SubForms)
[Example] Villeroy's Form filtering examples
[Example] Filter table
[Tutorial] Efficient Database Design
[Tutorial] Database design: Normalization
[Article] Database design: Database Denormalization
[Tutorial] From Newbie to Advocate in a one, two... three! (download)
[Tutorial] Forms in OpenOffice.org Base
[Example] Relations reflected by list boxes in forms
[Tutorial] More Base Tutorials
Lets take a closer look at using a List Box to set the filter-criteria for a SubForm. Early in the design phase, we need to decide where to save the List Box selection. This decision determines the table for our MainForm, since the List box will record inputs to the underlying Form's table. There's generally two filtering scenarios to consider: filtering with or without saving a permanent record.
- Scenario 1: When simply filtering a SubForm, we can avoid critical data destruction by saving the List Box selection to a "filter-table." A filter-table is created just like any other table, but it will usually (in a single-user environment) consists of a single, re-usable record, with a primary key field, and at least one input field. Multiple input fields are required with multiple Form Controls, but in this example we'll be using a single field and List Box. When creating a filter-table, take care to seed the primary-key field with a value or simply select "AutoValue=Yes" during table construction. Also ensure that each input field is set to the proper data-type corresponding to the intended SubForm 'slave' link field type. And decide whether you'll allow NULL entries or "Entry required" for each individual field. "Entry Required" is the equivalent of NOT NULL, and this is currently (due to a bug; table 'Entry Required' versus the broken List Box Property 'Input Required') the only way to control the presence of a 'blank' user-selection option in a List Box. And finally, utilize various Form attributes to effectively prohibit new records and record deletion, while allowing record modifications as necessary for proper filter-table function.
- Note: it's also possible to set-aside a dedicated record within an existing Table to store List Box selections for filtering purposes. This technique requires a SQL (permanent Query or SQL-command) as the basis of dependent SubForms/List-Boxes in order to limit access to the specific filter-record (ID=0). Admittedly, this type of embedded 'filter-record' could lead to real problems in Queries or Reports without the proper awareness (WHERE ID>0). So a dedicated filter-table is generally the better option. Multi-user environments can complicate filter-table/record implementation such that macro-driven filtering becomes the better option. Either way, a dedicated filter-table/record is superior to the built-in Form-based options when setting-up a permanent filtering mechanism because the built-in options (filter/sort) are subject to modification and elimination through end-user Nav-bar interaction.
We'll populate the List Box with client names from the "Clients" Table. We do this by selecting 'SQL-command' as the basis of its 'List Content.' SQL is powerful so we can optionally combine Table fields such as the First & Last name of each client for list-display purposes. At runtime, a List Box has an automated search/auto-complete feature built-in, whereby the end-user begins typing a name and the closest matches appear in the Drop-down to aid selection. Therefore, it's best to sort the List Box contents alphabetically within the SQL-command used to populate the list. And once again, the List Box displays the client list from the "Clients" table, while it actually saves the associated "Client_ID" to the "Dates" table upon user-selection. This works because the List Box is located on a MainForm based on the "Dates" table, and as such, the List Box's 'Data field' is set to "Client_ID" within the "Dates" table. Technically speaking, you're saving the ("primary key") field from your "Clients" table as a "foreign key" in your "Dates" table.
Date entry with Today as the default:
A "Date" field allows the user to input a date, complete with drop-down calendar for easy selection. And we can set the default to Today's date. Unfortunately, you can't setup the default-date feature directly in the Form editor (a considerable oversight in the current Date-field design). However, we can add this feature to the Table itself using SQL. Here's a post by Sliderule that outlines those steps (default to the current date). This is a bit of a workaround, because the Form doesn't actually show the default-date for new records until the record is saved and the Form is refreshed. So it's a little clunky without using Macros, but it works fine.
Showing client visit history:
Upon selecting a client using the MainForm List-Box, and pressing the 'refresh' Push-Button, the SubForm link will filter the SubForm based on the selected "Client_ID". We can sort the SubForm Table by descending "Date" and display the results in a Table Grid.
Eliminating the Push Buttons with Macros:
I won't get into the specifics, but a few lines of code can make your Form more professional by eliminating the Push Buttons (see the example Form below). Please note: as these examples demonstrate, even when using Macro-enhanced Forms, the role and complexity of Macros is reduced significantly as you learn to integrate the full power and potential of SQL through SubForms. It's a steep learning curve either way, but that's just the nature of building database applications, especially with Base.
For example:
The following example includes each of the Form-techniques mentioned above (Scenario 2) for tracking client visits...
- 1 Check-in (grid) = a Form using a Table Grid for client selection, as possible through the Form wizard in Base
2 Check-in (grid query) = a Form using an Input Dialog (parameter query) to filter a Grid to aid client selection
3 Check-in (list box) = a Form using a List Box and Push Buttons for client selection (as profiled above)
4 Check-in (macros) = a Form using a List Box with Macros to eliminate the 'refresh' Push ButtonsEdit: Attachments
- 1 Attendance (grid) = a Form using a Table Grid for student selection, along with multiple SubForms
2 Attendance (list box + filter table) = a Form using a List Box and filter-table to link SubFormsEdit: Attachments
...