Hi There,
I have a spreadsheet which has filtered rows in it. I only want to mail merge with a document the lines that are now showing. How can i stop the document taking all the lines (both visible and suppressed)?
Thanks
Mail merge using a spreadsheet with filtered rows
Mail merge using a spreadsheet with filtered rows
KW/SAC
Openoffice version 4.1.0
Windows 7
Openoffice version 4.1.0
Windows 7
Re: Mail merge using a spreadsheet with filtered rows
Greetings and welcome to the community forum!
It would be nice if the filtering were applied when the sheet is used as a data source but as far as I can tell, it isn't--at least I can't get it to work.
The really proper way to manage this is to create a database query to filter the data. You can do that by opening the database document, select "Queries" at the left, then click on one of the "Tasks" options.
If you don't want to deal with creating a query, you can use the spreadsheet filter option "Copy results to ..." to get the filtered rows on a separate sheet, then use that sheet as the data table. NOTE: You may have to quit and re-start OO to see the new table--spreadsheet data sources can be difficult that way.
It would be nice if the filtering were applied when the sheet is used as a data source but as far as I can tell, it isn't--at least I can't get it to work.
The really proper way to manage this is to create a database query to filter the data. You can do that by opening the database document, select "Queries" at the left, then click on one of the "Tasks" options.
If you don't want to deal with creating a query, you can use the spreadsheet filter option "Copy results to ..." to get the filtered rows on a separate sheet, then use that sheet as the data table. NOTE: You may have to quit and re-start OO to see the new table--spreadsheet data sources can be difficult that way.
AOO4/LO5 • Linux • Fedora 23
Re: Mail merge using a spreadsheet with filtered rows
HI acknak,
Thanks for that - You're right, I'm not keen to create a database for every spreadsheet! I tried the copy and paste to a new document, but it takes over the original formula in the parent box rather than the result.
what i mean by that is for example is this:
Smith gives £10/month and so has 12 rows for the year (1 for each month). There are columns for both the monthly income figure and an accumulator column, adding the monthly income together. When the filter is activated, we are left with one row for Smith with the accumulator column. When this is copied and pasted to a new spreadsheet, the accumulator column looks for a new set of figures rather than carrying over the old set. It is as if i need to "freeze" the set of figures somehow before copying it.
Thanks for that - You're right, I'm not keen to create a database for every spreadsheet! I tried the copy and paste to a new document, but it takes over the original formula in the parent box rather than the result.
what i mean by that is for example is this:
Smith gives £10/month and so has 12 rows for the year (1 for each month). There are columns for both the monthly income figure and an accumulator column, adding the monthly income together. When the filter is activated, we are left with one row for Smith with the accumulator column. When this is copied and pasted to a new spreadsheet, the accumulator column looks for a new set of figures rather than carrying over the old set. It is as if i need to "freeze" the set of figures somehow before copying it.
KW/SAC
Openoffice version 4.1.0
Windows 7
Openoffice version 4.1.0
Windows 7
Re: Mail merge using a spreadsheet with filtered rows
1) Data>Filter>Standard... [More Options] lets you filter the result to another sheet which is then another table in your pseudo-database.
2) Queries are the best way to filter tables because they store all the criteria permanently, as many as you need in any evaluation order. You can also build parameter queries which prompt the user for the filter criteria: SELECT * FROM "Sheet2" WHERE "Name" = :Enter_Name_Please [select all columns from Sheet2 and prompt for a filter value in the "Name" column]
3) Then you can filter directly in the mail merge dialog. Print the document, confirm that you are going to do a mail merge and filter the list in the next dialog or select the records you want manually.
All the above options can be combined.
2) Queries are the best way to filter tables because they store all the criteria permanently, as many as you need in any evaluation order. You can also build parameter queries which prompt the user for the filter criteria: SELECT * FROM "Sheet2" WHERE "Name" = :Enter_Name_Please [select all columns from Sheet2 and prompt for a filter value in the "Name" column]
3) Then you can filter directly in the mail merge dialog. Print the document, confirm that you are going to do a mail merge and filter the list in the next dialog or select the records you want manually.
All the above options can be combined.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Mail merge using a spreadsheet with filtered rows
Your orgainisation of data is inadequate.KW/SAC wrote:HI acknak,
Thanks for that - You're right, I'm not keen to create a database for every spreadsheet!
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Mail merge using a spreadsheet with filtered rows
Sorry, I don't see any easy way to use the spreadsheet filtering with formulas. It would be nice if there was a "copy only values" option.
With some care, you should be able to make the formulas work even after they're copied. The simplest way seems to be to set up a database range (Data > Define Range ...) to copy the results to. This can be on the same sheet and the output range will appear as a separate "table" when you connect to the data source.
It can be labor intensive to make the formulas work after being copied, and it invites mistakes so it has to be done carefully.
The big advantage of the query is that it will always, automatically, carry out the proper filtering of the rows. It's a nuisance to get it set up the first time but after that you can forget it--there's nothing you need to do.
With some care, you should be able to make the formulas work even after they're copied. The simplest way seems to be to set up a database range (Data > Define Range ...) to copy the results to. This can be on the same sheet and the output range will appear as a separate "table" when you connect to the data source.
It can be labor intensive to make the formulas work after being copied, and it invites mistakes so it has to be done carefully.
You don't need a database for every spreadsheet. You only need a "database document" for any spreadsheet you want to use as a merge data source. And there is always a database document for a merge, so you already have the database document that connects your Writer document to the spreadsheet data. All you have to do is add the query to the (existing) database document.... I'm not keen to create a database for every spreadsheet!
The big advantage of the query is that it will always, automatically, carry out the proper filtering of the rows. It's a nuisance to get it set up the first time but after that you can forget it--there's nothing you need to do.
If you're going to go to the trouble of Copy/Paste, you can use Edit > Paste Special and un-select "Formulas".... I tried the copy and paste to a new document, but it takes over the original formula in the parent box rather than the result.
AOO4/LO5 • Linux • Fedora 23