Report limited by list box choice?

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
est_co1
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Report limited by list box choice?

Post by est_co1 »

Hi.

So I've created a database for tying art pieces to multiple citations (one-to-many) and multiple different elements of a piece that could relate to another (many-to-many). I've got the table set up to work (Pieces -> Citations, Elements -> Element Connectors (junction back to Pieces).

Now I'd like to create a report that would allow the user to choose a piece by title via drop-down, and receive all the information about that piece (through a query, presumably), and another that would let her choose elements via drop-down and list all the pieces that contain it (again, a query).

Is this possible with oOo reports? I've looked around both Design View and the Report Builder, and can't seem to find controls, besides text boxes. Though I'm not entirely sure I'd know how to use a drop-down to control additional fields in a Form, I'd much rather a read-only solution.
oOo 3.4.1, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report limited by list box choice?

Post by Villeroy »

Example of mine: http://user.services.openoffice.org/en/ ... p?id=14591 (use the form filter and then run any of the reports)

Background info: [Example #1] Filter/Search with Forms (leveraging SubForms)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Report limited by list box choice?

Post by MTP »

Forms can be set to read-only:

Open the form in edit mode. Open the form navigator. Right click on the desired read-only form and choose "properties". Change "allow additions", "allow modifications", and "allow deletions" all to "no". Repeat for any other forms that need to be read-only. (I think you'll need at least one form to allow writing, the one with the drop-down box so the user's selection can be recorded somewhere.)

Or, instead of doing things at the form level, you can set individual controls to read-only (one of the options in the "General" tab of the control's properties).
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
est_co1
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Re: Report limited by list box choice?

Post by est_co1 »

Villeroy wrote:Example of mine: http://user.services.openoffice.org/en/ ... p?id=14591 (use the form filter and then run any of the reports)

Background info: [Example #1] Filter/Search with Forms (leveraging SubForms)
Thanks, I've just been going through that last link. It seems that this example (Example #2) is more or less what I want. I think. But the text in the example keeps referring to a "filter-record," but there doesn't appear to be one anywhere in the example file (i.e. no separate filter table, etc). Am I missing something?

edit: To get a little more specific, I've set up a list box with the title for each piece and the ID (displaying the title, with the ID set as the bound field), a push button set to refresh the form, and a table control master/slaved to the main form where the list box is. Obviously, pushing the button just refreshes the form without passing the ID to the table, because why would it? But, like, how should it?
oOo 3.4.1, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report limited by list box choice?

Post by Villeroy »

Open the only table and look at the very first record. This works without any extra table as long as the criteria fields match with the fields to be filtered.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
est_co1
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Re: Report limited by list box choice?

Post by est_co1 »

Ah, did not see that. Thanks!

I think I'm still not clear on the procedure for passing a bound value from one form to another though.
oOo 3.4.1, Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report limited by list box choice?

Post by Villeroy »

You store your filter criteria somewhere and then you query the data rows matching the criteria.

A filter table has this row:
Row_ID: 0
Crit_INT: 9812

Now you can select all data rows where some thingy-ID equals the "Crit_Int" stored in row 0 of the filter table:

Code: Select all

SELECT "D".* 
FROM "Data Table" AS "D", "Filter Table" AS "F" 
WHERE ("F"."Row_ID"= 0) AND ("D"."Thingy_ID" = "F"."Crit_INT")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
est_co1
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Re: Report limited by list box choice?

Post by est_co1 »

I think I get that part, the table control's reference to the filter row and subsequent re-reference to the table itself. What I'm not getting is where the push button (or list-box or whatever does it) is saving the filter record to the filter row. I'm not seeing an UPDATE or ALTER command or macro or anything anywhere.
oOo 3.4.1, Windows 7
Post Reply