Page 1 of 1
Mail Merge from multiple data sources ?
Posted: Wed Mar 30, 2011 4:37 pm
by onesimus
Is it possible to mail merge from multiple data sources ?
I have a scenario where I wish to send out to my customer base, a list of products and their corresponding prices
i.e.
Dear Customer1
Here are the prices
- Item1 Price1
Item2 Price2
etc.
Dear Customer2
Here are the prices
- Item1 Price1
Item2 Price2
etc.
So there are two databases: the customer and the products.
Can I achieve this in mail merge ? Because at the moment I can only print out the items (using F4 and 'Data to Fields'), I then select print and choose the 'Yes' option for is it a form letter, I then select my customer base.
But I only get one letter with the items correct, but only a single customer.
Re: Mail Merge from multiple data sources ?
Posted: Wed Mar 30, 2011 4:45 pm
by JohnV
If the items & prices are the same for Customer1 & Customer2 then merge these to a single document. Then do Edit > Exchange Database and on the right select the one with customer names and click Define. Now you should be able to click the Print icon, answer 'yes' to the 'form letter' query and print for all customers or ones you select.
Re: Mail Merge from multiple data sources ?
Posted: Wed Mar 30, 2011 5:14 pm
by onesimus
@ JohnV
Yes, prices are the same for all customers.
But I should have been a little bit more accurate in my description.
Customer and Prices are two different Tables within the same database.
But I am only getting one customer printed, despite selecting all customers.
Re: Mail Merge from multiple data sources ?
Posted: Wed Mar 30, 2011 5:55 pm
by JohnV
Try a new Writer document, open F4, select Bibliography and the biblio table. Drag Identifier into the document. Now see if you can print more than one.
If not, it may be a problem with your version of OOo. Ubuntu's version? If so, often somewhat flaky.
Customer and Prices are two different Tables within the same database.
You would still need to change the data source to the other table.
Re: Mail Merge from multiple data sources ?
Posted: Wed Mar 30, 2011 6:13 pm
by onesimus
JohnV wrote:Try a new Writer document, open F4, select Bibliography and the biblio table. Drag Identifier into the document. Now see if you can print more than one.
If not, it may be a problem with your version of OOo. Ubuntu's version? If so, often somewhat flaky.
Customer and Prices are two different Tables within the same database.
You would still need to change the data source to the other table.
I have done what you said, and am able to print many.
But I don't see how to achieve what I am wanting ???
Re: Mail Merge from multiple data sources ?
Posted: Wed Mar 30, 2011 7:17 pm
by Villeroy
Compile a list of articles related to certain customers based on the customers' orders:
Code: Select all
SELECT "Customers"."Name","Customers"."Addr","Customers"."zip","Customers"."City","Articles"."Name","Articles"."Price"
FROM "Customers", "Articles", "Orders"
WHERE "Orders"."Cust_ID"="Customers"."ID" AND "Orders"."Art_ID"="Articles"."ID"
Since each customer gets another list of articles, this requires the report builder extension
http://extensions.services.openoffice.o ... portdesign which can print customer details and article details to different page sections. I'm not experienced enough with the report builder. You may ask in the Base forum if this type of serial letter is possible with it.
#############################################################
Compile a list of articles for all customers:
Code: Select all
SELECT "Customers"."Name","Customers"."Addr","Customers"."zip","Customers"."City","Articles"."Name","Articles"."Price"
FROM "Customers", "Articles"
100 Customers and 1000 Articles gives a total amount of 100,000 lines. The report builder extension should be able to compile 100 letters with a list of the same 1000 articles in this case.
Instead you may create an ordinary serial letter to all customers and insert the table of articles as a static copy into the serial letter. Drag the table/query icon from the left side into the text document.
Compile an ordered list of Articles with proper field names:
Code: Select all
SELECT "AID" AS "Order Number", "N" AS "Name", "Desc" AS "Description", "P" AS "Price", "C" AS "Category"
FROM "Articles"
ORDER BY "C" ASC, "P" ASC