Report limited by list box choice?

Getting your data onto paper - or the web - Discussing the reports features of Base

Report limited by list box choice?

Postby est_co1 » Thu Sep 13, 2012 6:10 am

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
est_co1
 
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Re: Report limited by list box choice?

Postby Villeroy » Thu Sep 13, 2012 10:36 am

Example of mine: download/file.php?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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26412
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report limited by list box choice?

Postby MTP » Thu Sep 13, 2012 8:03 pm

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
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Report limited by list box choice?

Postby est_co1 » Mon Sep 17, 2012 6:40 am

Villeroy wrote:Example of mine: download/file.php?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
est_co1
 
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Re: Report limited by list box choice?

Postby Villeroy » Mon Sep 17, 2012 8:34 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26412
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report limited by list box choice?

Postby est_co1 » Mon Sep 17, 2012 1:08 pm

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
est_co1
 
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm

Re: Report limited by list box choice?

Postby Villeroy » Mon Sep 17, 2012 3:24 pm

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   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26412
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report limited by list box choice?

Postby est_co1 » Tue Sep 18, 2012 6:09 am

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
est_co1
 
Posts: 7
Joined: Tue Sep 11, 2012 6:28 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest