[Solved] Insert a database field into Writer - Any Record

[onesimus]

I am wanting to inserting a database field(s) into a Writer Document. I am currently using:

Code: Select all

Insert -> Field -> Other

Selecting the database tab, and choosing Any Record - I put in a record number. But it doesn't insert the data when I choose F4 and select Data to Fields icon

Any ideas would be appreciated.

[JohnV]

At Insert > Fields > Other > Database tab don't use Any Record, instead use Mail Merge Fields. Then open the database on the right, open the table, select the field and Insert.

FWIW, when you do F4, etc., you must select a record before you use the Data to Fields icon. You can also skip F4 and just click the Print icon and answer 'yes' the the 'form letter' query.

[onesimus]

I am currently using the Mail Merge Fields for the first ten records, but I needed to go back to the 1st record again. Is there any way that I can do this ?

[JohnV]

Not that I know of.

Do you need to print all 10 records again?

[onesimus]

I need to print the first ten records, then go back and print the first record again.

[Villeroy]

Drag the row set from the left pane of the datasource window into a spreadsheet. This creates a dynamically linked import range.
Create a link to the first one or two rows (incl. label row) somewhere else.
menu:Data>Define...[More Options]: set all additional options for that import range, hit [Modify],[OK]
Apply the cell and page formats you want.
Set the print ranges accordingly: First 10 or 11 rows of the import range, first one or two rows of the linked range.
menu:Data>Refresh will refresh the import range and the additional range linked to the import range.
When you open the file you'll be prompted to refresh the import range because there are no data saved in the spreadsheet.

[onesimus]

The scenario that I have is as follows: I have a OOo database (.odb) file that on clicking a button launches a OOo Writer template file which contains the necessary fields pointing to the database. The user will import the data records into the template using F4 and 'Data to fields'

@Villeroy
Whilst the solution that you suggest may work, I would like a solution that is hidden as much as possible from the user. The use of an additional spreadsheet may just add confusion.

Am i missing understanding the Any Record option ? The documentation seems to suggest I can pick any record (given a record number) and it will deliver the goods. However, the documentation stops short of giving an actual example, and searching on-line reveals little further information.

[RoryOF]

Why does record 1 have to repeat after record 10? If it is merely a delimiter of some sort, perhaps there may be another way to do this.

[Villeroy]

@Villeroy
Whilst the solution that you suggest may work, I would like a solution that is hidden as much as possible from the user. The use of an additional spreadsheet may just add confusion.

So what? The user opens a spreadsheet, confirms a dialog and hits the print button. Is that hidden enough? Is a simple link so difficult to understand?

[onesimus]

The blunt answer is because I want it to !

I may have to redesign my template, but I would prefer not to.

Doesn't the 'Any Record' option work ???

@Villeroy
If I was the only user then I would implement what you have suggested, however, the users I am working with would be confused by that !

[Villeroy]

What a big shame. Indian tele workers?

[NewGuard]

I wish I could help answer your question, but I am also looking for similar answers. I like the fact that people present alternate ways to do things when you ask a question or have an issue. But, sometimes, it would be nice to get an answer and then get the alternate methods. Don't get me wrong, I like the "free" help and the "free" software, but sometimes a simple answer is more elusive than sound examples in the help and documentation files. Sorry I can't help, but I smell what you're steppin' in.

[onesimus]

@NewGuard
Yes, it would be nice if someone came out and said:

'Sorry this can't be done, despite the documentation saying so. But there is a lack of functionality
within OpenOffice, or their is a bug that has yet to be resolved.'

But without any solutions (or workarounds), I may just have to have a re-think on my way forward.

[Villeroy]

@NewGuard
Yes, it would be nice if someone came out and said:

'Sorry this can't be done, despite the documentation saying so. But there is a lack of functionality
within OpenOffice, or their is a bug that has yet to be resolved.'

But without any solutions (or workarounds), I may just have to have a re-think on my way forward.

Mail merges work for me as documented in various mail merge guides. I have a serial letter with database fields, hit F4, select the one record of a person I want to send a letter to and hit the "data to fields" button which fills all the placeholder fields in that document with the selected record's data. In case of a multiple record selection it seems to import the first record only. I can also print the document directly and select more than one record in the following print dialog.
The "data to text" button lets me create a table, plain text or a new collection of fields for the selected records.

I need to print the first ten records, then go back and print the first record again.

Depending on the type of database, a so called union query can select the first 10 rows of some record set followed by the first one of the same. Looks like this and does not work with pseudo-databases (spreadsheets, text files, dBase, email address books):

Code: Select all

SELECT TOP 10 * FROM "Table"
UNION ALL
SELECT TOP 1 * FROM "Table"

Based on this row set, your document with mail merge fields will receive the selected 11 rows only.

[onesimus]

I have been able to produce the results as I wanted. Rather than have a database with two tables which the writer document incorporates, I have been able to produce a single query that contains the combined data. It was necessary to sort on particular fields (e.g. product and then customer) in order to achieve this.

In the Writer document I include the various fields (using the Next Record field in order to move onto the next product). So that rather than using F4 to enter the data into the fields, I select the 'form letter' when printing, and hey presto I get multiples letters to my customers.