How can I have a listbox with a query linked on a field and have another master slave field?

[Genom]

I am quite familiar with typical Form/SubForm logic when working with forms. That has a strict hierarchical logic. I can also make a custom query to generate a table of ids and labels, use these in a list box to pick values/linking entries based on the relationships.

But let’s assume this simple example:
Clients table with id, name …
Addresses table with id, street, client_id …
invoices table with id, client_id, address_id, …

Now, I have clients form and addresses subform, linked with Clients.id/Addresses.client_id fields.
I also have an invoices subform under clients, linked with Clients.id/Invoices.client_id fields.
Now, for each invoice entry (in the invoice subform), I would like to pick from client addresses in listbox and use that address id in the invoice table. Here, we are breaking this strick hierarchical logic. Now, we have 2 separate relationships in our logic.

1. between clients and addresses
2. between invoices and addresses

I tried the filter under listbox, but it only accepts fixed values. For instance, I can’t use the main form (clients) id field to filter results.

I wonder, if there is any way to accomplish this without Macros?

[Villeroy]

The attached database has the following tables:
A: Addresses
C: Clients
C_A: mapping Clients to Addresses (many-to-many relation)
INV: Invoices with client-ID and client’s address-ID and date (default: today)
PROD: Products
SALES: mapping Products to Invoices ((many-to-many relation)
F: a filter table with 2 rows (ID 0 and 1) to store critera values for filters

Queries:
Listbox contents for articles, clients, products and for addresses filtered by filter record 0 and filter record 1.

Macros:
AutoRefresh macro to update listboxes and forms after updating/deleting/inserting some form’s record.

Forms:

• A setup form to map addresses to clients and enter new addresses. Address listbox is auto-refreshed when new address has been added.
• A form to enter new invoices quickly. First you enter a client in record 0 of the filter table, hit Enter, then you pick a client’s address, hit enter and add sold products. Auto-refresh macro updates addresses when client has been entered.
• A form to search and edit existing invoices. First you enter a client in record 1 of the filter table, hit Enter, then you select a client’s invoice. Auto-refresh macro updates addresses when client has been entered.
  clients_addresses_invoices.odb (35.9 KB)

[Genom]

Where are the macros stored? I can’t seem to find them.

[Villeroy]

Tools>Macros>Organize… Python
https://extensions.libreoffice.org/en/extensions/show/apso-alternative-script-organizer-for-python
AutoRefresh “home page”: Apache OpenOffice Community Forum - [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes - (View topic)

[Genom]

Thanks but LO also has a built in Macro organizer, which does not display any Macros.

(Same under dialogs or Edit Macros…) I am looking into your Python post now… Thanks!

[Wanderer]

@Genom

but LO also has a built in Macro organizer, which does not display any Macros.

because that will only show Macros written in Basic. So trust the author and use APSO…

[Villeroy]

You can use your prefered IDE or text editor with Python. My AutoRefresh macro is a tiny pice of code (725 byte).

In order to use it with any form, extract it from the zip archive (office documents are zipped) into your user profile, subdirectory Scripts/python/. Then you find it under “My Macros” in the merged macro hierarchy where Basic, Python and macros of other languages are merged.

You don’t have to understand nor edit that macro. It is configured through a hidden field named “AutoRefresh” where you enter one or many form items to be refreshed. You enter these items semicolon-separated by their hierarchical names according to the form navigator. …/ refers to the parent item. The root node is the collection of forms as shown in the form navigator.
The macro is triggered by a form’s “Action” event (insert, update, delete record). It reads the hidden AutoRefresh value, splits that value by semicolon, identifies and refreshes the specified items one by one.