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 from version 3.0, OpenOffice Base now has the ability to save scripts in a database file (with the .odb extension), which allows you to conveniently distribute macro OO Basic code

There is some confusion with the 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 Form are a little different. And Form is not only Form. I will explain in more detail.

When you open the OpenOffice Base database in edit mode, you see four tabs on the left side of the screen. In the Russified version it will sound like
')

• Tables
• Requests
• Forms
• Reports

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

Note. Because a form is actually an instance of OpenOffice Writer — an application can be created without downloading the OpenOffice Base component — right into OpenOffice Writer.

Each Form has a Forms collection of Form objects. Initially, this collection is empty. Despite this, you can open and close the Form. And even display text information in it. And only when adding 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 before that time.

It is most convenient to create new forms from the Form Navigator (shown on the palette as a compass), which can also be accessed 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 can be useful when defining relationships between tables.



Here it is necessary to note one anomaly of the Form object. This object is primarily an object associated with a database table or an SQL query. You can think of a Form as a ResultSet object (it really is). On the other hand, Form 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 with another name you like. In this Form element, create a child Form element.

Each Form must be linked to an existing database table, query or SQL query. In the subordinate Form, you can set rules to reflect the data filtered by the value of the associated fields in the main Form of the Master / Slave type. To do this, in the property editor of the subordinate Form, you need to fill in the properties link master fields and link slave fields.

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

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

SELECT USERNAME, USERID FROM USERS ORDER BY USERNAME 

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

Create a filter for the database table by the value entered in the text box. To do this, we write in OO Basic the procedure that handles the 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 item property list). Note that this text field must be located in another Form (not the one on which the filter is applied) so that the reload of oForm.reload () does not “reset” its current value entered from the keyboard.

To be continued.