These are all good questions.
nathany wrote: "Filter" table...Can you explain the basic concept?
SubForms do their filtering-magic based on the value of
linked fields -- much
like the WHERE clause in SQL (although it is possible to use
variables linked indirectly through parameter queries 
). Merely navigating among records on the Parent-Form (MainForm in this case) will change the linked value(s), thereby automatically updating (re-filtering) the associated SubForm data. Navigation among Parent-Form records is sequential, but we can add a Table Control (grid) to the Parent-Form for random record selection. In fact, the Base Form wizard can create this entire design automatically, complete with Table Controls and linked (filtered) SubForm. This works well for some Form designs, typically involving shorter lists.
But for longer lists, it's preferable to use a List Box to select filter criteria. A List Box allows the user to jump to matching entries by typing within the List Box, much like an auto-complete feature. Using a List Box in this role (without macros) requires that we save the user's selection to a table (bound to the underlying Form) in order to utilize/invoke SubForm linking mechanisms. And that's not all. We must also update the SubForm somehow, because List Box interaction does not involve a record-pointer change as necessary to automatically trigger the SubForm update. No problem. We can update the SubForm manually through a Push Button using the built-in 'Refresh form' action (or
automatically with a macro). This requirement to save the List Box selection (without macros) can present a problem in some cases, because this will overwrite the current record, possibly destroying valuable data. So when creating a
dedicated search/filter function (without macros), we need to employ a dummy "Filter" table concept.
A Filter table is created just like any other table but will generally consist of a single row including a primary key field and at least one additional field to allow us to save filter criteria. Data-types should be properly set for each field in the Filter table. For instance, when saving a List Box selection to a Filter table, the target field will typically be set to INTEGER. That's because we populate the content of a List Box using an SQL Command or stored-Query. This approach allows us to display one field from the source SQL/Query, while saving another field (known as the 'bound field') to the Filter table; so using SQL/Query to populate a List Box allows us to present a human-readable list, while storing the Primary Key of the selected record to the Filter table. Since the Primary Key is typically an INTEGER data-type, we set the target field in the Filter table to INTEGER as well -- although this technique is not a hard-and-fast rule. Multiple fields may be added to a single record in a Filter table to accommodate multiple List Boxes, even across multiple Forms as necessary. Multiple rows in a Filter table are rare due to data-typing requirements -- although multiple rows could be used to de-conflict user-selections in a multi-user environment -- but this becomes an administrative liability so in multi-user scenarios it's probably better to use a macro to intercept the user-selection of an unbound List Box thereby eliminating the need for a Filter table altogether. So a Filter table is a relatively simple technique used primarily to avoid macros, but there may be some logical limits to its flexibility and use.
So we bind the Parent-Form (MainForm in this case) to a Filter table in order to save the List Box selection. We then add a Push Button [>>>] to the SubForm and assigned the 'Refresh form' action. This has the effect of both saving the List Box selection to the Filter table, and of refreshing/re-filtering the SubForm.
TIP: Placing the 'refresh' Push Button on the SubForm has proven to avoid an extraneous popup that would otherwise ask us to verify our intent to save the selected List Box value to the underlying table. Duh!
nathany wrote:Also, am I correct that there are no Relationships between tables in this current example you created?
This is a good question and
I don't think it's ever been asked or discussed in these forums...and I certainly welcome any experiences/advice here...as always!
I'm not necessarily recommending the following practices, but the discussion may provide some perspective on the utility and sequence of saving relationships "formally" to the backend database (as through
Tools > Relationships in the Base GUI).
In this example, the relationships are implied [ad hoc; defined on-the-fly] by the Forms, Queries, etc. I've simply found that I spend more time fighting with formal relationships (referential integrity) during development than it's worth; I find myself deleting those relationships to get things done at times (to delete columns, tables, establishing keys, testing in-progress forms, etc.). So as a technique, I don't save Table relationships to the backend database in the early stages of development unless it aids me in some way -- such as visualizing the database structure. It's one of the first things I examine and promptly
undo when I download an example with embedded-database, as I'm reverse-engineering the design. If I need the benefits of a relationship during development, I simply define it locally within the Control (SQL list-content within List/Combo Boxes), Form (SubForms imply 1:n relationship with their Parent-Form), Query or View at hand. These ad hock relationships may include various types of JOINs combined with other functions, so they seem more fluid and powerful than formal relationships, anyway.
Eventually, you'll want to save the actual/formal relationships between tables to the backend for various reasons. For instance, you'll probably want to enforce referential integrity
universally throughout your database, so someone interacting with a table directly won't introduce problematic data. But if your primary interaction with your database is through Forms, Queries/Views and Reports, then you'll effectively (?) achieve all of the benefits without setting-up formal relationships in the backend.
So in this "quick" example, I simply didn't get around to establishing the universal/formal relationships in the database backend (read: laziness). And as you can see, those "formalities" are scarcely needed to complete a simple design, much less required at this stage in the process.
nathany wrote:...in the Batches form, what is the meaning of the "Save Keg" [button]? It always seems grayed-out.
Well, the process I setup in this demo has you entering new Kegs by entering a "fill date" for the Keg. There were other ways to go about this, but I simply allowed the "Keg#" to be the Primary Key in the Kegs table. That also ensures that Kegs are unique. And, since we're saving the BatchID assignment to the Kegs table, the unique primary key also ensures that each Keg is assigned to one-and-only-one batch. Anyway, upon entering a new date (or modifying an old date) the 'Save Keg' button should "light up" (un-gray). It's your option if you want to press the button or simply click to another record to save the changes. When adding a new record, the KegID is
generated automatically through the key's AutoValue function. So the button is unnecessary but I didn't want to assume that changing records was an intuitive way of saving a new/modified record.
nathany wrote:...I forgot to mention...that Kegs will eventually become empty (and dirty) and returned to the prime location where they need to be tracked and NOT related to any batch. Once they become "clean" again, they should now be available to be linked to a Batch which has not yet been fully consumed by available Kegs. Do you have any thoughts on how best to enable this scenario to un-link a Keg from a Batch after it's existed for some time?
Hmmm...that is a twist. But it mostly affects the Forms, because it seams you're simply adding a few more "batches" to the equation. Perhaps add an EMPTY, a PRIME and a CLEAN "batch" to your Batches table so Kegs may be assigned to these "batches" as well, complete with date. So now you can track each Keg and the date it was filled with a selected batch of beer; the date it was returned EMPTY; the date it was sent-out to the PRIME location for cleaning; the date it was received back CLEAN; and so on (repeat).
Now the Forms will change to accommodate the twist...
But may I ask how you track/differentiate Kegs physically? Do they have a permanent imprint or other unique ID? Or is the difference semi-permanent such as a removable beer logo-cap? In other words, how do you plan to correlate your physical Kegs to the Kegs table for tracking identification purposes? This information might also change the Forms and/or Kegs table design slightly.