Transferring fields between forms

[marus_b]

Hello all,

I am trying to completely understand the example of transferring information between a form and a subform shown in the following example:

https://forum.openoffice.org/en/forum/viewtopic.php?t=56006

Can anyone point me to more information pertaining to this matter? Possibly even more simplistic examples? Even though I was able to completely recreate the entire database and form structure, I still cannot wrap my head around how this feature “works under the hood.”

Furthermore, is this technique applied relatively often, or are macros the preferred approach?

[Ratslinger]

Hello,
Take a look at the Base documentation here → Documentation/Publications - The Document Foundation Wiki
.
Chapter 4 should be of help.
.
Macros take a lot of time and effort to understand and use. Avoid wherever possible.

[Villeroy]

A simpler sample with a one-to-many relation between persons and animals and a many-to-many relation between persons and tools.

• 3 lists for persons, animals and things. Each list has a name and an auto-id (a technical row number)

• The many-to-many relation requires another table “P_T” mapping tools to persons.
  This is the database as shown in the relations window:
  

• Each ID number identifies one unique animal, person or thing. This primary key is the 1-side of a relation.

• The n-side specifies that each person’s Animal-ID needs to exist in the animals table. A person with a non-existing animal-ID is rejected. This Animal-ID on the n-side is a foreign key.

• Each item in the mapping table “P_T” is specified by the unique combination of a Person-ID and a Tool-ID. Each of the Person-IDs needs to exist in the persons table. Each of the tool-IDs needs to exist in the tools table. The table stores nothing more than which tools belong to which persons.

The forms I have added do not show a single ID number while all the functionality is based on these numbers.
Each list box shows some item’s name but stores the corresponding ID number in the foreign key field of some other table. A listbox of animals stores the one animal-ID that belongs to a person. The persons form, for instance has persons as a main form showing the person’s animal-ID represented by the correspinding animal name in a listbox. You switch the person’s animal-ID by selecting another animal name from the listbox. The current person’s tools are shown in a second subform reflecting the many-to-many relation.
There is only one design pattern which is able to reflect a many-to-many relation in a Base form. A many-to-many relation requires as many listboxes as there are tools belonging to a person or owners belonging to a tool. This is what a table control can do. A table control is not bound to any table. The table control’s form is. A table control is a bundle of form controls showing one instance of this bundle per row. In this simple case each row in a table control shows one column of listboxes for a person’s tools or for a tool’s owners respectively.

Invoice_Forms_v2.odb by DACM follows the exact same principles although he did not specify the relations explicitly in the relations window.

[marus_b]

Thank you @Ratslinger , I am going through that manual already, but I did not find an answer yet, specifically to the question I intended to pose.

@Villeroy I greatly appreciate you taking the time to explain all this. I do understand that portion entirely. What I was referring to is the functionality of the command buttons on DACM’s form. I’m going to try to elaborate on his “Invoice Basic” form, so that I can pose my original question properly:

If you look at the form tree structure is has the following makeup:

Main form: (table): Invoice

• Subform1: (table): Sold
  • Item (listbox): (query): → filters out items which have not been sold yet.
  • (2. Next Item): Push Button (action: refresh form)
• Subform2: (join tables): Sold + Items
  • (Grid): listing information deriving from the Subform2 join.
  • (1. Add item to Invoice): Push Button (action: refresh form)
• Subform3: (querry): total of a given invoice (not really relevant to my question)

My question pertains to the push button functionality:

1. How is it possible to select the item from the “Item” listbox and by pressing the “1. Add Item to Invoice” have it shown in the Subform2 grid (which ultimately has to be subjected to the Subform2 join criteria filter).
2. By pressing the “2. Next Item” the Item listbox gets reset and not showing the selected item anylonger.

As DACM stated in this tutorial, one would lean towards a macro solution (myself included), as it seems to be a more pragmatic and comprehensible approach, than what he is showing. Yet, it is very intriguing that all that functionality exists, and with little time that I have available to commit to learning Base, I’d truly like to grasp the idea behind it.

[Villeroy]

I forgot to link the database: https://forum.openoffice.org/en/forum/download/file.php?id=11250

[marus_b]

@Villeroy no worries, I searched for the filename, and quickly found the forum entry with it :).

[Villeroy]

@marus_b

Yet, it is very intriguing that all that functionality exists,

Base forms provide the bare minimum of functionality for editing simple data types in related tables.

1. An arbitrary large hierarchy of subforms allows editing records from many related record sets. Subforms can be linked by master and slave fields and by master fields and slave parameters of a param query.
2. A listbox writes to a foreign key by picking an associated text.
3. A table control bundles one or many bound column controls (text boxes, listboxes, date controls etc.) showing one instance of each bundle per record. The table control itself is not bound to any record set.
4. Other input controls are input helpers for booleans, currencies, dates, times, text.
5. Push buttons can be associated with form actiions and/or macros or URLs including UNO-URLs (e.g. “.uno:CloseDoc”).

With these chess pieces one can build fairly functional input forms without writing a single line of macro code, given that you operate on a well made, properly normalized database.

The most important design tool is the form navigator. It displays the hierarchy of forms, subforms and form controls. Unfortunately, it does not include the column controls of a table control. It allows drag&drop operations to manipulate the hierarchy.

@marus_b

How is it possible to select the item from the “Item” listbox and by pressing the “1. Add Item to Invoice” have it shown in the Subform2 grid (which ultimately has to be subjected to the Subform2 join criteria filter).

• The form navigator reveals that the yellow controls belong to three subforms. When you select a control, the form navigator highlights the corresponding item in the hierarchy.
• Two subforms are bound to the same table SOLD. Table SOLD is the mapping table between invoices and sold items. It stores item IDs with invoice IDs. Both subforms inherit their INVOICE_ID from their blue master form (master: INVOICE.ID → slaves: SOLD.INVOICE_ID).
• The listboxes to select a sold item are bound to a subform that is set to “Add data only”. A form with this property set always loads a new record. The invoice ID inherits from the parent form. This subform is misleadingly named “Subform_Customer”. Just rename it if you bother. And two of the labels belong to the wrong form which does not affect the funtionality. Drag them into this subform if you bother.
• The table control displays the sold items filtered by the parent form’s invoice ID.
• Button #1 (add item) is set up to refresh the form it belongs to. It belongs to the second subform with the table grid.
  Now you select an item from either listbox into a new record. Then you click that button which takes away the focus from the new record form. Taking away the focus stores modified form content to the new record before the button’s refresh action updates the form with the table grid.
• Button #2 (next item) belongs to the “new record form” and refreshes it (reloads into the next new record). The button action “new record” would work as well in this case.
• Button #3 (get total) belongs to “Subform_SubTotal” and refreshes that one recalculating all the values of various sub-subforms. It is filtered by the same invoice ID from the same master form. It’s sub-subforms are filtered in the same way.

[Villeroy]

And this is the design scheme of the database after adding and connecting all the tables in the relation design window:

Basically, we have a many-to-many relation between items and invoices mapped by table “SOLD” plus various one-to-many relations about shipment adress, invoice addreess, taxes, shippers, discounts.
SOLD uses its own auto-ID as primary key where my “relations2listboxes.odb” makes use of a combined primary key.
The advantages of setting up relations:

1. It is impossible to edit any value on the n-side that does not exist on the 1-side. As long as you use subforms and listboxes, this is not a problem because these tools can make sure that you always use valid items. Without set up relations it is possible to enter an invoice for customer 999 directly into the table. The invoice form would show an invoice with no customer.
2. Every foreign key is indexed which improves the lookup performance.

Disatvantage:
Once these rules are set up, it can be difficult to modify the tables. You may prefer demo databases and drafts without enforced relations.

[marus_b]

@Villeroy I just noticed your post, but at the moment I do not have the time to carefully go through it. Nonetheless, thank you for that. I did come up with a properly functioning form, which functions just like DACM’s (please see attached), and I’m still analyzing it trying to understand how this all works. I kept it to the very minimum, in terms of not applying any relationship, nor macro automation, or any error/workflow characteristic. Just simple database tables, a form and a few queries behind it. From now on, I’ll be referring to the nomenclature in my example, instead of DACM’s as it logically makes more sense to me.

I came to the conclusion that DACM very cleverly used the form property called “add data only” in the form which holds the listbox (in my case “ReservedItems”), in conjunction with an appropriate form structure. Unfortunately, I am still doing this almost on a “monkey sees, monkey does” principle. But I believe your explanation above will point me further in my goal of understanding the semantics behind it.

There is a very short explanation in the BaseGuide ver. 7.3 (which @Ratslinger pointed me to) on page 173, where it specifically states “If Allow additions and add data only are selected, data can be added to a field. But once the data has been saved, it is no longer visible and the data has been written to the tables.” I’m under the impression that this is precisely the aspect of this property which is utilized in this case. Although, for a layman like myself, it would be rather difficult to come up with such complex design, given such short annotation.

test_tfc_2.odb (13.7 KB)

[Villeroy]

@marus_b

But once the data has been saved, it is no longer visible and the data has been written to the tables.”

Not exactly right. You may navigate back to this session’s newly added records. However, when the form is loaded, it does not load any existing records. It jumps directly to the new, empty record which is normally displayed below the last record.

[Villeroy]

@marus_b

I just noticed your post, but at the moment I do not have the time to carefully go through it.

Well, I tried to outline 90% of the functionality in a text as short as possible.

[marus_b]

@Villeroy I wanted to thank you for taking the time to go into such detail with the explanation above. I finally had some time to go over everything today, and it all makes perfect sense now. I definitely need practice to make sure it sinks in, but on a logical level it is definitely more comprehensible than it was a few days ago :).

[marus_b]

I meant to do this a few days ago already, but never got around to it. Now that I understand the logic behind the dependencies of these forms, I put together an automated version of it. Maybe someone down the line can use this for their future projects.

This example is based on an assumption that there is only one set of items available. There is no multiple quantity criteria implemented, although that can be easily changed.

Also, if there is a more eloquent manner of doing this, I’d love to see an alternative.

sample_itemSelection_macro_01.odb (16.2 KB)

[Villeroy]

@marus_b

sample_itemSelection_macro_01.odb

Nice try. But there is no back reference to the current owner. The list of available items shrinks as you add items to the current owner, but after switching to the next owner with no items yet, the list remains the same. The macro turns a many-to-many relation into a one-to-many relation where each item belongs to one owner.

P.S. There seems to be no way to access the record set of a listbox, otherwise it would be possible to substitute parameters as in:

SELECT "string", "ID" FROM "Other_Table" WHERE "X_ID" = :paramX

I can not find a way to substitue paramX for a listbox.
It might be possible to implement your own service c.s.s.form.binding.ListEntrySource. I won’t invest that many hours (days) to investigate. I’m satisfied with the combined primary key raising an SQL error which can be handled with simple Basic code, if you want to handle it anyway.

[marus_b]

I truly appreciate all the pointers, as I’m still learning all this, and every bit of information is important to me. However, what do you mean when you say there is no back reference to the current owner? And also, when you say that there is no way to access the record set of a listbox?

[cpb]

@marus_b,

I have not been following this thread but took a look at your database ‘sample_itemSelection_macro_01.odb’.
.
not sure I’ve got it right but worked on the principle of not more than 1 item of type per person with every person having access to every item.
.
made some minor changes and hopefully fixed the list box problem.
I tackled some of the issues in a slightly different manner to yourself, not necessarily better or worse just differently.
.
this is your database to which I have added a copy of your form and 1 module with 3 small subs.
ItemOwnership.odb (26.8 KB)

[marus_b]

@cpb,

Yes, my principle was that there is only one set of items available to split among participating parties. Whereas yours makes one set of items available to each party. Each of those has real life application, and I should have presented that solution from the get go as well. Thus, thanks for posting it :).

PS. I’m sure that after 20 or 30 years of being a programmer it can get monotonous, but to a newbie like me this is really cool stuff. I truly regret not getting into this earlier in my life. You guys are very lucky

[Villeroy]

My bad. I got the idea too late. So we have a one-to-one relation between individual items and the item ownership which involves an owner ID. I added a quantity (default 1) and a value to the ownership.

The form looks almost the same but its structure is quite different. I dropped the whole Basic module in favour of my generic AutoRefresh macro.
Download AutoRefresh.odt to a trusted directory and click the install button.
When you add an ownership, the item will be removed from the list of availlable items.
When you delete an ownership, the item will be back in the list of availlable items.
The listbox in the table control shows all items but is disabled. You can edit quantity and value but you can not change the item. In order to replace an owned item, you have to remove it first.
The AutoRefresh macro is set up with hidden controls in both subforms (see form navigator). It is triggered by form event “After record action” (either one of delete, edit or insert).
sample_itemSelection_macro_02.odb (16.3 KB)

[marus_b]

Both of you guys came up with stuff that I need to take a closer look at, and I’m sure it’ll help plenty of other people for sure. Thank you for that. @Villeroy with your permission I’d like to come back with questions for your solution. But let me try to go over all this, and do some research first.