OpenOffice.org Base – No Frills Document Management

The code is too long to list it in full here, but it’s available as a neatly packaged OpenOffice.org Basic library on the DVD. To import the library, unpack the InOutFile.zip archive, switch to the form and choose Tools>Macros>Organize Macros>OpenOffice.org Basic. Press the Organizer button, switch to the Libraries section and select the DocMabase.odb database from the Location drop-down list. Press the Import button and select the InOutFile library. Go back to the form and add two buttons to it: Add File and Save File. Now you have to assign the appropriate macro to each button. Double-click on the Add File button to open the Properties window, switch to the Events section, and assign the onClickAddFile macro to the “When initiating” action. In a similar manner, assign the onClickWriteFile macro to the Save File button.

Save the form under the DocForm name. To see whether everything works properly, create a new record, fill it out and attach a document. To do this, press the Browse button, select the desired document and press Open. Hit ‘Save File’ and you should see the name of the attached file in the FileName field. To save it back to the hard disk, press ‘Save File’ and select the destination directory. If everything works as it’s supposed to, you should find the extracted document in the chosen directory.

Automating data entry
Your no-frills document management database is ready to go, but you can improve it in a number of ways. For example, instead of manually entering document information like title, description, notes, word count and so on, you can create a simple macro that pulls this data from the currently active document and inserts it into a new record in the ‘documents’ table. For this trick to work, the document must contain the required information in the appropriate fields in the File>Properties window. Also, you have to register the DocMaBase database with OpenOffice.org if you haven’t done so when creating the database. To do this, choose Tools>Options in the Main toolbar. Select OpenOffice.org Base>Databases and press the New button. Select the document management database and give the new connection a name (eg ‘DocMaBase’). Press OK to save the settings and close the window.

Now you can start working on the macro that pulls the document’s metadata and inserts it into the appropriate fields in the ‘documents’ table. The macro itself is pretty simple, and it does two things. It starts with establishing a connection to the DocMaBase database and then uses an SQL query to insert data retrieved from the document properties and the document word count into the appropriate fields.

Sub InsertMetadata()
DBContext=createUnoService(“com.sun.star.sdb.DatabaseContext”)
DataSource=DBContext.getByName(“DocMaBase”)
Database=DataSource.GetConnection (“”,””)

SQLQuery=”INSERT INTO “”documents”” “ + “(“”Title””, “”Notes””, “”WordCount””) VALUES “_
+ “(‘” + ThisComponent.DocumentInfo.Title + “’,’” + ThisComponent.DocumentInfo.Description + “’,’” +_
ThisComponent.WordCount + “’)”

SQLStatement=Database.createStatement
Result=SQLStatement.executeQuery (SQLQuery)

Database.close
Database.dispose()
End Sub

To make it easier to evoke the macro when needed, you can add it to the main menu and assign a shortcut to it. To do this, choose Tools>Customize, select the desired menu (eg Tools) from the Menu drop-down list, and press the Add button. In the Category pane, select the module containing the macro in the OpenOffice.org Macros section, then select the InsertMetadata macro in the Commands pane, and press Add and Close.

Another thing you might want to add to the form is a visual indicator which shows whether the current record already contains a document. To add this feature, you need to create a conditional SQL query which displays the ‘ATTACHMENT ADDED’ or ‘NO ATTACHMENT’ message depending on whether the FileName field is empty or not. In the document management database, switch to the Queries section and click on the ‘Create Query in SQL View’ link. Enter the following SQL query in the Query Design window:

SELECT “ID”, “FileName”, CASEWHEN( LENGTH( “FileName” ) > 0, ‘ATTACHMENT ADDED’, ‘NO ATTACHMENT’ ) AS “Status” FROM “documents”

The query selects the ID and FileName fields from the ‘documents’ table and sets the value of the Status field to ‘NO ATTACHMENT’ if the length of the FileName field is 0. Save and close the query, then open the DocForm form for editing. Now you have to add a subform based on the created SQL query. To do this, turn the Form Navigator palette on, right-click on the main form and choose New>Form. Right-click on the inserted subform and choose Properties. Switch to the Data section, select Query from the ‘Content type’ drop-down list, then select the created query from the Content drop-down list. The next step is to link the main form and its subform via the ID field. Click on the selection button next to the ‘Link master fields’ entry and choose ‘ID’ field in both columns. Do the same for the ‘Link slave fields’ entry. Now you can add the ‘Status’ text field to the subform. Make sure that the subform is selected in the Form Navigator and drag a text field control onto the form in the Form Designer window. You can then format the added text field to your liking.

That’s all there is to it. While the created document management database is not the most sophisticated solution of its kind, it does provide the basic functionality. And now that you’ve learned a few useful database building skills, you can extend the default functionality even further.

Click here to find more tutorials from Linux User & Developer, or see what features in the latest issue…