Many to Many Relations with subform

[nathany]

Hello. I'm relatively new to OO Base but am relatively experienced in coding and database concepts. I'm trying to get a very simple "Hello World" type if scenario working in which I can manage n-relationships between an object in one table and n-objects in a 2nd table. I think I have accurately cloned the basic example which has been shared in this forum regarding movies and genres but when I am entering data into the subform and then having Base try and save the record, I get an error: "Error inserting the new record" - "Attempt to insert a null into a non-nullable column".

The basic scenario is I have a table representing batches (of beer) and a 2nd table representing kegs, where one batch may be consumed into n-kegs.
The 3rd table is the simple table to manage each relationship between a batch and a keg (possibly the same batch related to n-kegs).

I'm guessing that I am missing some basic detail someplace and will greatly appreciate any help!

Thanks very much --

Nathan

[Villeroy]

Something is wrong with the file you uploaded. I can open it but when I try to access the embedded database I get

SQL Status: S1000
Error code: -40

General error: org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException: /build/aoo/src/clean/branches/AOO34/main/package/source/xstor/xstorage.cxx:<unknown>:3177;

If you can access the database, close the whole office suite to cut off any connection to the database and try uploading again.

[DACM]

Hi nathany,

You'll need to pass the Keg_ID as well as the Batch_ID to the SubForm in order to clone the Keg_ID field to the KegBatch junction table. Just add Ked_ID to the master and slave link fields for your Keg SubForm.

...where one batch may be consumed into n-kegs.
The 3rd table is the simple table to manage each relationship between a batch and a keg (possibly the same batch related to n-kegs).

That's describing a one-to-many relationship. So I don't understand your need for a many-to-many relationship between Kegs and Batches. Do you ever fill a Keg from multiple Batches?

[nathany]

DACM - THANKS for looking at this and your reply. I think I partially understand things. I added the KegID to the subform in both the "master" and "slave" fields and sure enough I *can*
successfully enter data into the form and in-fact I DO see that the linked-relationshop record IS being created in the "KegsBatches" table.
I now realize that simply having this subform and listbox control is not enough to ALSO result in defining the "BatchID" value in the Kegs table as well.

To clarify a Keg is ONLY filled from ONE Batch, but one Batch can fill n-Kegs. I will want to ideally look at a Batch object/record and see a list of all Kegs which it has filled.

I'm guessing this is one more very basic conceptual issue on my part... I was thinking that the result of choosing a (BatchID) value in the listbox control would ALSO result
in assigning a value to this column in the Kegs table. Any more "obvious" advice for me?

VILLEROY - do you still have a problem opening the attached database to this posting?

Thanks again folks for the help!


Nathan

[nathany]

Following up my previous posting, I'm now wondering if my actual requirement is only for a 1->many set of relationships and if so, whether I need to be adjusting my approach.

To reclarify my requirements:

- Create and manage a table of "Keg" objects, each of which is filled from a particular Batch (ie related to a specific Batch object)
- Create and manage a table of "Batch" objects, each of which may be consumed by n-Keg objects (ie related to n-Keg objects)
- An individual Keg is only related to ONE Batch object
- An individual Batch is related to one or more Keg objects
- Once a Keg is filled from a specific Batch, the Keg is no longer available to be related to some other Batch
- From the Batch form I want to view the Keg(s) which are related to a specific Batch. Ideally, I would like to allow the *creation* of the relationship between a Batch and a desired Keg (remembering that a specific Keg can only be related to a Batch ONCE... ie I should not see the Keg ID as an available selection if it is already associated with the current Batch or any other Batch for that matter)
- From the Keg form, I want to view the existing Batch objects and choose the Batch to which I will be related (from which I will be filled)

- I anticipate needing a variety of queries and reports regarding Batches and their associated Kegs as well as other objects which will be related to Kegs as well

Hopefully this helps to clarify the requirement(s) and whether this scenario is in fact simply a one-to-many situation....
Thanks,

[DACM]

Okay so it's a one-to-many relationship between Batches and Kegs. Here's a quick example:

[nathany]

THANKS for this example - you've actually included examples of many interesting and useful constructs. One mystery for me though is the use of the "Filter" table which you added to the database. Can you explain the basic concept? It appears to be referenced by the '>>>' push buttons... is it actually just a dummy database because a pushbutton-subform needs to be associated to some arbitrary table?

Also, am I correct that there are no Relationships between tables in this current example you created?

Also, in the Batches form, what is the meaning of the "Save Keg" field and item? It always seems grayed-out.

One other twist I forgot to mention is 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?

This help is MUCH appreciated btw...

[DACM]

These are all good questions.

"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!

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.

...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.

...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.

[nathany]

You continue to provide LOT's of great info and food for thought - HIGHLY APPRECIATED!
I very much appreciate the discussion on the relative need to explicitly persist and manage the Batch-Keg relationships as opposed to the current transient-on-the-fly behavior provided via the use of the forms. I need to think more on whether or not this is truly a requirement. Noting that I can now see how "easy" it is to deliver the key behavior of interactively showing all Kegs related to a Batch (and I guess, doable via SQL for generating a desired Report as well?) perhaps this is not a hard requirement.

Regarding the very last question on the Kegs and their ID's, in fact, each Keg is physically marked with a serial # using a permanent label. In fact, in terms of actual workflow, I am imagining that the Keg table will be initialized up-front with basic attributes for ~100 Kegs... initially NO Kegs associated with Batches.. just having their Primary Key ID of course, then their Serial #, designation of Size (quarter, half, full size), location (will initially all be in primary storage), and clean state (all will be clean). I'm still unclear if typical usage for linking a Keg to a Batch will be via the Keg form or Batch form or both.

Perhaps to ensure you understand the basic scenario here is a higher-level recap of the use of this database:

- This database is going to be used to help with operations at a brewery.
- There will be an initial inventory of ~100 Kegs and this quantity will be stable for some time.. perhaps increasing in the future to 150 Kegs.
- Batches are filled into multiple Kegs for a single Batch. It is key to track which Keg's have been filled from which uniquely-produced Batch
- Batches are unique objects in and of themselves and will accumulate parameters as they are created related to their "processing". After Kegs are filled, the user may want to easily access a Kegs' Batches' parameters on demand.
- Kegs will be related to various Locations over their ongoing lifecycle (ie customers who temporarily use the Keg until it is returned empty to the brewery) and this needs to be easily track-able. My plan is to create a "Locations" table whose entries will be referenced by individual Keg objects.
- Kegs are uniquely serial-numbered objects with permanently affixed/stamped serial # ID's

Final question on the Keg ID - since these Kegs *already* exist with serial #'s which must be used as they currently are defined, am I correct in thinking I should simply utilize a regular attribute for this true Serial-#, distinct from the Primary Key ID which can be auto-generated?

let me know if I need to further clarify any details at this point --

[DACM]

Thank you for outlining the work-flow in greater detail. That certainly helps.

[Kegs table] Primary Key ID of course, then their Serial #, designation of Size (quarter, half, full size), location (will initially all be in primary storage), and clean state (all will be clean).

...am I correct in thinking I should simply utilize a regular attribute for this true Serial-#, distinct from the Primary Key ID which can be auto-generated?

Oh yes. That's a more typical table structure.

I'll work-up another example if that would help...?

[nathany]

DACM - *yes* I would greatly appreciate your help with another example.
Also, can you point me at any good reference or additional info/tutorial regarding the use of filters (and associated filter Table) along the lines of
the basic example you previously provided? I'm still not clear on all aspects of this core concept and "best practices".

Thanks again --

[DACM]

Thanks for your patience...I just got a moment to generate another example:

[nathany]

VERY COOL -- *Thank You* so much for this help... you have given me ALOT of interesting food for thought in terms of UI options.
It's been invaluable to get this type of "support" and advice.

Thanks again!