Printing envelopes. mail-merge, oracle or standard reports

[brockman]

Hi all!

I made a database and it's been really helpful this past year so I want to thank f3k and villeroy for their invaluable help thus far. I am writing this because I have turned my attention to an old problem that I found a workaround for, but don't consider solved yet. I'm trying to print an envelope with data from a query(or a table)

I currently have:
a form with a listbox that selects a person by name. This actually sets a scratch table to the persons ID.
a query that finds that persons name and makes a query-table of their firstname, lastname, address1, address2
a button that when pressed opens a writer document.
a writer document formatted to look like an envelope with fields for the persons first name, last name, address1 and address2

What I do to print an envelope is:
Select the person from a the listbox and hit the button, which opens the writer doc. then I print the doc using <ctrl> p and say "yes" that I want to do a mail merge. Then I make the window bigger so I can see the database names, scroll down so I can see the database components, hit the "+" to expand the list, select the query, press "ok", and finally press "print". And it prints!

It works, but it's clunky, so I tried to use a report.

I tried:
making a report and I got the page style so it looked like a nice envelope, but the resulting report was in a table in only one row. not suitable for mailing addresses. It looked like this:
firstname lastname address1 address2

I wanted it to look like this:
firstname lastname
address1
address2

....so I opened the report in edit mode and inserted some rows and copied and pasted the address fields into their correct locations and it didn't work. The fields looks like:
firstname lastname
address1
address2

...but it renders:
firstname lastname

No need to give up yet. I thought I'd use oracle report builder , which I had used about 10 years ago on another project and thought it was kinda cool. So I went to "extensions," enabled it, closed oo and started it up again. I went to reports and hit the "not the wizard" button and nothing happened. I hit the "wizard" button and nothing happened. I searched around and found out that some people with my version seem to have this problem and some don't. It could be java related??? ,,,I guess if my java is too new... Version related? ...I am running 3.5.7.2 ...apache says it works with 3.4...

I guess my question is. How do I make this better?

More specific.

How do I edit a report in the standard report builder so that I can put fields from the same record on different lines?

or...

How can I get oracle report builder to work again?

or...

How can I do the mail-merge thing described above with less clicks?


Sincerely,

brockman

[Villeroy]

How do I edit a report in the standard report builder so that I can put fields from the same record on different lines?

Open the report for editing and edit the text table. There is also a form with hidden fields which determine the source, source type, the sort order, column order etc.
You may also get the old style report wizzard back when you disable the Oracle report builder and restart the office suite. Later versions of LibreOffice disabled the thing completely, I think. I can still edit these reports with Apache OpenOffice.
See also: viewtopic.php?t=81229&p=374955#p374955] ... se Reports with report options in the custom properties (menu:Tools>Properties). Simple report implementation without grouping.

How can I get oracle report builder to work again?

I don't know. Linux? Is there a report builder in the repositories?

How can I do the mail-merge thing described above with less clicks?

I wrote a simple macro which works with mail merge settings in the custom properties (menu:Tools>Properties)
viewtopic.php?t=83996&p=390567#p390567 (demo with Bibliography database)

[MPEcho]

Hi all!
What I do to print an envelope is:
Select the person from a the listbox and hit the button, which opens the writer doc. then I print the doc using <ctrl> p and say "yes" that I want to do a mail merge. Then I make the window bigger so I can see the database names, scroll down so I can see the database components, hit the "+" to expand the list, select the query, press "ok", and finally press "print". And it prints!
brockman

I do similar. Though I don't have a button to open a particular writer doc, I have a doc that has fields from the query already. So open the document, click print, yes merge, click for the entire table (one record) and press print. I don't have to expand anything or select the query because it is already selected. I'm curious how you set up the document to to make life more complicated.

[longi]

Hi!

If you want to use a report with the old fashioned wizard you have other possibility:
As you create a report, you can erase all table.
Then, you can generate a new form (with its origin, etc), because a report is a form.
In this new form(not a subform), you can put your fields in your own way, so you can distribute them like in an enveloppe and you won't have a table in the middle disturbing you.
I know it is a bit weird, but it works for me.

Bye!

[brockman]

WOW! Using forms as reports is a great idea. I feel so stupid for not thinking of this I just made a even better envelope in under an hour using forms!

I can go buck wild with this technique! Thanks, longi!


Update on the other stuff:
Report Fields: I gave up on this. I found several different ways to enter fields and the only one's that worked brought me into mail-merge land. I like the idea of Insert-Field.... but I can also f4 and drag and drop. I can also copy and past special. I'm not sure I ever found the hidden window and i get how the fields are pointing to something else that points to the data, but controling this is not intuitive to me. Thanks for trying to help me understand fields though!

Oracle/Sun report builder: I still can't get it to work. I mean, it's not that difficult. I've got so many versions of this thing and I can't get it. The only thing I can think of is that my Java might be to new.

Mail-Merge Land: I haven't tried this macro yet, but I'm glad you posted it in case it's needed in the future.
MPEcho: part of the problem is that my screen is small and so sometimes windowed data is squished. It's also possible that I set up the database in some hierarchy i'm unaware of or don't understand that requires additional clicks. Anyway, I've got it to one click and print now! so SOLVED!

[robleyd]

The only thing I can think of is that my Java might be to new.

Do you have a 32 bit version of Java installed? OO needs Java 32 bit regardless if your OS is 64 bit.

[Villeroy]

What I do to print an envelope is:
Select the person from a the listbox and hit the button, which opens the writer doc. then I print the doc using <ctrl> p and say "yes" that I want to do a mail merge. Then I make the window bigger so I can see the database names, scroll down so I can see the database components, hit the "+" to expand the list, select the query, press "ok", and finally press "print". And it prints!
brockman

Open your letter hit F4, select the database record you are interested in by clicking the grey row header left of the first field.
Push toolbar button "Data to Fields".

How to limit the displayed records
Define a parameter query and link your mail merge fields to that query. You will be prompted for a name or ID or whatever parameter you use.
Return a single record by some identifier:

Code: Select all

SELECT "Surname", "Forename", "ZIP", "City", "Address" FROM "address table" WHERE "ID" = :Customer_ID

Return all records having a given surname

Code: Select all

SELECT "Surname", "Forename", "ZIP", "City", "Address" FROM "address table" WHERE "Surname" = :Surname

Same case insensitively

Code: Select all

SELECT "Surname", "Forename", "ZIP", "City", "Address" FROM "address table" WHERE UPPER("Surname") LIKE UPPE(:Surname)

Surname starts with ...

Code: Select all

SELECT "Surname", "Forename", "ZIP", "City", "Address" FROM "address table" WHERE "Surname" LIKE CONCAT(:Surname, '%')