Help for designing and solving a typical problem including many to many connections (items with multiple authors and multiple tags) using Base

[PV28638]

Hi all.

I want to use the LibreOffice’s Base software on making a solution for the problem which I describe in the video on the following link:
https://twitter.com/PV28638/status/1655163431094198273?s=20

Still, although I have seen some basic tutorials of using LibreOffice Base, I feel that I can’t actually use it properly on making a full solution, including tables, forms, queries and reports for my problem. Having been discouraged I decided to ask for help, and I created that video in order to provide as much information I can.

Thus I would like to ask, if anyone is able to see the video, understand the issue I want to solve via LibreOffice Base, and if it indeed can be solved, to provide either links to similar solutions, either ODB files containing similar - if not the same solutions - as examples to study, or a video tutorial useful for anyone who may want to learn how to make such or a similar solution from scratch.

Thanks.

[Villeroy]

Transferring fields between forms

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 …

[UnklDonald]

If I understand your video correctly you are trying to establish many to many connections between Items and Authors and Items and Tags.

A many to many relationship requires an intersection table. A special table where each record has a link to both tables and they are combined into a compound Primary Key.

I attached a demonstration showing how the relationships would work on a Form document named Items. The Relationships diagram may help you understand how the tables fit together.

I included another form document named SQL_Cmds which contains the commands I used to create all the tables except Authors (that I copied from another demonstration).

I added three queries that group the results by Authors, Items and Tags. Using the Tags query as the data source for a report you should be able to design a report similar to what you show in your video.

Let us know if you need further clarification

Demo060LO.odb (25.0 KB)

[PV28638]

Thank you for your support and help!

Your ODB file helped me a lot to understand how to use LibreOffice Base for this and similar problems. Still I haven’t completely study it, but I’ve reach to the point where I had a question on your ODB which I recorded it in the following video:
https://twitter.com/PV28638/status/1655979656795783195?s=20

The idea is to avoid in the drop-down list the author names which have already been used. Do you know any solution on this issue?

[Villeroy]

@PV28638

Do you know any solution on this issue?

No simple solution at least. Anyway, you can not add any duplicate items because that would violate the primary key. You get an error message when you try.

The subforms inherit the item-ID from the items table and the combinations of item-ID and author/tag-ID need to be unique due to the combined primary key including both fields.

[PV28638]

I understand the reason it creates the error.
I may guess the solution is to filter out the results of the SQL query, with something like:

SELECT "FIRSTNAME" || ' ' || "LASTNAME", "Authors".* FROM "Authors" ORDER BY "LASTNAME" ASC,"FIRSTNAME" ASC WHERE "ID" NOT IN (SELECT "AuthorID" FROM "itemXauthor" WHERE "ItemID" = "---how can I get the selected itemID from the master fields---")

But then the question becomes: —how can I get the selected itemID from the master fields— ?

[Villeroy]

A listbox has no master fields, only the form has.

[PV28638]

From what I understand, I have a “List Box” inside a “Table Control” which is under a “Form”.

The “itemXAuthor” correspond to the Content of the Form, which links the “itemXAuthor.itemID” to the “items.ID”. Thus the form knows the master “items.ID” which is the same as “itemXAuthor.itemID”.

Isn’t that information available for accessing via SQL to “Table Control” and “List Box” as sub-children of the Form?

[Villeroy]

The form knows the author ID, say 2, and the listboxes know the corresponding items
2 | 6
2 | 8
2 | 3
However there is no easy way to make a listbox aware of the current author No. 2. There is no property for this. Thus you can not filter the listbox by the current selection in the parent form nor the author ID in its own form.
With a filter table storing the author ID in some temporary record, it is possible because the list box can query something like

SELECT "Name","ID" FROM "Authors", "Filter" WHERE "Filter"."ID" = 0 AND "Authors"."ID" = "Filter"."AID"

And there are macro driven solutions: [Tutorial] Cascading Listboxes with macros (hope this helps)

[UnklDonald]

Yes that error message seems rather technical for "Hey dummy you’ve already used that name, pick a different one!"

The following query will generate a list of authors that have not been used for a specified Item. But you must manually enter the ItemID value to see the results.

SELECT ( "FIRSTNAME" || ' ' || "LASTNAME" ) AS "FullName", "ID"
FROM "Authors"
WHERE "Authors"."ID" NOT IN ( SELECT "AuthorID" FROM "itemXauthor" 
                                                     WHERE "ItemID" = :Enter_ItemID )
ORDER BY "LASTNAME" ASC, "FIRSTNAME" ASC

There is no easy way for the query to get the ItemID from the form and even if you did you would discover that none of your existing selections could be displayed!

A coded macro as suggested by Villeroy is the only solution but the API is complex and quite opaque. Documentation is often difficult to understand and tutorials are rare. Macros are hard to write and easy to break.

My recommendation, just explain to the user what the error message means and leave it at that.