OpenOffice.org Training, Tips, and Ideas

Doing interesting things with queries: multiplying two fields together, and summing detail records

Here are a couple interesting things you can do. Let's say you've got a bunch of customers with a few invoices each, and you just want to know how much each of them has spent. You take your one customer record and you add many invoice records together to get the total they spent on all invoices.

You can also multiply items: let's say your invoice table has the item price, and number of items purchased on the invoice, but not the total per invoice. You can multiply item price by number of items to get the total.

Open the database.

Click on the Queries icon and choose to create a query in Design view.

You'll get a window where you can choose which table to use. In this example I'll use the invoices table. Click Add to add the table.

Then double-click a few fields to add them. I'm going to add a few here, then delete some later.

Now I've got the fields added.

If I click Run Query then I just get those fields.
 

Note I have the unit price and number of items but not the total per invoice. Here's how to do that. Just type as shown in the far right column. It is case sensitive.

Clicking Run Query gives the results.

Which look like this--you get the Alias you typed, not the multiplication. Note however that it is not formatted as currency in this example. If you want to change it you can right-click on the column heading, choose Column Format, and select a currency or number format.

And here's what that looks like.

Here's a note. You don't have to have the fields you're multiplying in the query. You can unmark the checkmarks as shown and they won't show up. Or you don't even have to have them at all; you can right-click on any column head and choose Delete to remove it from the query. (Do this in the bottom area of the query where you do the defining, not the top where you see the results.)

Now you're thinking about doing that sum thing, adding up all the totals for all the multiple invoice for each single customer. For this, you need to have just the customer ID (one) and the calculated field (many for each one customer). So I deleted everything but those two columns.

Then for the single field I selected Group in the function list, and I select Sum for the many field, as shown.

Run query, and here are the results. Note that now each customer is only listed once, and the total for their invoices is calculated.

 

How to specify fieldname Not Equal To fieldname in an OpenOffice.org Base query

Let's say that you have a whole bunch of data. You're the head of HR and you have thousands of employees. You want to figure out which ones you need to talk to about new benefits because they've changed their health insurance plan from one year to another. You just need to figure out which are the ones who changed. How do you do that?

You create a query that says "find me all the people for whom Plan08 field does not equal Plan09".

OK, good. But what's the syntax?

Design View

<>  is how you say not equal

and then you just put square brackets around the fieldname.

so in the field for Plan08 you just put   <>[Plan09]

Here's some example data. You need to have the fields set up so they have the value for each employee's choice for health plan for 08, and then again for what they're switching to for 09.

 

The data needs to be in or connected to a database file, an ODB file.

Then you open your database .odb file containing that data, and you click the Queries icon at the side.

You have to create the query in design view or in SQL view, you can't use the wizard.

In design view it's as I showed before, and in SQL it's just "Plan08" <> "Plan09".

Here's how it looks in design view.

 

and here's the results when you click the green-checkmark Run Query icon.

Doing an email mail merge without most of the overhead of the OpenOffice Mail Merge wizard

I'm afraid the Tools > Mail Merge Wizard has never been one of my favorites. Too complicated.

I always train people to just "roll their own" when making a mail merge document.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

Everything you can do in the mail merge wizard, you can pretty much do in the roll-your-own approach, especially since you can now print all documents to a single file and then open that and customize it as you like, before printing on paper.

Printing

and viewing/editing the file output. (Click to see the bigger version; you'll see different values in the two letters.)

BUT

One thing you can't do in the roll-your-own approach is do an email mail merge.

Do you really want to go through the complexity and muscle aches of using the Mail Merge Wizard? No. And you don't have to. You're going to mix and match.

Step 1. Create your email the way you want it with the roll-your-own approach.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

Save it. Keep it open.

Step 2: Set up email configuration. In Writer, choose Tools > Options > OpenOffice.org Writer > Email. This setup worked for me. Key settings are smtp.comcast.net and port 587. You just need to do this once. If you have security on your email, like requiring a password to send, you'll have to click the Server Authentication button and enter additional information.

Step 3: Choose Tools > Mail Merge Wizard. Choose Current Document, or else browse to your document, and click Next.

Choose Email and click Next.

Select the database you're using and the table. Click OK and click Next.

Keep clicking next til you're here. Fill it in by selecting the field from the database that has the emails in it, and anything else you want.  Click Send Documents.

You'll see the progress window (in this test I only sent two emails).

 

And you're done!

The received email looks like this. Note that I sent it in email format, but with the extra carriage returns I put between the lines in Writer, it looks a little spacey here. You'll want to experiment with and adjust how you format the original Writer documents and what format you send in.

Cell formats, and using NOW() and other functions in OpenOffice Calc spreadsheets that you use in databases and mail merges

Mail merges work fine with spreadsheets as the data source. You create a spreadsheet of data, then choose File > New > Database, specify connecting to an existing database, Spreadsheet as the type, then click Next and point to the database itself.

Here are a couple things about formats, though.

Formats don't come through. So if you want your Salary field to have dollar signs, decimals, etc. you need to set the format. Likewise with date or time.

One way to do this is just set the formats when you view them. Press F4 and expand to view the query or table you want. Then just right-click on the column heading and choose Column Format.

Then in the window that appears you can select a category (Currency, Date, Number) and the the specific format you want. Click OK.

You can do the same thing in the database file itself. Open the .odb file, click the Tables or the Queries icon at the left, then double-click on the particular table (the sheet or query).

In the window that appears, right-click on the column heading, choose Column Format as before...

and in the window that appears, as before, make the choice you want. Click OK.

Now, here's a related topic. Can you use the =NOW() function in a spreadsheet and have it interpreted correctly in the database and in mail merges? (Thanks to a Colorado Springs LUG member for this idea.) The answer is Yes.

Here's a spreadsheet with =NOW() showing the absolutely current time and date.

Here's what it looks like, with nothing done to it, in the database view. It needs some tender loving formatting since it's just showing the internal numeric value. Right-click on the column heading and choose Column Format.

 

Set the format you want. The NOW() function can let you use a date or time format since it contains both.

 

And now it looks fine in the database view.

 

When I use this field in a mail merge....

Here's the output when I format the field as a date:

 

and when I format it as a time.

Suppressing empty fields (and the lines they're on) in OpenOffice.org labels, or any mail merge document

Note: This is a repost but useful. It's important to follow the steps exactly. Everything is case sensitive. Also when you type the two "" quotes, don't put a space between them. If you have spaces in your database, this won't work. Either change the database field names, or create a query based on the table, and change the field names in the query. Then base the labels on the query.

Here it is -- suppressing a blank Address2 field in your mail merges. It's not extremely simple, but it's reasonably straightforward and it works.

Here's the situation we're addressing. Sometimes your addresses have two lines for the address part, sometimes they don't.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave
Broomfield, CO 82211

But you have to put in the <Address2> field for everybody, since it's a mail merge. The setup has to be the same.

<Firstname> <Lastname>
<Address1>
<Address2>
<City>, <State>, <Zip>

But with this approach, your addresses look like this.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave

Broomfield, CO 82211

Ick. How do you suppress that second Address2 line and the corresponding carriage return if there's no content for a particular record, for that Addres2 field?

Select the Address2 field in your mail merge document, choose Insert > Section, and create a conditionally hidden section with this formula.

databasename.tablename.fieldname EQ ""

Here are the details, using an example of labels.

1. Create the labels for mail merge as usual. File > New > Labels, select your database and tables, insert the fields, etc.

2. Choose the Synchronize Contents checkbox.

3. Click New Document.

4. Here are the labels.

5. Turn on nonprinting characters if they're not on already.

6. Select the first soft return, shown selected.

7. Press Return or Enter to replace it with a hard return.

8. Repeat, to make them all hard returns.

9. Click Synchronize to update the other labels to be the same.

10. Select the Address2 field.

11. Choose Insert > Section.

12. Name the section Suppress. Select the Hide checkbox and type the following condition. The screen shot shows the syntax.

Syntax
databasename.tablename.fieldname EQ "" � �(the last part is two double quotes together)

Example
databasewithtwoaddresslines.Table1.Address2 EQ ""

NOTE: if you are using the Thunderbird address book as a data source, you need to use square brackets if the field name includes a space (i. e.: [Address 2]=="") to hide the second line of the address if it the Address 2 field is blank.)� I would suggest in general avoiding field, table, or database names with spaces.

http://www.oooforum.org/forum/viewtopic.phtml?t=43528&highlight=

Click the screen shot to see it bigger. It shows the syntax, not an actual example.

13. Click Insert.

14. Click Synchronize.

15. Now preview the data or print the data and you'll see that it prints correctly.

15. If you need to change the section, select it in the first address and choose Format > Section. Select the one named Suppress for the master label and make changes, then click OK. Click Synchronize again in the labels.

---

---

Creating Views and Queries in OpenOffice.org 2.0

Views and queries let you cherrypick what fields, and what data, you want to see. You can also create calculated fields to add data, and change the field names. You can then base mail merges, among other things, on what you've created.

This is an article on creating views in OpenOffice.org 2.0. Queries are pretty much the same thing with a few differences.

See also part 1 and part 2 of an article about creating databases in the database tool in OpenOffice.org 2.0, and the forms article, part 1 and part 2.

---

A useful little hack for mail merges and addresses, in OpenOffice.org

When you set up labels, you set them up like this.

Firstname�� Lastname
Address1
Address2
City, State� PostalCode

All of those are mail merge fields.....EXCEPT the comma.

So when you print out 17 labels on your 30-label sheet, you get a comma on EVERY label in the sheet. This is annoying.

To get around this, add a field in your data source, like your spreadsheet, that's just a comma. Make a new column anywhere, call it Comma, and type a comma in every row or record.

Then instead of typing the comma, just insert the Comma field.

Getting the contents of a table or query into an OpenOffice.org Calc spreadsheet

For the longest time, the really easy way of bringing database content into a document didn't work in Calc spreadsheets. At least, not for me. But now in 2.4 it does. So here you go. It's the same approach you use in Writer, just a little more limited.

Choose View > Data sources or  press F4.

Expand the DB you want, then the table or query you want. Select the table or query name.

Click on the upper left corner as shown, the un-obvious little gray square.
 

Click and hold down and drag into the document. And you'll get your data.

---

In OpenOffice mail merge documents, you see the field names like Name onscreen, not the data like Bob. The data appears when you print.

I just wanted to clarify something since I hate to see people thinking they're doing something wrong when they're doing it right.

Here's an example using labels.� This is how it's supposed to look. You won't see the data onscreen, you'll see the field names like first name. When you print, the correct data will appear.

If you don't know want to print all the data, you can pick the ones you want in this� window. Just choose File > Print, click yes that you want to print a form letter, and then select the records you want in this window. Select the first record, hold down Ctrl and select the next one, and so on.

Or specify a range.

Then just click OK.

A Huge Printable PDF Article on Advanced OpenOffice.org Report Techniques, Including SQL-Based Calculated Fields on Reports and Using the Next Record field (Repost)

I decided to repost this since it's got lots of really fun powerful NOT obvious technical stuff for reports.

---------------------------------------------------------------------

Here's the TOC.

A Quick Review of the Report Writer Tool

What You Can Do in the Query Design Tool and SQL View

What You Can Do With Report-Specific Fields

Quick-and-Dirty Reports

Creating Your Own Reports Using the Next Record Field

Powerful Reports Can Be Fun....If You Enjoy Getting Your Hands Dirty

 

Here's the link. It's a big PDF.

---

---

Starting a new database: spreadsheet or native Base database?

Let's say you've got a bunch of data: addresses, or lists of your DVDs, or whatever. It's not world-changing high-transaction information, but you want it in a good storage format

Do you create a spreadsheet and put your info in that, or do you go straight for the higher-end solution and create a Base database?

The good news, it doesn't matter that much. Generally, I would recommend that you start with the spreadsheet, then if necessary go to the Base format. The transition isn't a matter of right-clicking and choosing Convert but it's not bad. Pasting, essentially.

Follow these directions to get your data in a spreadsheet, then make a database file that points to that spreadsheet. Your spreadsheet might be called mymailinglist.ods and your database file might be called mailinglistdatabase.odb. Note the different file extensions. (You can call the files Bob.ods and Mary.odb if you want; these are just examples.)

Then if you find you want to do more complex operations like queries, data entry forms, etc. follow the directions in this article, also below, to turn your spreadsheet into a true Base database.  You create a new database file, like mynativedatabase.odb, and paste the contents of your spreadsheet into it. You will then use mynativedatabase.odb instead of mailinglistdatabase.odb.

Turning a spreadsheet into a native Base database

Creating or opening a new database

Now, you get to choose what database file you want to bring the data into.

If you've already got a database file
If you've already got a database, open it up by choosing File > Open. The database file can have existing tables in it; you can add new tables to it from the external data or you can append the external data to an existing table if the data has the same columns.

If you need to create a database file
If you don't have a database, you need to create one. Choose File > New > Database. Choose to create a new database and click Next.

In this window, just leave everything as is and click Finish.

You'll be prompted to save the file. Save it under any name you like and click Save.

You'll see the database file open up like this. You're ready to continue to the next step.

Bringing the data into your database

This is the simplest part. Open your spreadsheet, and paste it into your new database.

You heard me -- that's all there is to it. Well, there's a little more, but it's very easy. When you paste, you'll create a new table with the pasted data.

Note that you can append to an existing table or create a new table. I'll cover appending later in this section.

Creating a new table
Here's what the process looks like to create a new table.

1. First, in the spreadsheet, select all the data you want to bring in. Do include the column headings like Name, Address, etc.

   

2. Now move over to your database file. Click the Tables icon on the left, since you're going to be adding a table.

   

3. Right-click in the table area and choose Paste.

   

4. And you'll see this wizard, which will walk you through the rest of the process.

   

5. Name the table and leave the Definition and Data item selected. Also, decide whether you need to create a primary key. If you've already got something like a customer ID that is unique, you don't need to. If you don't, choose to create a primary key. Click Next.

   

6. In the next window, insert all the fields you want to bring over. You can use the >> button to add them all. Click Next.

   

7. In the Type Formatting window, be sure that the file types and other settings are correct. Then click Create. If you're prompted to create a primary key, you can do it now or do it later (I'll cover that toward the end of this article).

   

8. Your table appears in the database.

   

Appending data to an existing table
You approach this the same way as adding. But in the first wizard window, choose to append, and type the name of the table to append to.

Click Next. Line up the fields you're pasting with the fields that are already there. You can unmark fields to import and use arrows on both sides to make sure that the fields are lined up correctly. The fields don't have to have the same names: Zip will import correctly into Postal Code as long as the data types and other factors are correct.

Click Create, and the data will be added to the table.

Editing and viewing the imported data

Once you've got the data in, you can edit it normally. Here are a few common tasks.

Editing the table definition
In the main database window, right-click on the table name and choose Edit. The window will look something like this. Make changes to field names, types, etc. and save changes.

Opening the table to view or add data
In the main database window, double-click on the table name. The window will look something like this. Make changes to data and save changes.

Specifying a primary key
Each table must have a primary key. Right-click on the table name in the Tables area and choose Edit. In the window that appears, right-click on the name of the field that should be primary key and choose Primary Key. Close the table, saving changes.

Conclusions

This is a pretty straightforward approach -- once you know about it, of course. I think something along the lines of, oh, an option called Import under the Tools or File menu would have been nice; let's hope it'll be in the next version.

But enough "wouldn't it be nice" for now. Returning to the metaphor I began with, you've now got a front door in your database so that you can get the data in. But what if you want to get data out again? Let's say you want to take your table, query or view, and you then want to export it to a spreadsheet or CSV text format. Again, there is no easy way. There is no option under File or Tools. But there is a way. And it's the topic for my next article.

---

---

How to turn your spreadsheet into a database for mail merges in OpenOffice

Got labels?

I've been pointing folks to the instructions in this blog, but I wanted to pare things down so that I have one blog entry, just one, that shows only how to make your spreadsheet into something you can use in mail merge letters, label mail merges, envelope mail merges, etc.

Overview of How Mail Merges Work in OpenOffice

First: To print labels from a list of addresses, you should  put them in a spreadsheet. (You don't have to since text files, address books, and regular databases are fine for holding your data for mail merges, too. But spreadsheets are a simple approach.) Put headings across the top like Firstname, Lastname, etc.

Then: To make your spreadsheet into something that your mail merge document (labels, Christmas letter, etc.) can talk to, you need to make a little database file that points to your spreadsheet.

Finally: You make your mail merge document (labels, Christmas letter, etc.) and have it point to your database.

That's the summary. Here are the specifics.

Instructions for the First Step, Putting Together Your Data in a Spreadsheet

Here's what your data should look like. The columns you use are up to you. Put in whatever data you will need. Put City, State, etc. all in different columns. Be sure to put the label at the top for what the field is: Name, etc. This is important.

Here's a sample file. Right-click on the link and choose to save it to your computer.

Instructions for the Next Step, Making Your Spreadsheet Into a Database

You need to make a database file that points to the spreadsheet.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is. This is the name that you will see in your lists of databases.
 

You're done with the database.

Instructions for Making Labels or a Letter and Pointing It at a Database

To make labels that point to the database you created, follow these instructions. You've already done the database so

http://openoffice.blogs.com/openoffice/2006/07/mail_merge_labe.html

To write a letter that points to the database you created, follow these instructions, steps 3 and 4. You've already done 1 and 2.

http://openoffice.blogs.com/openoffice/2007/01/mail_merge_in_o.html

To do envelopes that point to the database you created, follow these instructions.

http://openoffice.blogs.com/openoffice/2007/08/printing-envelo.html

I strongly advise you NOT to use any of the mail merge wizards under the Tools menu. Far too complicated.

---

---

Rough Draft: Video Tutorial of Creating a Database and Simple Mail Merge

This is not fancy. There are a couple goofs. But it is accurate, and it's a video. Let me know how you like it!

~~~~~
I realize the size is quite big -- one thing that isn't obvious because of that is the navigation tools way at the bottom. (I'm pretty sure that they show up for everyone; I hope they're not just appearing for me somehow because I have Captivate installed.)

Sun Report Builder Extension

Anyone who's worked with the OpenOffice.org Base report writer knows that it's....a first generation product. It works but it doesn't have huge features. So I’m particularly glad to see some work being done with reports, in the new Report Builder extension from Sun.

http://extensions.services.openoffice.org/    for all extensions

http://extensions.services.openoffice.org/project/reportdesign   for the Sun Report Builder

The Report Builder extension looks like it has a lot of powerful features, though not exactly easy to see how to use. I’ve spent a few hours with it and one thing that bugs me a bit is that the tab for selecting the data source for the report disappears if you click on something else first. Ease of use aside, though, it does have quite a feature set, including grouped records, sorting of records, different alignment of text fields, and calculations.

I'm going to have to spend a lot more time with this to really figure it out and give some procedures, but here's a short tour of the basics.

To use the Sun Report Writer extension, download and install it first. (Tools > Extension Manager). Then open the .odb database file for the database you want to create a report for. Choose Insert > Report, and you’ll see the report writer interface.

 

This is the tab that disappears too quickly. Select Table or another type of data, then select the actual source. Once you make that selection, the Add Field palette appears; use it to drag fields onto the appropriate section of the report.
   

Click the Sorting and Grouping icon on the toolbar to get this window where you have a lot of control over how the fields and the report behave.

 

When you’ve dragged fields onto the report, set options, inserted page numbers, and done other formatting, save the report. The report will show up in the Reports area of the main editing windows of the .odb database file.

 

---

---

Summary of new features in OpenOffice.org 2.3

Here’s a summary of the features from the 2.3 new features list that I considered the most useful or important to write about. This page  http://wiki.services.openoffice.org/wiki/New_Features_2.3 about the new features is an excellent guide, as well.

General

• This is convenient for anyone who prints to multiple printers, all over the world. You can load or ignore the printer settings for your documents. This means you don’t end up accidentally printing to the printer in building 4 which is on the opposite side of the country, just because you were on a business trip there last week and that’s where you last printed your document.
  

• If your document isn’t wider than the OpenOffice.org window, then it will be centered in the window, not left-aligned.
  

• Lots of locale information was added, for locations such as Tagalog, Frisian, and Hausa.
  

Writer and Web

• The HTML editor now has a preview feature. Choose File > Preview in Web Browser and the document opens in the default browser.

• I love this feature. You know how when you get a hyperlink but then want to retype it or reformat it, but clicking on it takes you to the target of the link? No more. You can select hyperlinked text all you want; you now have to Ctrl Click to open a link. This is very nice.
  

• The notes say that there is a new compatibility option on Tools > Options > OpenOffice.org Writer > Compatibility: Do Not Justify Alignment in Lines Ending With Manual Line Break. However, I’m mentioning this because I couldn’t see it. The illustration shows the compatibility options that are there.
  

• When you open the Styles and Formatting window (Format > Styles and Formatting), you can set what kinds of styles you wanted to see: Applied, Custom, Automatic, etc. Previously, you had to reset this every time you opened a new document or re-opened OpenOffice.org. Now, thankfully, that category will stick. The setting is saved per application. However, the choice you make for Paragraph, Character, Frame, List, or Page doesn’t stick.
  
  

• When you right-click on text, you used to see Default as one of the options. Now you see Default Formatting, which is clearer. (Default Formatting is a great way to just clear out any extraneous formatting and apply the default style to the selected item.) This is a very nice feature regardless of the text; for one thing, it’s the best way to remove the hotlink from a URL.
  

• A new export filter lets you export to MediaWiki format. Choose File > Export and select MediaWiki in the file format list.

Calc

• This is a very, very smart change. By default, the print options for Calc are now set to Print Only Selected Sheets and Suppress Output of Empty Pages. If the Print Only Selected Sheets option is enabled, the Calc page preview shows only the displayed sheet and the message “There is nothing to print.” To change these options, choose Tools > Options > OpenOffice.org Calc > Print, or choose File > Print and click the Options button.
  

• Here’s another very smart change that will screw up all my documentation. :) The SUM icon on the main Calc toolbar has changed. Now you can select the range of numbers to add, click the SUM icon, and get the total in the first cell below the selected range. Phew. But if you liked it the old way, it still works that way, too.
  

• Graphics can be linked to macros. This should help with Excel compatibility.
  

• The Excel export filter now handles the cotangent functions COT, ACOT, COTH, and ACOTH.

• Calc now supports inline matrix/array constants in formulas. An inline array is surrounded by curly braces '{' and '}'. Elements can be each a number (including negatives), a logical constant (TRUE, FALSE) or a literal string. See this link for more detail. http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=230

• You can now use dynamic ranges, rather than absolute ranges defined with $, in lists in Data Validity. Choose Data > Validity, and under the Criteria tab select Cell Range from the list.
  
  

• The GETPIVOTDATA function returns a result value from a DataPilot table, so it can be used in a cell formula.
  

Mail Merge, Databases, and Forms

• The infamous checkbox on the print message when you print a mail merge document, Do Not Show Warning Again, is gone. Phew! See this blog http://openoffice.blogs.com/openoffice/2006/10/how_to_get_the_.html for why that caused problems.
  
  

• This is nice. When you choose File > Print with a mail merge document, in the Mail Merge window, you can choose to save the document as separate documents or as one document.
  
  

• Unfortunately, in Base there is still no File > Export or File > Import feature. File > Export does appear, but it’s dimmed.

---

---

A Huge Printable PDF Article on Advanced Report Techniques, Including SQL-Based Calculated Fields on Reports

I realized that while this was published on TechTarget, I hadn't made much of a big deal of it here.

Here it is. Lots of fun, techy stuff about reports. Here's the TOC.

A Quick Review of the Report Writer Tool

What You Can Do in the Query Design Tool and SQL View

What You Can Do With Report-Specific Fields

Quick-and-Dirty Reports

Creating Your Own Reports Using the Next Record Field

Powerful Reports Can Be Fun....If You Enjoy Getting Your Hands Dirty

 

Here's the link. It's a big PDF.

---

---

Much easier, much simpler ways to print the contents of a database in OpenOffice.org Base

I've been so caught up in the complicated tools and cool hacks that I've forgotten about the really simple ways to get data from a database into a Writer document.

I'm so embarrassed.

Here's a simple way to bring in data, whether it's still in fields, or just plain text. You probably want to use this instead of the Reports feature, and instead of the Next Record field under Insert > Fields > Other, Database tab.

Here are some screen shots. I'll do more on this later but I just wanted to slap these options out there so you know about them.

Click any of the screen shots to see them bigger and in more detail.

1. Create a new text document and choose F4.

2. Click the + by your database, click the + by the word Table (or Query) and click the table or query you want to print.

3. Click the blank gray square shown, to the left of the first fieldname.

4. Click and hold down on that gray square and drag into the document.

5. Now you have various options.

Pick the option you want, insert the fields you want, apply any relevant formatting, and click OK.

Table

Fields

Text

Then print. If you bring in the data in fields, when you choose Print, click Yes when prompted and DON'T click the checkbox.)

Again, if you're printing in fields, you cna print to files or a printer. To select specific records, select the first record, hold down Ctrl, select another record, and so on.

 

 

---

---

Updating the Preview in Your Mail Merge Docs so You See Different Data in Every Field

This is a very specific but annoying problem for which I've found a workaround (thanks to the smart folks in the training department at MASCO! ;> ).

A Little Background: Synchronize
So, you've got a mail merge doc. It's labels. You've set it up to Synchronize, so that when you make a change to the upper left address and click Synchronize, all other addresses take on the same updates. I.e. if you make the text blue, or insert a graphic, or whatever, you just make the change in one place and it updates all the others. This is a very nice feature.

Here's where you set synchronize. File > New > Labels, and in the Options tab select Synchronize contents.

Here's what a new sheet of labels looks like with the Synchronize floating button:

Here's what it looks like when you change the formatting for the upper left address:

Here's what it looks like when you then click Synchronize:

A Little Background: Preview
As you can see above, you see the field names, not the field contents, in mail merge documents. To see the contents, press F4 to view your data sources. Expand the data source you're using, select the table you're using, click the blank gray box that's circled to select all the rows, and click the also-circled icon called Data to Fields.

Here's what it all looks like.

But! What Happens When You Preview, Then Synchronize???????
Let's say you've previewed so you can see the data .Then you think, "Hey, the font needs to be different, and I want it to be green." So you make that change to the upper left frame like normal, you click Synchronize....and you get this. All the content is the same.

Don't Worry: It's Easy to Fix
You just click that same Data to Fields icon. Select all the data just like before, click the Data to Fields icon again, and this refreshes the display and gives you back the normal content.

---

---

Suppressing Blank Address2 Fields in OpenOffice.org Labels, Envelopes, or Other Documents, for Mail Merges

Note: Everything is case sensitive. Also when you type the two "" quotes, don't put a space between them. If you have spaces in your database, this won't work. Either change the database field names, or create a query based on the table, and change the field names in the query. Then base the labels on the query.

Here it is -- suppressing a blank Address2 field in your mail merges. It's not extremely simple, but it's reasonably straightforward and it works.

Here's the situation we're addressing. Sometimes your addresses have two lines for the address part, sometimes they don't.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave
Broomfield, CO 82211

But you have to put in the <Address2> field for everybody, since it's a mail merge. The setup has to be the same.

<Firstname> <Lastname>
<Address1>
<Address2>
<City>, <State>, <Zip>

But with this approach, your addresses look like this.

Bob Jones
101 Main
Suite 55
Boulder, CO 80022

Marion Silverman
888 105th Ave

Broomfield, CO 82211

Ick. How do you suppress that second Address2 line and the corresponding carriage return if there's no content for a particular record, for that Addres2 field?

Select the Address2 field in your mail merge document, choose Insert > Section, and create a conditionally hidden section with this formula.

databasename.tablename.fieldname EQ ""

Here are the details, using an example of labels.

1. Create the labels for mail merge as usual. File > New > Labels, select your database and tables, insert the fields, etc.

2. Choose the Synchronize Contents checkbox.

3. Click New Document.

4. Here are the labels.

5. Turn on nonprinting characters if they're not on already.

6. Select the first soft return, shown selected.

7. Press Return or Enter to replace it with a hard return.

8. Repeat, to make them all hard returns.

9. Click Synchronize to update the other labels to be the same.

10. Select the Address2 field.

11. Choose Insert > Section.

12. Name the section Suppress. Select the Hide checkbox and type the following condition. The screen shot shows the syntax.

Syntax
databasename.tablename.fieldname EQ "" � �(the last part is two double quotes together)

Example
databasewithtwoaddresslines.Table1.Address2 EQ ""

NOTE: if you are using the Thunderbird address book as a data source, you need to use square brackets if the field name includes a space (i. e.: [Address 2]=="") to hide the second line of the address if it the Address 2 field is blank.)� I would suggest in general avoiding field, table, or database names with spaces.

http://www.oooforum.org/forum/viewtopic.phtml?t=43528&highlight=

Click the screen shot to see it bigger. It shows the syntax, not an actual example.

13. Click Insert.

14. Click Synchronize.

15. Now preview the data or print the data and you'll see that it prints correctly.

15. If you need to change the section, select it in the first address and choose Format > Section. Select the one named Suppress for the master label and make changes, then click OK. Click Synchronize again in the labels.

---

---

Huge, detailed list of links for using macros in OpenOffice.org

This huge and excellent list was put together by the helpful and brainy folks at www.oooforum.org, including SergeM.

http://www.oooforum.org/forum/viewtopic.phtml?t=50952

I don't write macros myself, but if there's something you're doing with macros that you can't find help with on that page, I imagine you're doing something with macros that's either very advanced, like gene research, or something that just isn't possible.

---

Beware! Saving in Word format, and mail merges, don't mix.

Mail merges don't work if you're saving your OpenOffice document in Microsoft Word format.

Whether you're doing this manually, or doing it with the window under Tools > Options > Load/Save > General, saving in Word format will strip out the mail merge fields' connection to the database.

So you can save in .doc by default, but you need to save the mail merge documents, whether they're labels, envelopes, or other documents, in OpenOffice.org native .odt format.

---

One Way to Export Data From OpenOffice Base to a Spreadsheet

In OpenOffice.org 2.2 you can now export data in a reasonable manner. Here's one way in 2.4.

Here's another way you might try. It's almost identical to the (albeit invisible) import method.

1. Open your database .odb file.
2. Click the Tables icon.
3. Right-click on the name of the table to export. Choose Copy.
4. Go to a spreadsheet. Paste.

The data will appear.

---

Sample Project for Joining Tables, Regarding Primary Keys

If you don't spend every day hip deep in databases, you might not always be clear on the whole primary key thing, and how to join them.

I want to clarify that this will work.

Let's say you've got two tables.

One is a table with city residents' IDs and names, street numbers, street names, etc.

The other is a table with street names and the days on which garbage is collected.

You need to send people a letter telling them what day their garbage is collected on. Thus you need to link the two tables.

The primary key on the first table, the ID field, is different than the primary key on the second. The primary key on the second could be the street name since the street name is unique in that table.

1   Oak    Monday
2  Elm     Monday
3  Main  Tuesday

and so on.

To create the query, you don't need to link the primary keys. You can link the two tables simply by connecting the two Street Name fields when you create the query.

1. Open the database file containing the two tables.
2. Click the Queries icon at the left.
3. Click the option to create a query in design view.

4. In the window that appears, select the first table name and click Add.

5. Select the second table name and click Add.

6. Click in the second table, on the common field, in this case the StreetName field, and drag your mouse to the other table's StreetName field. (If this doesn't work, drag from the first table's field to the second table's field.)

7. Now create your query. Double-click the name of any field you want in the query. In this case it might be Name, Street Number, Street Name, and Garbage Day.

Run the query by clicking the Run Query icon.

The results will appear.

Save the query and close it.

Now create your mail merge. You can create a new Text Document and choose View > Data Sources. Open the database containing the query and under Queries select the query you created

Drag the fields you want into the letter. You might want it like this.

---

Creating Calculated Fields in OpenOffice Base

When you make a database, you of course have it chock full of data.

Contractor name, Contractor pay rate, Number of hours the contractor worked, etc.
Invoice ID, Item price, Number of items purchased, etc.

Now, let's say you want to figure out something based that data. The pay rate times the hours to equal the total amount paid, for instance.

To do that, you make a query.

1. Create a database. (File > New > Database, select the first radio button in that window and continue.)
2. Create or add a table.  (Choose the Wizard or design view.)

<>

3. Open the database file. (File > Open and find the .odb file.)
4. Click the Queries icon at the far left.

5. Click Create Query in Design View.

6. In the window that appear, select the table that contains the fields you need to calculate and click Add.

7. The table will appear in its own separate window. Add all the fields that you want in the query -- you might all of them. To add a field, double-click it. It will appear in the design area at the bottom.

8. To add the calculation, click in the top cell of the first blank column. Type fieldname*fieldname

<>

For instance, to multiply the contents of Payrate by Hoursonjob,   type
Payrate*Hoursonjob

In the cell below that, the cell to the right of the Alias label, type the name you want to use to refer to this calculated field, like TotalPaid.

Then click the Run Query icon to run the query.

You'll see your results, with the calculation.

For any of the columns, including the result, you can apply currency formatting. Right-click on the column heading and choose Column Format.

Select the format you want and click OK.

The formatting is applied.

Now you can:

• Just view the data
• Run a report based on the query
• Create a mail merge of some sort based on the query

---

OpenOffice.org Base Queries: Switching Between SQL and Design View

One of the simplest ways to write a query in OpenOffice Base, while still not having to be a programmer, is to use the Design view. Or even if you're as comfortable with SQL as anything in life, design view is still kind of a pleasant little tool.

To create a query in design view, open the database file, click the Queries icon, and click the Design View option.

You can click Add to add the table or tables to the query, then double-click each field you want in the query. It appears in the query design area. Add your functions, criteria, etc. Run the query if you like.

Now, let's say you have a sudden attack of the SQLs. You can just choose View > Switch Design View On/Off and the view will change to SQL.

Here's the SQL view.

Make the same menu selection again and the view changes back.

---

Very cool feature in OpenOffice Calc spreadsheets: Edit > Paste Special, Transpose

Chris from the Boulder LUG pointed this out to me last night while I was singing the praises of Edit > Paste Special in OpenOffice.org.

Let's say you've got this data.

It's accurate, but you want the columns and rows in different positions. You want it flopped 90 degrees like this.

Here's all you need to do.

1. Copy the original data.

2. Choose Edit > Paste Special.

3. Leave All selected and select Transpose.

4. Click OK.

And there you are, with the whole thing flopped around the way you want it.

Now just delete the old data, drag the pasted data over it -- whatever you want.

---

Mail Merge in OpenOffice Writer: Creating Mail Merge Documents From Text/CSV or Spreadsheets

I've got a lot of info out there, including lots of coverage in my book, about mail merges. However, I don't have a nice simple straightforward blog on it with everything in the same place all spelled out. Didn't, that is. This is all you need to do to make a nice simple document based on data in text files or spreadsheets.

What You Have to Do

1. Get your data. You've already got it, probably. This blog  is for people with data in text files, and in spreadsheets.

2. Turn it into a data source.

3. Create your mail merge document and suck the data in through the data source.

4. Print, specifying how many of the data records you want to print for, and whether to print to a file or printer.

1. Get Your Data

You probably already have it. It's in a .txt file or .csv that's comma or tab separated, perhaps. Or it's just a spreadsheet.

2. Make the Data Source: Text File Instructions

If your data is in text files, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Text as the format.

3. Click Next.

4. Specify the DIRECTORY where the text files are. Each text file in that directory will be a table in your database. Then select the item separating fields, i.e. a tab or comma or something else.

5. When all the settings look correct, click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.

You're done.

2. Make the Data Source: Spreadsheet Instructions

If your data is in a spreadsheet, follow these steps.

1. Choose File > New > Database.

2. Make the selection shown, with Spreadsheet as the format.

 

3. Click Next.

4. Specify the spreadsheet file. Each SHEET in that spreadsheet will be a table in your database.

 

5. Click Next.

6. Umark the option to open the database for editing. You can open it; you just don't have to.

 

7. Click Next.

8. Save the data source (aka database) under a name that will help you remember what it is.
 

You're done.

3. Create Your Mail Merge Document and Suck the Data In From the Datasource

You can also use the simple or complex mail merge.

Simple:  http://openoffice.blogs.com/openoffice/2006/03/techtarget_arti_1.html

Complex: http://openoffice.blogs.com/openoffice/2006/02/techtarget_arti.html

But this is a nice way to do it too.

1. Create a new Writer document or open a document containing text that you want in the mail merge document.

2. Choose View > Data Sources. Everything you've created will be displayed. Click the + sign by the data source you want to use, then click + by Tables til you see the data you want to use.

3. Type any content you want and do any formatting. You can do this later too.

4. Click on the NAME OF THE FIELD, not the piece of data, that you want in the mail merge.

5. Drag it into the document and release. The field name will appear.

 

6. Add any other content and fields you want.

Save the document. You're ready to print.

4. Print the Mail Merge Document.

1. Choose File > Print.

2. You'll see this message. Click Yes. DON'T MARK THE CHECKBOX SAYING YOU DON'T WANT TO SEE THE MESSAGE AGAIN.

3. In the print window, specify the range of records, if you don't want them all, and specify to print to a printer, or to files.

4. Click OK.

5. In the print window, specify the printer and click Print.

---

Modify the Layout of Prefab OpenOffice Labels Under File > New > Labels

Note: For other label tweaking tips, click here and here.

I was just putting together the mailing labels for my books (thanks everyone!) and the 8163 label layout built into OpenOffice.org was too wide for my printer.

What to do?

Changing the margins seems like the first thing to do. But when I do that, the layout skews. The page thinks there isn't enough room for both columns of labels so the second column of labels jumps to the second page.

Then I thought, well, I'll  shrink the width of the little frame things that the text is in.

When you try to do that, though, you get the Ghostbusters symbol that indicates "no changing anything here, buddy."

However, you can change the frame so that its size and position are no longer protected.

Some of you, reading along, are saying, "Um, Solveig....there's that adjustment window in the freakin' label creation window for just such purposes. Why not use that?" That's a good one too. ;> If you haven't already typed or pasted a lot of content into your actual label document, at least.

So here are some ways to tweak your labels. These apply to the prefab ones used through the File > New > Labels window. The ones you get from WorldLabel are made from tables, not frames, so the font modification items might work but the frame stuff is irrelevant.

Tweaking Label Size in the Label Window Before You Create the Label Document

Choose File > New > Labels.

Select the correct page size and label number, such as Letter and 8163.

Click the Format tab.

The Left Margin field is what you want to increase, if the left side of your labels are getting cut off. Increase it just a tad, perhaps to .3.  Or if your margins are getting cut off on top, change the Top Margin measurement. Then click New Document to create the label, if you're ready.

The new label document will have a slightly bigger  margin to give you some breathing room. (Or it will otherwise reflect the change you made in the Format tab.)

Tweaking Already-Created Label Documents

If you've got your document and you don't want to re-create it, here's some stuff you can do. Be forewarned that it is harder to control than the previous approach.

Here's the sample I'm working with; there's content only in the top two rows just from sheer laziness on my part. ;>

Right-click on the top left frame and choose Frame.

In the Frame window, go to the Options tab and unmark the Size and Position checkboxes.

Click OK.

If you want, you can just physically drag the frames one by one. Or use this approach to modify their dimensions all at once.

Choose Format > Styles and Formatting. Be sure that Frame styles are displayed. Right-click on the Labels style and choose Modify.

Now you can do whatever works in your layout. Make the label slightly narrower, perhaps 3.75  or 3.8.

Click OK. This will update the measurements of all the frames.

Choose Format > Page. In the Page tab, set the left margin to something a tad larger, like .3. Make sure the right-hand margin is .01 or something similarly small. You want to make sure there's enough room on the left side to get all your content in on the left without it being cut off and a too-wide right margin will goof that up.

Click OK.

Your labels should have a little more space on the left now, without the content being cut off.

Alternate Approach to Indenting Text From Left
I tried this once but the frames went kaflooie. Then I tried it again and it was fine. See how it works for you.

If you haven't selected the Synchronize checkbox, use this approach to quickly reformat. Choose Format > Styles and Formatting. Be sure paragraph styles are showing, and that all of them are showing.

Right-click on Default and choose Modify.

Now you can update the Default style, used by the label text.  In the Indents and Spacing tab, specify a left indent of .2 or .3.

Click OK.  All label formatting will be updated to add more space to the left.

 

One-Step Reformatting of Label Contents
If you haven't selected the Synchronize checkbox, use this approach to quickly reformat: make all your label content 17 point red Arial Bold or whatever you want.

Choose Format > Styles and Formatting. Be sure paragraph styles are showing, and that all of them are showing.

Right-click on Default and choose Modify.

Now you can update the Default style, used by the label text.  In the Font tab you can of course change the font.

Click OK.  All label formatting will be updated.

---

TechTarget Article: Getting Data Back Out of OpenOffice.org Base Databases

I've written an article for TechTarget.com on how to export data from a Base database to a spreadsheet or text file.

http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1226457,00.html

See this blog for how to import data from a spreadsheet into a Base database. (This creates a true Base database, as opposed to a database file that points to a spreadsheet.)

---------
Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export
---------

Exporting data from an OpenOffice.org Base database

Solveig Haugland
10.30.2006

Digg This!     StumbleUpon     Del.icio.us

I think somebody on the OpenOffice.org database development team has a mystery fetish. He or she read too many murder mysteries or hardboiled detective novels early in life or worked for too many years as the White House Easter Egg hunt director. Whatever the reason, this apparent fetish shows up in the Base module.

The best mail merge tool isn't on any of the menus...you have to hunt it down and add it. You can easily get data into Base...but you won't find it by looking for a File > Import command. And if you want to get data out of Base....well, it's possible, but certainly not under anything as logical as File > Export.

This keeps me busy writing articles to bring all the mysteries into the clear light of day. This article is all about how to actually export data from your Base database.

Why export data in the first place?

You don't necessarily need to export data from Base. If all you do is enter data and pop it out in OpenOffice.org reports, you're fine. But let's say you need to submit your data to, oh, the IRS or another government agency. Or suppose you work with another organization that uses a different database. In this case, you'll need to provide your data to that group in a form they can read. That format is usually a spreadsheet or a delimited text file. This article will walk you through how to do it.

The technique is a hack, of course. (The way it's supposed to work....doesn't work.) But as hacks go, it's not horrible. You take the data from your table, view or query; bring it into a Writer document; then massage it to become a spreadsheet or delimited text file.

Step 1: Prepare the data

If you want to export all the data in a table, you can skip this. But if you need to export only certain records or certain fields, you need a query or view to restrict the data appropriately. I'm not going into detail about that here; see this article to create one.

Once you've got a query or view, complete this step to update and make sure your query or view contains the latest data.

1. Open the database file (File > Open and select the .odb file).

2. Click the Tables icon at the left to view views; click the Queries icon at the left to view queries.

3. Double-click the view or query.

4. When it opens, click the Refresh button.

5. Close the view or query and database, saving changes.

Step 2: Bring the data into a table in a text document

1. Create a new Writer text document (File > New > Text Document).

2. Choose View > Data Sources.

3. Expand the database and view the table, view, or query to get the data from.

4. Click the blank square in the upper left corner to select all.

5. Click and hold down on the blank square; keep your mouse held down.

6. Drag your mouse into the text document and release your mouse.

7. The Insert Database Columns window will appear.

8. In the window, leave Table selected, and click the >> button to move all fields to the right side of the window.

9. Click OK.

A table will appear containing all the data.

Step 3: Creating a spreadsheet or delimited text file from the data

We're on the home stretch now. If you want the data in a Writer table, you're done. But if you want it in a spreadsheet or a text file, follow these steps.

1. Select the entire table, including the headings.

2. Copy.

3. Create a new spreadsheet (File > New > Spreadsheet).

4. Click in the upper left corner.

5. Paste. The data will appear.

6. Resize the column widths as necessary.

7. Save the spreadsheet.

8. If you need to create a delimited text file, choose File > Save As and select Text CSV as the file format. Specify the name you want for the text file and click Save.

How it's supposed to work (but doesn't)

I'm including this because this might work sometime soon, and because the online help tells you to do it this way. I would still, of course, prefer that the next rev of Base include something simpler, like a File > Export function.

1. Open up your database file, then open your table.

2. Click the upper left blank square to select all, or Shift+Click to select certain rows.

3. Copy.

4. Paste into a spreadsheet.

But, as I said, this doesn't currently work in 2.03 or previous versions. (I haven't tested it on 2.0.4 yet, since it crashes each time I start it on both my computers.)

Another mystery revealed

Digging the hidden treasures out of OpenOffice.org is actually kind of fun. I enjoy donning my virtual trenchcoat and dark glasses. (I'm already planning who will play me in the movie, tentatively titled All Suarez-Potts Programmers.) Of course, it's not that fun for frustrated users; I hope that the annoyance and frustration among users is at least somewhat offset by my revealing the mysteries of the Base module here.

---

Getting Data Back Out of OpenOffice Base

Click here.

Note: Having clearly labeled import and export features for Base has been proposed and you can vote for it by clicking one of the following links. Here's how to make things work until the features are implemented and put into the next build.

Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export

---

Here's the related article on the cleverly disguised import function, for getting data from a spreadsheet, Access, or another database into an OpenOffice Base database.
http://searchopensource.techtarget.com/tip/0,289483,sid39_gci1222186,00.html

---

Turning data into information with functions in OpenOffice Base

I've written an article for TechTarget on how to use the functions such as Sum and Max in OpenOffice.org Base queries and views.

It took me a while to grok how to use these -- the difficulty is not in applying the functions, but in the kind of data you can use the functions with, and the Group function that needs to be applied along with Sum, Max, or whatever you're using.

 

---

More on the Data Pilot: Analyzing a Data Source/Database

 

It's time for more data pilot adventures.

I blogged here about the data pilot. The example I used there was for analyzing data in your spreadsheet. You also, of course, have the option of analyzing, using the data pilot, any of your data sources. You'd want to analyze a data source rather than a spreadsheet for many reasons-- perhaps the data is in Access and not in a spreadsheet. Or the spreadsheet is just realllllly big and you don't want to have to open it every time you want to run data pilot on it.

 

Here's my sample data for this blog. Just a nice basic set of data for a week. It's similar but different to what I used in the previous blog and more suitable for demoing the different options. Click to see it bigger.

The first step, of course, is to make sure you have a data source. (Or database—it's all kind of the same thing.) If you already have your data in Access or another database, you'd skip this, of course. However, let's say that this spreadsheet is extremely large and I need to make a database out of it to save time.

Here's what you do to create a database from a spreadsheet; using a different type of data is pretty similar.

1. Choose File > New > Database.

2. In the window that appears, select the last radio button, and Spreadsheet as the type. Click Next.

3. In the next window, just specify where the spreadsheet is that you want to turn into a database. Click Next.

4. Unmark the Open for Editing option and click Finish.

5. Save the database with an obvious name. This is the name you'll be selecting from dropdown lists in OpenOffice.org.

Now you've got a database.

 

To analyze a database, start the same way as usual.

1. Open a new empty spreadsheet and choose Data > Data Pilot > Start.

2. Choose to use a data source already registered with OpenOffice.org. Click OK.

3. Select the data source name, then the sheet name (typically Sheet1) where your data is, and specify Sheet as the type of data. Click OK.

4. Now you're ready to drag fields into the layout area and set options as you have with other analyses. This time, I'm using the Date fields as the quick filter field, so I put it in the Page area. The rest is as pictured.

• I double-clicked the Units Sold field and selected Average. In the results, we'll see the average for each book and store, not the total.
• For options, I'm choosing to identify categories, and to sum rows and columns.

<>

 

Click OK.

 

Here are the results of the setup shown above.

---

New OpenOffice Base Database Workbook

I've finished my Base workbook!

Click here to see the TOC.

For the rest of the year it's at a Special Introductory Price of just $15. Feel free to provide suggestions or corrections for what to add to it. I'm not a DBA so would appreciate information on what additional tasks would be most useful with the tools in Base.

---

How to Get the "Do You Want to Print a Form Letter?" Message Back for OpenOffice Mail Merges

If you've done mail merges before, you've seen this message.

 

What you should do, every time, is to click Yes and leave everything else alone. Then the mail merge will print with the contents of your data source.

What would make sense is to mark the Do Not Show Warning Again checkbox and click Yes, thinking that every time thereafter you'll be able to print the mailmerge correctly, with the contents of your data source, just like you did this time, but without that pesky message popping up.

You'd be logical, but you'd also be wrong because of the wacky design of the program. If you mark the checkmark, then from that day forward you will print, instead of a mail merge, a list of fields like <Firstname> and <Lastname>.

So:
How do you get that dialog box to come back so you can print a mail merge correctly?

Answer: Here's how it's supposed to work. People say this works. I can't find a file with the relevant flag in it on my machine but if it works for you, great. It should work.

<< Reader David Beroff offers some fine suggestions after his successful implementation, which I have updated the instructions with.>>

1. Close OpenOffice.org.

2. Find the Writer.xcu file. It's in one of these locations.

~/.ooo-2.0-pre/user/registry/data/org/openoffice/Office/Writer.xcu

Documents and Settings\[users]\Application Data\OpenOffice.org2\  user\registry\data\org\openoffice\Office\Writer.xcu

3. Make a backup copy of it. Just copy the file in your file manager and paste it somewhere else.

4. Open it with an Ascii editor (i.e Notepad or 1stPage or some such program.)

5. Look for <prop oor:name="AskForMerge">  and set the value to true. To do this, look for "AskForMerge=False"  and type "True" where it says "False."

6. Save the file.

Now you'll get the message popping up again when you print a mail merge. Leave the checkbox alone and click Yes, and you're golden.

 

---

Another Mystery Solved: Getting Data Into an OpenOffice.org Base Database

---------
Vote for adding a wizard to import data into Base
http://qa.openoffice.org/issues/show_bug.cgi?id=51904

Utility to export CSV from Base
http://wiki.services.openoffice.org/wiki/CSV_export
---------

I swear, my shift and matching pumps are getting a little worn out with all this detective work.

I've written an article for TechTarget about how to get data out of one database, such as Access, into an OpenOffice database table. Not exactly obvious, but easy.  You just need to know what to do. (And I'm pleased to say that my mantra, "When in doubt, right-click," does apply here.  ;>  )

(You can of course just type from scratch but when you've got 50,000 records of census data, for instance, that's kind of impractical.)

---

Tweaking Your OpenOffice Forms Created From the Wizard

A reader wrote to me about how to automatate a 50-page document that had a mix of canned and uncanned text, data entry fields, etc. I didn't have a perfect solution but the email did remind me about forms.

I wrote an article for TechTarget about how to create forms based on a database; that post is here. The salient portion is actually here on TechTarget.  The main point is that the simplest and easiest approach to creating a nice data entry form is through the wizard.

Another approach is to choose File > New > XML Form Document. I haven't even begun to get into the guts of forms like that--here are some of the windows/palettes you get when creating XML forms. Click to see it larger.

As a nonprogrammer, I'd just like to say that Submissions and Bindings sounds a little kinkier than a basic data entry form, and also that I need to do a lot more work before I can blog intelligently on either topic.

And now I'll move on to my main point in this blog, which is....

How to tweak your forms created in the wizard if they're not what you want.

OK. So you've created this form in the wizard.

You want to make a few changes--one, obviously, that the Review field isn't big enough.

Open the database that it was based on, choose Forms, then right-click on the form name and choose Edit.

The form will open up looking like this.

To change the size of a field or make other changes, first ungroup it from its prompt. Right-click on the field, choose Group > Ungroup.

Then  just click on the field  and resize it the way you would a text box or a graphic.

You can check the form to see if it's what you want now. Click the Design Mode On/Off icon in the Form Controls toolbar. (If you don't see that toolbar, choose View > Toolbars > Form Controls.)

However, the field still doesn't look right--it doesn't wrap and the text is centered vertically in the middle.

So click the Form Design On/Off icon again to go back to edit mode and fix this.

At this point, you always just right-click on the field and choose Control, pretty much regardless of what you need to do. You'll see the form control window. Scroll through the first tab and see if there's anything that jumps out at you. In this case, there is--I changed the text option from single line to Multi Line.  (DO NOT CHOOSE MULTILINE WITH FORMATTING. This will delete the connection to the database.)

   

Now the field looks the way you want.

Now, let's say you need to change one of the fields from a regular entry field to a list box or combo box, which would present a list of choices. A List Box requires the user to select one of the items you set up; a Combo Box lets the user select from the list or enter their own.

To demo this, I'll use the Genre field in this form. I ungroup the field as before, plus I had to  rearrange fields below it to give it some room. Then I resize it to make it bigger, so that there'll be room to display the options.

<>

Now I right-click on the field and choose Replace With. Here are all the options. List box, radio buttons, all sorts of fields, etc.

I'm going to choose Combo Box. Here's what it looks like.

Now I'll create the items that will show up in the list. I right-click and choose Control.

In the window that appears, click on the List Entries  field, and type the items you want to appear in the list. After each one, type Shift + Enter to go to the next line. When you're done, close the window.

That's all there is to it. The field will look like this.

And when you create a new record, you get the same options to choose from. The selection is saved in the database the form is connected to.

There's a lot more you can do to tweak forms, of course, but those are two pretty representative types. Just use the Replace option, and right-click and choose Control to manipulate the changed field.

TechTarget.com Article: Creating Forms in Base 2.0

I'm cleaning up my blog's sidebar areas, and categorizing posts. This post is one of the results--linking directly to an article on TechTarget.com.

This is an article on creating forms in the new OpenOffice.org 2.0 database tool: part 1 and part 2.

See also part 1 and part 2 of an article about creating databases in the new database tool in OpenOffice.org 2.0, and the article on  views in OpenOffice.org 2.0.

---

TechTarget.com Article: Creating Views and Queries in OpenOffice.org Base 2.0

Views and queries let you cherrypick what fields, and what data, you want to see. You can also create calculated fields to add data, and change the field names. You can then base mail merges, among other things, on what you've created.

This is an article on creating views in OpenOffice.org 2.0. Queries are pretty much the same thing with a few differences.

See also part 1 and part 2 of an article about creating databases in the database tool in OpenOffice.org 2.0, and the forms article, part 1 and part 2.

---

TechTarget.com Article: Creating Databases in Base 2.0

I'm cleaning up my blog's sidebar areas, and categorizing posts. This post is one of the results--linking directly to an article on TechTarget.com.

Here's part 1 and part 2 of an article about creating databases in the new database tool in OpenOffice.org 2.0.

See also articles on views  and the forms article, part 1 and part 2.

---