Page 1 of 1

Report limited by list box choice?

Posted: Thu Sep 13, 2012 6:10 am
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.

Re: Report limited by list box choice?

Posted: Thu Sep 13, 2012 10:36 am
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)

Re: Report limited by list box choice?

Posted: Thu Sep 13, 2012 8:03 pm
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).

Re: Report limited by list box choice?

Posted: Mon Sep 17, 2012 6:40 am
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?

Re: Report limited by list box choice?

Posted: Mon Sep 17, 2012 8:34 am
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.

Re: Report limited by list box choice?

Posted: Mon Sep 17, 2012 1:08 pm
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.

Re: Report limited by list box choice?

Posted: Mon Sep 17, 2012 3:24 pm
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")

Re: Report limited by list box choice?

Posted: Tue Sep 18, 2012 6:09 am
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.