Programming LibreOffice Base. Part 2

Part 2 will discuss the relationship between database tables and forms. In particular, how to define a relationship between two tables and use “reference tables” as a data source for drop-down lists.

Versions of OpenOffice and LibreOffice for which this message is relevant from 3.0 to 6.2 (the current version of LibreOffice). Starting with version 3.0, OpenOffice Base now has the ability to save scripts in a database file (with the .odb extension), which makes it convenient to distribute OO Basic macro code

. There has been some confusion with forms in OpenOffice Base. The fact is that in the process of software evolution from an office suite to a database environment, it turned out that Forms, Forms, Forms and Forms are a bit different. And Form is not only Form. I will explain in more detail.

When you open the OpenOffice Base database in editing mode, you see four bookmarks on the left side of the screen. In the Russian version, it will sound like

• Tables
• Inquiries
• Forms
• Reports

In this sense, the Form is a separate application that runs in a separate window. I also note that in fact this is the window in which the instance of OpenOffice Writer is launched.

Note. Since the form is actually an instance of OpenOffice Writer - you can create an application without loading the OpenOffice Base component - right in OpenOffice Writer.

Each Form has a collection of Forms of Form objects. At the beginning, this collection is empty. Despite this, you can open and close the Form. And even display text information in it. And only when you add a new control (for example, Button), the system will create a Form object with the default name Form, if you have not created such an object yourself by that time.

It is most convenient to create new Forms from the Form Navigator (displayed on the palette as a compass), which can also be called from the View | Toolbar | Form Navigation menu. Form objects are created either at the top level in the Forms collection, or subordinate to another Form object. This does not affect the appearance of the Form, but it can be useful when defining relationships between tables.



Here we should note one anomaly of the Form object. This object is primarily an object associated with a database table or with an SQL query. You can think of Form as a ResultSet object (the way it really is). Form, on the other hand, is a container for controls, like FORM in an HTML document (and this is also true).

Open the newly created Form in edit mode (right mouse button | Edit). Create a new Form object in it with the name Form, or another name you like. In this Form element, create a child Form element.

Each Form needs to be associated with an existing database table, query, or SQL query. In the slave Form, you can set the rules so that the data filtered by the value of the related fields in the main Form is displayed as Master / Slave. To do this, in the property editor of the subordinate Form, fill in the link master fields and link slave fields properties.

Now we will add a Table Control element to each of the Forms by selecting the appropriate Form in the Form Navigator>. The Table Control element is not in the panel of available elements. To display an expanded list of elements, you need to activate the More Controls button in the element palette. After adding the Table Control element, it is necessary to enter the editing mode of this element and add the columns required for the display of the table (right mouse button -> Insert / Replace / Delete Column)

The second type is the frequently encountered in practice type of relationship between tables (after Master / Slave), and which we will consider - a relationship of type Directory. We store in the data table the key of the GUID object, autoincrement, and in the displayed table we display its full name, taken from the linked table, the “directory”. There is a convenient mechanism for this. In the visual Table Designer, add a column of type List Box. If a column of type Numeric / Text has already been created in which the key is displayed (and not the name), it is possible to change its type to List Box (right-click | Replace With | List Box). Next, edit the column properties list. On the data tab, set the Type of List Content property to SQL. The request in the List Content property must contain the displayed value in the first column, and the Bound Field property is the index of the column with the key field (assuming that the first column has index 0). That is, in the vast majority of queries this will be 1 for queries of type

SELECT USERNAME, USERID FROM USERS ORDER BY USERNAME

So far, work has been described in configurator mode without programming. Add functionality with OO Basic macros.

Create a filter for the database table by the value entered in the text box. To do this, we write in the OO Basic language a procedure that processes an event from the keyboard.

 Sub TextBox_onKeyUp(oEvent)
   oDocument = ThisComponent
   oDocument.LockControllers
   sText = oEvent.Source.text
   if Len(sText) > 3 Then
     oForm =  ThisComponent.Drawpage.Forms.getByName("MyForm")
     oForm.Filter = "USERNAME LIKE '" + sText  + "%'"
     oForm.reload()
   End If
   oDocument.UnlockControllers 
End Sub

We associate this procedure with the Key released event of the text field (on the event tab of the element's property list). Note that this text field must be located in another Form (not in the one to which the filter is superimposed), so that reloading oForm.reload () does not “nullify” its current value entered from the keyboard.

To be continued.