[Solved] Report from one-many relation

[yaiban]

Greetings!
I have two tables, connected through a one to many relationship and I want to add a button to a form which contains both these tables, and have that button create a report and send it to a printer. First time for me trying to create a report. I tried googling it. It taught me how to create reports from one single table. I need to print the active row and its sub-rows. Can this be done with the wizard? I also downloaded the oracle report-builder which adds the option "create report in design view". Neither of them gives me any luck.

The id is the key to the first table, table_delivery
The row_key is the foreign key to the second table, table_delivery_sub
Not sure whether the terminology is correct

A picture says more than a thousand words they say. The left part is an example of how I want the report to look. It shares many similarities to the form a wizard creates when you have two related tables. If I have been unclear, tell me and I will try my best to clarify.

Looking forward to replies
Yai

[Villeroy]

Your 1-to-many relations is OK if each sold item is a unique item that can be sold only once so each item appears on one particular invoice.
In this case you make a record from a query which looks like this in SQL view:

Code: Select all

SELECT "T1".*,"T2".*
FROM "ItemTable"AS "T1" JOIN "InvoiceTable"AS "T2" ON "T1"."row_key"="T2"."id"

Otherwise, the sold items of an invoice should be a many-to-many relation where a list of sales maps invoice-IDs to item-IDs.
[List of invoices.ID] 1---n [Sales.InvoiceIDs | Sales.ItemIDs] n---1 [Items.ID]

[yaiban]

Thank you for your reply Villeroy. I have before tried to use

Code: Select all

SELECT d.*, ds.* FROM table_delivery AS d, table_delivery_sub AS ds WHERE d.delivery_id=ds.delivery_sub_row_key

which is essentially the same. The problem is that I doesn't get it to do what I want. Did you see my picture? I want on every report, to show the current active row in table_delivery and all its items from table_delivery_sub. And yes, one delivery can have only unique rows from table_delivery_sub. I.E ONE instance of table_delivery and MANY instances of table_delivery_sub, looking as when you create a form with a subform-grid, using the wizard.
Thanks in advance

[Arineckaig]

The picture is indistinct but if the report is to be printed as a single page either print a copy (view>print layout) of the form document or create an independent Writer or Calc document, suitably set-up, to read data from a query in the Base file.

[yaiban]

Hmm ok, I was afraid I would have to create some kind of writer document. And yes Arineckaig, you are right, I want it printed as a single page. It is what I will send to a customer. (Profiting from zombie apocalypse :» ). Printing a copy of the form document will not do, for it is way too ugly and displays too much information and components.

I was considering to create a new form document with the correct layout(i.e. only text and NO listboxes or textfields) or a writer document. I need this to be dynamic i.e. show the current record of the mainform and all related records in the subform.

It is of utmost importance that the user only needs to press a button to print the relevant information. Maybe one could macro this to create a new writer document and place out textfields on appropriate locations?

I will keep trying and any help is appreciated. Thank you :»

[yaiban]

I attempted to create a form. This gave me an easy time doing the layout. However, while having the grid filled with items and printing the page, the printer spat out a page which was correct EXCEPT that the grid was empty! When I preview the page everything is ok, but then when exporting to pdf/printing the subform's items/rows disappear.

[Arineckaig]

yaiban:

Printing a copy of the form document will not do, for it is way too ugly and displays too much information and components

FWIW I often find it helpful to prepare a special for document for printing a single record: especially as It permits selective exclusion of some displayed items from the print out.

I have no means of guessing why data in your grid controls should not print. Printable form documents can be designed to included several data forms and sub-forms some of which include grid controls reading data from several data source tables. [An independent Writer document can even print data from more than one database].

Without an sample of your form document and some token source data I cannot suggest where your problems may lie.

Later Edit:
I have added a simple demo printable form document, "2p. Cascading sub-forms Print" to a sample database that can be downloaded from:
http://dl.dropbox.com/u/10552709/Indexed-Subforms.odb
and have attached the PDF output from that document.

[yaiban]

I am now inspecting your file (which works), just thought I'd tell you about my form/db. I have the table_delivery which is the upper half. It contains adress, name, ordernumbers and so on. Then I have table_delivery_sub which contains amount, item, item code, and row key. I used the wizard to create my form, and just moved around what it gave me until I had the current layout. Took me 5 min tops. Basically, all is default values from the wizard, hence why I am astonished when print doesn't work.

[yaiban]

I have now tried to use the wizard and show both table_delivery and table_delivery_sub in a grid each. The result is that the information in a grid, is invisible. Everything written in any given grid disappears. Maybe it's worth noting that I don't use the embedded hsqldb, but rather my own mysql database. Should still work though. The information is shown to the computer user, but when you print/export it.

[Arineckaig]

So far as printing a form document from Base is concerned, I have experienced no difference between using MySQL and the embedded database. FWIW, I was taught by Villeroy to exercise caution when using Base wizards for the design of form documents - the wizards may save time but inevitably have their limitations.

[yaiban]

ok
In any case, I tried without the wizard. I created 2 tables in design view and it worked. The thing is, when checking the options of the two forms, and the two table controls, they are all the same as the one in my previous report.

EDIT: I removed all textfields and labels, and it worked! Dafuq is this?
FINAL EDIT: I marked all textfields and labels, removed them with ctrl+x, and put them back with ctrl+v. It worked. Maybe the subform needs to be the topmost component in the form navigation field? Very odd but it worked. Arineckaig, thank you for your help. Your example database gave me some clues