[Example #1] Filter/Search with Forms (leveraging SubForms)

Some examples to be used directly
Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

[Example #1] Filter/Search with Forms (leveraging SubForms)

Post by DACM »

.
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...
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].
Overview:

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.
Please note that in some cases data-Forms may not allow write access to their data-source. Write access depends on the individual data-Form Content (Table, Query or SQL Command) and/or associated data-Form settings (Allow additions, Allow modifications, etc). In general, an individual data-Form based on a Table or Query based on a single table, can be write-enabled. Otherwise, multi-table Queries produce read-only data-Forms, in general. There are exceptions. For instance, you might achieve reliable write-through operations on a multi-Table query on a case-by-case basis. But in all cases, queries that require 'Direct-SQL' will always be read-only, and may even disable SubForm function all-together (involving SQL aggregation...?). And just to be clear, data-Forms are a sub-component of the displayed Form, so parts of the Form may be write-enabled while other parts are read-only, as determined by the individual data-Forms that make-up the visible Form.

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:
List Box filter with 'refresh' Push-Button (without macros):

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.
    Scenario 2: Otherwise, if you'll be saving a permanent record of the List Box selection to the MainForm table as part of a new or modified record, then you can skip the filter-table/record technique altogether. NOTE: It is possible to combine these scenarios when filtering a series of cascading List Boxes while saving a permanent record of only the last SubForm/List Box selection in the series.
We'll be employing the second scenario in our case, because we're using a single List Box to input a new record directly into the "Dates" table -- as necessary to record a client visit at check-in. So in this case, the MainForm will be based on the "Dates" table, rather than a filter-table. We'll place the List Box on the MainForm for client selection. The SubForm will be based on the "Dates" Table as well, and linked to the MainForm by the "Client_ID". This link effectively filters the SubForm as necessary to show only the selected client's history. And finally, since List Box selections don't automatically update linked SubForms, we'll use a manual Push-Button assigned to 'Refresh' the SubForm. This 'refresh' push-button will automatically save the List Box selection to the "Dates" table, and update the contents of the SubForm.

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 Buttons
     Edit: Attachments
    FoodPantry3.odb
    example Forms with client check-in by date and visit history
    (48.8 KiB) Downloaded 5876 times
     
The following example utilizes Form-techniques mentioned above (Scenario 1) to track student attendance...
  • 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 SubForms
     Edit: Attachments
    Students5 - attendance tracking.odb
    example filter-table and SubForms with attendance input & history
    (49.35 KiB) Downloaded 4635 times
     
See also:
Eyecare (list box refresh subform).odb
example of two List Boxes in Scenario 2 (above) demonstrating various Subform refresh techniques
(35.69 KiB) Downloaded 2318 times
Employee clock-in
...
Last edited by DACM on Mon Mar 23, 2015 7:16 pm, edited 134 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

[Example #2] Filter/Search with Forms (leveraging SubForms)

Post by DACM »

This example demonstrates a series of cascading List Box filters. "Cascading" here refers an ordered sequence of List Boxes, where each selection effectively filters (narrows) the contents of related list boxes in sequence. SubForms are used at each stage to trigger List Content filtering, but note that SubForm links are not sufficient to filter List Box Content (discussed in detail below). No macros are necessary, although one example-form is macro-enhanced to eliminate the Push Button(s) otherwise necessary to initiate List-Content updating. Cascading Text Box filters are also demonstrated.

List Box content filtering vs. built-in SubForm filtering:
  • While this example is ultimately focused on filtering successive List Box content, it should be noted that List Boxes are not the natural choice in this role in Base. That's because List Boxes require a more complex design than Text Boxes or Table Controls (grids) in this same role. Since the latter simply reflect the underlying SubForm table/query data, SubForm linking is sufficient to filter a series of cascading Text Boxes or Table Controls. Unfortunately, this built-in filtering mechanism doesn't work for List Boxes because the List Content is not derived from the SubForm (by design). Therefore we must create our own mechanism to filter List Box content by means of a dedicated Filter table/record. We still utilize SubForms with List Boxes, but only to host the necessary Push Buttons used to refresh the List Content at each stage (as necessary without macros). We'll examine this cascading List Box design in much more detail, but let's first consider the merits of the easier alternative-method which simply leverages the built-in SubForm filtering mechanism in Base.
Built-in SubForm filtering:
  • As you may know, Base Forms perform filtering by design through SubForm links. Under-the-hood, these links act as ad-hoc WHERE clauses in SQL, whereby SubForm content is filtered based on the current record-selection in the parent-Form. The linked-fields are setup in a master/slave relationship between the MainForm and SubForm (WHERE Main.Contact_ID = SubForm.Contact_ID), such that only records matching the parent-Form link-field(s) are included in the SubForm record-set. The nice thing about this built-in design is the automation. SubForm refresh is triggered automatically by record-pointer change in the parent-Form, thereby updating the contents of all successive SubForms. This, in turn, updates the contents of all associated Text Boxes and Table Controls.

    The choice between a Text Box or Table Control is straightforward. A Text Box is cleaner and perhaps more intuitive than a Table Control since the designer can leverage the record-navigation toolbar or employ a couple of arrow-buttons for user-selection at each stage. However, this sequential record navigation process is rather inefficient for selecting filter-criteria, so don't overlook the additional merits of a Table Control (grid) in this role. Table Controls allow random record selection using simple point-and-click, much like a List Box. This action likewise triggers SubForm refresh since the record-pointer is changed with each record selection. Due to the automation and ease of the design, Table Controls become the natural choice in this filtering role with Base (example). In fact, the Base Form wizard can be used to build a MainForm with filtered SubForm complete with a Table Control (grid) or with Text/Numeric Boxes for filtering purposes. Additional filter-stages can be added manually, as necessary, following this basic design formula. Note that linked SubForms cannot be chained indefinitely due to Base or SQL engine limits. In any case, you can mix and match these Controls (among Text/Numeric Boxes and Table Controls) while leveraging built-in SubForm filtering in support of successive filtering.
List Box content filtering:
  • Now, if you find the sequential selection process with Text/Numeric Controls to be inefficient (as with longer lists), and you also find Table Controls to be unsightly or inefficient in terms of screen real estate, then it's time to consider cascading List Boxes. List Boxes additionally support an auto-complete feature which is particularly helpful with longer lists; simply begin typing in the list box and the list will jump/auto-scroll to related entries (requires sorted lists). The decision to utilize List Boxes in this role is not without some consideration and resolve because cascading List Boxes involve more advanced Form-design concepts.

    As we consider the steps necessary to filter List Box content, it should be well-understood that List Boxes are not populated by the underlying Main/SubForm content. Therefore, filtering the List Content is not a matter of SubForm linking. Instead, we employ SQL and a dedicated Filter table/record. The Filter table/record is used to capture each user-selection in the sequence. The user-selections are collectively stored in separate fields, one field per List Box. These fields will normally be part of a single record within our Filter table. This fixed/known record allows us to use SQL to derive/filter the List Content for each successive List Box in the series; hence cascading List Boxes. So it becomes a matter of writing an SQL command that filters the List Content based on values stored in the Filter table/record, within specific fields (Criteria1, Criteria2, etc.), given a known record ID (ID=0).

    The only remaining issue is the lack of automation with respect to refreshing subsequent List Box content. As mentioned previously, List Box selections do not trigger SubForm refresh automatically because there's no record-pointer change involved. So we need another way to force List Content updates after each user-selection. Perhaps fittingly, we can employ SubForms to host Push Buttons utilizing the built-in 'Form refresh' button action. If we also place our List Boxes on these SubForms (one each), then we can effectively update the List Content by pushing a button to refresh all subsequent SubForms. A 'refresh' button on a Form performs two actions: (1) it effectively saves the List Box selection to the Filter table/record, and (2) it refreshes subsequent SubForms including associated List Box content (re-running the List Content SQL). Experimentally, we find that it's best to place the 'refresh' Push Button on the subsequent SubForm, in order to avoid an extraneous 'confirmation' popup dialog. Ultimately, we can eliminate the Push Buttons with a simple macro.

    Notes and limitations:
    • (1) SubForms involved in this filtering process must be bound to the Filter table/record in order to store the List Box selection to a known record at each stage. The notable exception is the final List Box (SubForm) in the series, which is normally bound to a different (target) table as needed. This is often sufficient since filtering implies data-relationships, such that the selections used in the filtering process can be inferred/derived based solely on the final user-selection, as needed.
      (2) Having said that, it is possible to save all Filter-table criteria to the target if we employ SubForm links, but only when creating a new record. Basically, when a new record is created, SubForm-linked fields will automatically populate their respective 'slave' fields in the target table with the corresponding 'master' link values found in the Filter table. Unfortunately, this macro-free record-cloning technique cannot be used to save filter criteria to existing records in the target table. Only the final List Box selection is saved when modifying existing records of the target table, so saving Filter table criteria to the target table in this manner has limited utility. Thus, record-cloning most often involves procedural code in the form of a cloning trigger or macro. But again, saving a permanent record of the Filter-table criteria is rarely necessary.
      (3) This macro-free cascading List Box technique should not be applied to List Box columns within a Table Control. Cascading List Boxes within a Table Control must be appropriately macro-filtered.
      (4) And one final note, since we're not utilizing built-in SubForm filtering mechanisms, the SubForm links (master/slave) may remain empty/disabled. Therefore, we're not limited by Base/Writer in terms of the number of filtering stages; [AFAIK] we can have an unlimited number of SubForms with this cascading List Box design.
    In summary, when employing a series of filtered List Boxes without macros, the primary filtering mechanism is the SQL used to derive the List Content of each List Box. The SQL must reference a known record (ID=0) in this case, so we save the filter criteria to a dedicated Filter table/record as necessary at each stage. Again, each List Box is bound to a separate field/column in a single-record 'Filter' Table. SubForms serve only a secondary role in this case, mainly to host the 'refresh' buttons necessary to update the contents of each successive List Box (without macros). We can chain SubForms indefinitely in this role...I presume...because the SubForms don't even need to be linked! Only the final List Box (SubForm) can be used to save permanent data to a table other than the Filter table (unless we resort to macros ... example). But when creating new records we can utilize SubForm links to transfer/clone data from the Filter table to the final table if necessary (beyond the scope of this discussion).
The SQL used to derive the List Content for cascading List Boxes:
  • Before we begin, consider that a List Box 'bound field' is typically used to transfer "keys" (ID fields; primary keys as foreign keys) in relational database designs. The bound-field can also be setup setup to simply save the displayed text, as applicable to a flat-file database (bound field = 0). For filtering purposes, we can choose either method, as we're simply saving the selection to a Filter table/record. This example implements the 'key' method (as opposed to saving the selected text) in order to demonstrate a typical design based on a properly 'normalized' database structure.

    And finally, the SQL necessary to filter successive List/Combo Boxes is non-trivial. But a couple of example-scenarios (below) should suffice. The SQL command used to populate 'List content' in a cascaded List Box will include nested SQL queries (parenthetical SELECT statements which read the known Filter-table/record; WHERE Filter.ID = 0). There's a couple of scenarios to consider based on the data (relationships) used to derive the List Box content. In most cases, the SQL for successive List Boxes remains similar.

    When deriving the List Box content from one-to-many relationships, the SQL logic goes something like this:
    • List Box 1:
      • select Criteria1
        contains no nested SQL
      List Box 2 (cascaded):
      • filter Criteria2
        WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "Filter"."ID" = 0 )
      List Box 3 (cascaded):
      • filter Criteria3
        WHERE "Criteria2" = ( SELECT "Criteria2" FROM "Filter" WHERE "Filter"."ID" = 0 )
      List Box 4 (cascaded):
      • filter Criteria4
        WHERE "Criteria3" = ( SELECT "Criteria3" FROM "Filter" WHERE "Filter"."ID" = 0 )
      ...and so on.
    With some designs (involving many-to-many relationships), these WHERE clauses may be repeated collectively at each stage (to populate the List Content) using AND-operators as follows:
    • List Box 1:
      • select Criteria1
        contains no nested SQL
      List Box 2 (cascaded):
      • filter Criteria2
        WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "Filter"."ID" = 0 )
      List Box 3 (cascaded):
      • filter Criteria3
        WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "Filter"."ID" = 0 )
        AND "Criteria2" = ( SELECT "Criteria2" FROM "Filter" WHERE "Filter"."ID" = 0 )
      List Box 4 (cascaded):
      • filter Criteria4
        WHERE "Criteria1" = ( SELECT "Criteria1" FROM "Filter" WHERE "Filter"."ID" = 0 )
        AND "Criteria2" = ( SELECT "Criteria2" FROM "Filter" WHERE "Filter"."ID" = 0 )
        AND "Criteria3" = ( SELECT "Criteria3" FROM "Filter" WHERE "Filter"."ID" = 0 )
      ...and so on.
The following example lends itself to the embedded 'filter-record' technique -- mentioned in the previous post [Example #1] (Scenario 1) and quoted below, due to the flat-file nature of the source Table. In other words, the following example utilizes a dedicated Filter record within a flat-file database table, as opposed to the more common, dedicated Filter table.
List Box client selection: (when using a List Box selection to filter a SubForm)

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 in this permanent filtering role because the built-in options (filter/sort) are subject to modification and elimination through end-user Nav-bar functions.
This example demonstrates cascading List Boxes including the necessary SubForms, 'refresh' buttons, nested-SQL, and filter-record. Please note that the number of "annoying" refresh-buttons grows with the number of cascaded List Boxes, because each SubForm requires dedicated "refresh" action. Otherwise, it is necessary to use Macros to eliminate the 'refresh' Push Button(s) with List-Box filters. Recall that simple 'Text-Box filters' don't require these design-complexities ('refresh' buttons, nested-SQL or macros), and may work best in this particular case -- given the relatively few 'Years' and 'Classes' involved in the selection process.

See what you think:

The following example includes several Forms...
button-driven (no macros)
. . . Students (2 list-box filters) = a Form using List-Boxes as filters with the requisite 'refresh' buttons
. . . Students (2 text-box filters) = a Form using Text-Boxes with dedicated 'Nav' buttons to invoke automatic subform filtering
. . . Students (3 text-box filters) = a Form using Text-Boxes with dedicated 'Nav' buttons to invoke automatic subform filtering
macro-driven
. . . Students (2 list-box filters) = a Form using List-Boxes as filters, plus macros to eliminate the need for 'refresh' buttons
. . . Students (group list filter) = a Form using grouped List Box content as a filter, plus 'refresh' macros only
 Edit: ...
version 2: eliminates extraneous popups by moving list-box 'refresh' buttons to their respective SubForms
version 3: see this post for another example Form derived from this example that groups List Box contents for filtering purposes
more examples:
  • This example demonstrates one List Box used to filter many List Boxes in a production-ready 'split HSQL 2.3 database' configuration.
    This example takes advantage of one-to-many (1:n) relationships between cascading List Boxes to save only the last List Box user-selection to the target table, while using a SubForm and SQL to derive the related user-selections.
    This example also takes advantage of the one-to-many relationships between List Content, but with a rather elaborate derivation of contact information.
    Yet another example.
 
Attachments
Students2.odb
Cascading SubForm filtering using a filter-record in a flat-file database table
(69.13 KiB) Downloaded 4147 times
Last edited by DACM on Thu Feb 05, 2015 11:07 pm, edited 75 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

[Example #3] Filter/Search with Forms (leveraging SubForms)

Post by DACM »

The Base Form wizard can produce a single MainForm and linked SubForm, complete with
basic Controls leveraging the Nav-bar for sequential record (filter) selection,

or an optional
Table Grid for random, point-&-click record (filter) selection.

Likewise,
List/Combo Boxes can replace basic Controls to select among fixed user-inputs featuring auto-complete/search (keyboard) or scrolling drop-down (mouse) record (filter) selection option.

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)
The previous examples in this series cover the user-input options mentioned above, with the notable exception of the very last option. That is, searching/filtering by one-or-more variable user-inputs from basic Form-Controls (such as Text Boxes, Combo Boxes or checkboxes). This requires a SubForm based on a Query or SQL command.

Forms based on SQL queries are powerful search tools. In this case, a Form based on a 'parameter query' can search on multiple user-inputs with varied AND/OR logic...all without Macros. Base plays a major role here because 'parameter queries' are typically a front-end function, where run-time user-inputs are best handled. Base supports parameter queries with a popup input box or with Form Control inputs leveraging SubForm links, depending on the implementation. In this example, we will be using a Text Box(es) on a Form to receive user-input for a parameter query, so Base will intelligently forgo the popup input box. I must admit, given the sparse Base-documentation on the necessary tools (support for parameter-queries through SubForm links), I found myself entirely indebted to Arineckaig's and Villeroy's examples for the ability to produce this example. The inter-dependent Form, Table and SQL structures combined with extensive SQL-logic, caused me to pause and consider whether a Macro-function library would provide a better start for the average Base Form-designer. But I think the SubForm-query approach is superior because the complexities are boilerplate. That is, the complex SQL-logic and parameter-query setup are preset in these examples, so we can simply drag-&-drop an example Form (and the associated "Filter" table) to another Base application. Then to adapt one of these example Forms to search a specific Table, simply replace the references to the "Table" name and the associated "Field" names ("T1", "T2", etc.) in the Query Designer (shown below). If...and I do mean if...you also need to tweak the SQL-logic, the Query Design tool in Base provides a superb visual representation of various 'AND/OR' criterion.

And that's it! 8-)

The following details can help you reverse-engineer the provided "building block" examples. But that's not always necessary when simply adapting these examples for your own use -- as mentioned previously. So consider yourself warned, because what follows is not intuitive or applicable beyond the quirky design of Base. It's provided for the curious and for those interested in modifying the SQL logic. Otherwise, just skip to the example download file.
Base Query Designer showing the SubForm query which receives input in-directly from two Text boxes.
Base Query Designer showing the SubForm query which receives input in-directly from two Text boxes.
Note: P1 and P2 (above) are variables corresponding to SubForm 'slave' links (shown below)...which receive their values from the corresponding 'Master Form' links (F1 and F2 as shown below)...which are direct links to those fields (F1 and F2) in the Master-Form's Table (a dedicated Filter-table in this case)...which receive their values from two Text-Boxes on the MainForm...which are used for user-input. In this roundabout way, two user-inputs are acquired and used to search Table fields T1 and T2 respectively through the parameter query shown above. Clear as mud?
Using the Form Navigator to setup the SubForm.
Using the Form Navigator to setup the SubForm.
Note: if you click the pull-down for the SubForm 'slave' links you'll see T1, T2, etc. Those direct links are insufficient in this case, so we type-in the variables P1 and P2 manually as necessary to correlate to the variables named in the SubForm's parameter-query as shown in the "Field" section of the Query Designer (above).

How it works...

Base is designed to recognize variables ("parameters" in SQL-speak) within a parameter-query. This feature is associated with- and limited by- the capabilities of the Base query "parser." Base then provides a popup Input Box to accept user-input for each variable upon running the query. Evidently, Base is also designed to accept those inputs directly from SubForm 'slave' fields when the SubForm is based on a parameter query.

Before we get into the details of setting up a SubForm in this fashion, lets examine related options. After all, we could setup a SubForm with a normal query and receive our search-inputs directly from the parent-Form through normal master/slave linking. This design could certainly be used to search a Table using Text-Box inputs (or other inputs such as Date inputs complete with drop-down calendar). This relatively simple design likewise requires a dedicated filter-table/record to serve as a storage mechanism for search inputs in the MainForm, just like our parameter query approach. When we use a filter-table in this role, the SubForm query must query both the filter-table and the Table to be searched. As you may know, queries based on multiple Tables in Base generally produce a read-only result-set (a Base limitation...with some recent caveats). We could implement a dedicated filter-record within the search Table (as outlined in the Students.odb example above) in order to achieve a writable result-set since this could allow a single-Table query, but embedded filter-records are a little un-orthodox and may not be an option in all cases. So in light of those considerations, lets press-on with an advanced SubForm design, where a parameter query allows us to produce a writable result-set. This is a documented- but nonetheless clever- workaround.

When linking the SubForm, the 'master links' are mapped to actual field's in the parent Form (filter-table fields: F1, F2, etc.). This is normal. But in this case, the corresponding 'slave links' are mapped to variables, as chosen and input manually by [you] the Form designer (lets choose: P1, P2, etc.). To utilize those variables, we'll need to base the SubForm (Data-Content) on a parameter-query. This type of query allows us to map those variables to actual fields in yet another Table (T1, T2, etc.). So in summary, we've taken inputs from the parent-Form's Table, mapped them to variables using SubForm links, and then re-used those same variables in the SubForm's parameter-query to search fields in another Table. This indiect-linking technique allows us to achieve a writable result-set in the SubForm, since the parameter-query can be based on a single Table.

You'll also notice that the variables (P1, P2, etc.) are nested in SQL 'LIKE' commands. This allows us to filter the SubForm on partial user-inputs using SQL. Likewise, we utilize the 'UPPER' command to eliminate case-sensitivity throughout the query. And to ignore a blank user-input, the variables are additionally referenced in 'OR' [WHERE] clauses. All these features are jammed into a single SQL-command, so the logic can become extensive as the number of input/search-fields grows.

As you can see, this is a rather advanced Form-design. The use of SubForms, link-variables, parameter-queries, SQL-LIKE/UPPER commands, SQL-AND/OR logic, and a dedicated Filter-Table complicates the Form-design significantly. But the hard work has already been done. For the most part, everything is preset in the examples such that adapting the designs to search a different Table is trivial. So given the available examples, which are preset for up to four (4) inputs per Form (or take a look at this version with six (6) inputs), this non-macro approach should be a good option for programmers and non-programmers alike. Perhaps start by dragging-and-dropping one of the example Forms and the associated "Filter" table to your own Base application. If you're unclear on the concept of a Filter-table, perhaps reference the first post above [Example #1 > Scenario 1] for more in-depth info on 'filter table' usage.

Note: this example uses a BOOLEAN data-type as the primary key for the dedicated Filter table. This was simply a design efficiency allowed by the HSQLDB engine included with Base. BOOLEAN keys don't auto-increment (IDENTITY), but that's not necessary or desired for most Filter table designs. In any case, MySQL engines don't accept BOOLEAN primary key's, so when using those engines select SMALLINT as the data-type for the primary key in the dedicated Filter table. For that matter, here's a nearly identical version modified to search across several fields (using CONCATENATION) from a single text-box, and using the more orthodox INTEGER data type for the primary key of the filter table.

Let's start with an example using Text Boxes to search/filter on variable user-inputs...
Attachments
LIKE_user_input_filter.odb
search & filter on up to four database fields using Text Boxes
(41.35 KiB) Downloaded 4703 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: [Example #1] Filter/Search with Forms (leveraging SubForms)

Post by dreamquartz »

Please update the 'dead' hyperlinks.
It appears that most point @ oooforum.org.
http://www.oooforum.org/forum/viewtopic ... 497#420101
http://www.oooforum.org/forum/viewtopic ... 622#437622
http://www.tomjewett.com/dbdesign/dbdes ... bclass.php
http://www.oooforum.org/forum/viewtopic ... 076#469076.

I did not check them all.

Tanks in advance for this posting,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply