Programming LibreOffice Base. Part 3
- Tutorial
Today we look at the use of dialog boxes when editing data in LibreOffice Base (OpenOffice Base). As we found in previous posts, the LibreOffice Base (OpenOffice Base) form is actually an instance of the LibreOffice (OpenOffice) Writer text editor. To organize the window interface inside the Form, Dialogs are used - which are conveniently created in the built-in visual interface, but do not have support for communicating with database tables. We will organize this connection with OO Basic macros.
Let's start in order. Let's create two tables in the database:
products
- id (integer, primary key)
- name (string)
orders
- id (integer, primary key)
- productId (integer)
- count (integer)
- date (data type Date)
Create a orders form, add a Form element with the name orders to the form. As well as Table Control with orders data source. How to do this was discussed in Part 2 of a series of messages.
When setting the properties of the date column, please pay attention to the properties marked in the figure with arrows that allow you to display data in the desired format and use the drop-down calendar to select a specific date.
Now, right in the table, you can add and modify data. Until a certain point, this is convenient, similar to the usual work of a client with a table processor. But at some point, problems begin that are also characteristic of working with table processors. Data can be changed randomly without even noticing it. And having noticed it is impossible to roll back everything as it was. There is little space for new speakers, and unpleasant horizontal scrolling may form. The column headings also have a size limit (otherwise they will not fit horizontally), and this does not allow a detailed description of the data.
As a half measure, you can place regular fields for data entry next to the table (Text Control, Numeric Control, etc.). Set the corresponding fields of the orders table as a data source and everything will work. When navigating a dataset, the data in the input fields will change. When changing data in the input fields, the data in the database table will change. But this solution will not look very beautiful.
Therefore, we implement such functionality using Dialogs. Dialogs have one significant limitation. Dialog entry fields are not related to the database table. Therefore, filling in the Dialog entry fields from the database table, and saving the input fields to the database table will need to be done with the OO Basic macro. However, this is good, because allows you to consider OO Basic programming options (before that, my messages only described working with the environment, but all this was preparation for today's post).
To bring up the Dialog Editor, you must select Tools-> Macros-> Organize Macros-> LibreOffice Basic-> Organizer-> Dialog-> New | Edit | Delete from the menu. I would like to have a faster way to get to this editor. After that, the dialog editor (alas, not the most convenient) will open, in which we will create fields with the names of the fields in the database table. As you recall, Dialogs do not automatically bind to database tables, so we will write a macro to do this. And as a naming convention, let's define the names of the input fields and fields of the database table the same.
An editor with a finished form will look something like this:
Next, add two buttons to the Dialog. We will give the names of the buttons arbitrary, but starting with an underscore, to distinguish them from the database fields. Each button in the properties palette can be assigned an action. Let us set the action OK to the button - it closes the dialog with confirmation of the action. And the second - Cancel - it closes the dialog without confirming the action.
Close the Dialog editor, and return to the Form editor. Create a button that calls up the dialog and assign the Order_Edit handler procedure to it, in which we populate the Dialog from the database table FromBaseToDialog (oForm, oDialog) and save the data from the Dialog database table FromDialogToBase (oDialog, oForm).
It is assumed that the Dialog was saved in the Standard library as dialogOrder. Naturally, you can choose other names. Names of controls starting with underscores are skipped and not processed. Also, names beginning with Label, which are used for text field headers, are not processed.
Calling oDialog.Execute () = 1 displays the Dialog inside the Forms window and pauses the macro until the OK or Cancel button is clicked. When you click OK, the return value is equal to one.
Let's start in order. Let's create two tables in the database:
products
- id (integer, primary key)
- name (string)
orders
- id (integer, primary key)
- productId (integer)
- count (integer)
- date (data type Date)
Create a orders form, add a Form element with the name orders to the form. As well as Table Control with orders data source. How to do this was discussed in Part 2 of a series of messages.
When setting the properties of the date column, please pay attention to the properties marked in the figure with arrows that allow you to display data in the desired format and use the drop-down calendar to select a specific date.
Picture
Now, right in the table, you can add and modify data. Until a certain point, this is convenient, similar to the usual work of a client with a table processor. But at some point, problems begin that are also characteristic of working with table processors. Data can be changed randomly without even noticing it. And having noticed it is impossible to roll back everything as it was. There is little space for new speakers, and unpleasant horizontal scrolling may form. The column headings also have a size limit (otherwise they will not fit horizontally), and this does not allow a detailed description of the data.
As a half measure, you can place regular fields for data entry next to the table (Text Control, Numeric Control, etc.). Set the corresponding fields of the orders table as a data source and everything will work. When navigating a dataset, the data in the input fields will change. When changing data in the input fields, the data in the database table will change. But this solution will not look very beautiful.
Therefore, we implement such functionality using Dialogs. Dialogs have one significant limitation. Dialog entry fields are not related to the database table. Therefore, filling in the Dialog entry fields from the database table, and saving the input fields to the database table will need to be done with the OO Basic macro. However, this is good, because allows you to consider OO Basic programming options (before that, my messages only described working with the environment, but all this was preparation for today's post).
To bring up the Dialog Editor, you must select Tools-> Macros-> Organize Macros-> LibreOffice Basic-> Organizer-> Dialog-> New | Edit | Delete from the menu. I would like to have a faster way to get to this editor. After that, the dialog editor (alas, not the most convenient) will open, in which we will create fields with the names of the fields in the database table. As you recall, Dialogs do not automatically bind to database tables, so we will write a macro to do this. And as a naming convention, let's define the names of the input fields and fields of the database table the same.
An editor with a finished form will look something like this:
Picture
Next, add two buttons to the Dialog. We will give the names of the buttons arbitrary, but starting with an underscore, to distinguish them from the database fields. Each button in the properties palette can be assigned an action. Let us set the action OK to the button - it closes the dialog with confirmation of the action. And the second - Cancel - it closes the dialog without confirming the action.
Close the Dialog editor, and return to the Form editor. Create a button that calls up the dialog and assign the Order_Edit handler procedure to it, in which we populate the Dialog from the database table FromBaseToDialog (oForm, oDialog) and save the data from the Dialog database table FromDialogToBase (oDialog, oForm).
Sub Order_Edit(Event)
Dim oDialog As Object
Dim orders As Object
orders = Thiscomponent.DrawPage.Forms.GetByName("orders")
DialogLibraries.LoadLibrary("Standard")
oDialog = CreateUnoDialog(DialogLibraries.Standard.dialogOrder)
FromBaseToDialog(orders, oDialog)
If oDialog.Execute() = 1 Then
FromDialogToBase(oDialog, orders)
orders.UpdateRow()
End If
End Sub
Sub FromBaseToDialog(oForm, oDialog)
Dim I
Dim sName As String
For I = 0 To Ubound(oDialog.Model.ElementNames)
sName = oDialog.Model.ElementNames(I)
If Mid(sName, 1, 1) <> "_" And Mid(sName, 1, 5) <> "Label" Then
ODialog.GetControl(sName).SetText(oForm.Columns.GetByName(sName).String)
End If
Next I
End Sub
Sub FromDialogToBase(oDialog, oForm)
Dim I
Dim sName As String
For I = 0 To Ubound(oDialog.Model.ElementNames)
sName = oDialog.Model.ElementNames(I)
If Mid(sName, 1, 1) <> "_" And Mid(sName, 1, 5) <> "Label" Then
oForm.Columns.GetByName(sName).UpdateString(Trim(oDialog.GetControl(sName).GetText()))
End If
Next I
End Sub
It is assumed that the Dialog was saved in the Standard library as dialogOrder. Naturally, you can choose other names. Names of controls starting with underscores are skipped and not processed. Also, names beginning with Label, which are used for text field headers, are not processed.
Calling oDialog.Execute () = 1 displays the Dialog inside the Forms window and pauses the macro until the OK or Cancel button is clicked. When you click OK, the return value is equal to one.