[Solved] Report from one-many relation

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

[Solved] Report from one-many relation

Postby yaiban » Wed Jul 18, 2012 3:50 pm

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
4uo8b.png

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
Last edited by yaiban on Fri Jul 20, 2012 10:41 am, edited 2 times in total.
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am

Re: Report from one-many relation

Postby Villeroy » Wed Jul 18, 2012 7:56 pm

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   Expand viewCollapse view
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]
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: 26627
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Report from one-many relation

Postby yaiban » Thu Jul 19, 2012 8:04 am

Thank you for your reply Villeroy. I have before tried to use
Code: Select all   Expand viewCollapse view
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
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am

Re: Report from one-many relation

Postby Arineckaig » Thu Jul 19, 2012 10:06 am

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.
Last edited by Arineckaig on Thu Jul 19, 2012 3:02 pm, edited 1 time in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Report from one-many relation

Postby yaiban » Thu Jul 19, 2012 10:20 am

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 :»
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am

Re: Report from one-many relation

Postby yaiban » Thu Jul 19, 2012 12:29 pm

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.
Attachments
prmBaseBU8.png
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am

Re: Report from one-many relation

Postby Arineckaig » Thu Jul 19, 2012 1:06 pm

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.
Attachments
Indexed-Subforms2p.pdf
Sample pdf output from printable form document
(35.35 KiB) Downloaded 177 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Report from one-many relation

Postby yaiban » Thu Jul 19, 2012 3:16 pm

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.
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am

Re: Report from one-many relation

Postby yaiban » Fri Jul 20, 2012 8:09 am

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.
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am

Re: Report from one-many relation

Postby Arineckaig » Fri Jul 20, 2012 9:52 am

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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Report from one-many relation

Postby yaiban » Fri Jul 20, 2012 10:34 am

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
Apache OpenOffice 3.4.1 on Linux Mint 14
yaiban
 
Posts: 59
Joined: Fri Jun 15, 2012 8:35 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests