[Solved] Populate Writer with data from Base

[rbgf]

Hi,
This is a very newbie question, I know, but I am having trouble understanding what I'm doing wrong.

The context: For a number of reasons, I'm looking to move my sole trader jobs spreadsheet into a database. It lists my clients, the individual jobs, payment details, etc. I figured I could more easily create invoices by having my invoice template contain fields that would be populated from the database at the click of a button.

So I moved my spreadsheet info to tables in Base. Then I created a query to gather all the relevant info about the invoice for the month. In Writer, using Insert>Fields>Other>Database, I have created a template file for an invoice, with fields such as client name, dates, job number, etc. Then, in the Data Sources view, I use Data To Fields to populate the fields. But where there are multiple records in my query (for the different jobs and their payment details), I don't see a way of creating unique fields in Writer for the first record, the second record, etc.

Have I just missed a key step, or am I going about this in completely the wrong way?

Any assistance most gratefully received.
Joe

[Villeroy]

A database report is very much like a text document. When you run a report, the result is a text document. Having only one record per invoice with no invoice items, a serial letter template may be the preferred solution.

Talking about invoices, there should be 2 recordsets at least:
1. The recordset representing the invoice data, typically with client data, invoice number, invoicing date and calculated sums.
2. The invoice items for each invoice, typically with article number, article name, quantity, price.
3. The calculated sums for each invoice may be a third recordset.
I can offer a (rather complex) macro to do this trick with a stand-alone Writer document.

And then there is viewtopic.php?t=56006 by @DACM. It does not come with anything printable, though.

[rbgf]

Hi Villeroy,

Thanks for the reply. What I'm beginning to understand, then, is that my method of creating and populating fields in a doc template is not suitable in cases where there are multiple records. So what I'm thinking is I could populate the template with the invoice data (client data, inv no., etc), and separately run a report or query with the invoice items (ref no, quantity, price, etc) and then copy and paste that into the doc?

Your link to DACM's example showed me how to calculate the sums, thanks, so I'll do something similar.

Nevertheless, I'm interested in what your macro does too. At the moment, I'm wondering what combination of methods/tricks I should employ to get the results I need.

Joe

[MrProgrammer]

Hi, and welcome to the forum.

I have created a template file for an invoice, with fields such as client name, dates, job number, etc. Then, in the Data Sources view, I use Data To Fields to populate the fields. But where there are multiple records in my query (for the different jobs and their payment details), I don't see a way of creating unique fields in Writer for the first record, the second record, etc.

The Mail Merge process generates an automatic Next Record at the end of the template. However you can insert additional Next Record fields in the document to go to the following database record when populating the following fields during Mail Merge. But I have only done that for situations where the number of additional records in known in advance and never varies for the template.

You are more likely to get specific assistance if you attach documents (database, query, and Writer template) demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach pictures instead of the documents themselves).

[Nick N,]

Hi rbgf,

Yes there are two paths you can go by, but in the long run, there's still time to choose the one that suits your needs...
(R. Plant)

I got stuck with print-union once. But there is an alternative given by the icon shown below (upper right frame) that allows you to retrieve the database you have prepared before and, start typing your letter to your customers and carefully select and insert each consequent field to be placed in your document. Once you finish and decide to print, then the program will prompt you for ready-made print-union and merging.

Regards

Nick

[rbgf]

Thank you for the responses - lots to consider. I managed to insert Next Record fields in my invoice template after each invoice item, which almost perfectly fixes my problem.
However, for now I am going to continue building my database up, learning as I go, and come back to the invoice generation issue later when I've got a better overall grasp of database fundamentals. Perhaps then it will be clearer how I should go about it.
Joe