Equivalent of VBA "OldValue" attribute for form fields

GeertJanssens · January 11, 2019, 8:51pm

When using forms on VBA, each form field as an “OldValue” attribute which is set when the user changed the field, but the field is not yet committed to the database row. This allows scripts to do things based on this old value as well as on the new value entered by the user.

I’m looking for a similar attribute or a way to retrieve this value in some way in Libreoffice Base macros.

My use case is as follows:
I have a table with textual ID’s and there is a file on disk linked to each ID (the filename is that ID). However if the ID changes, the file should be renamed as well. And in order to rename a file, I need to know the old ID as well as the new ID.

But so far I haven’t found an attribute or function on a text field in LO that gives me access to this old ID. Is there a way ?

ajlittoz · January 11, 2019, 9:13pm

Unless I remember wrong, this seems to me a feature of the particular SQL language (the one used for the DB engine handling your data). The SQL language in Base is a somewhat “common factor” universal language capable of interfacing with any engine, thus removing all specific statements or variants. This means it is highly likely that the old value feature (I used it in a PostgreSQL DB, but not through Base) won’t be accepted if you begin to tweak your queries SQL. Moreover, that was in “trigger” and “function” blocks and I think Base allows you only to customise the SELECT statement for queries.

Ratslinger · January 12, 2019, 12:09am

@ajlittoz There is no SQL in Base. It does have an editor/interpreter which can be bypassed allowing any SQL to proceed to the database without examination. There may be instructions which just don’t work. One is returning in Firebird (also in PostgreSQL - haven’t tried this). Have used triggers with some DB’s in Base without problems.

ajlittoz · January 12, 2019, 6:57am

@Ratslinger: by SQL, I meant the editor because the GUI doesn’t allow to finely tune all query subtleties in the SELECT statement equivalent to GUI description. How do you completely bypass the built-in editor?

GeertJanssens · January 12, 2019, 10:08am

What I referring to was not an SQL feature. I have used MS Access in the past with several backends (the built-in mdb and with postgresql). The “OldValue” parameter was something Access tracked internally for form field objects. Luckily as @Ratslinger shows something similar can be achieved by directly querying the db columns for the form’s current row in LibreOffice

Ratslinger · January 12, 2019, 6:01pm

@ajlittoz In SQL view you can turn on Run SQL command directly either from toolbar icon or on the Edit menu. With it on, and depending on the DB, you can execute some statements other than a ‘Select’. SQL also runs directly when used in macros or on forms when content is SQL and/or Analyze SQL command property is NO. It is rare I use the GUI for queries - mostly in reference to questions here.

ajlittoz · January 12, 2019, 7:26pm

@Ratslinger: thanks

Ratslinger · January 12, 2019, 12:00am

Hello,

There are many differences between Access and Base. Although “OldValue” is not part of Base you can still obtain the data in the field before it is actually changed by using a macro attached to the Before updating event of the field. A simple example:

Sub BeforeUpdating
    Dim oForm     As Object
    Dim oColumn As Object
    oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM_NAME")
    oColumn = oForm.Columns
    Print oColumn.getByIndex(INDEX # Relative to 0).getString()  Rem Field being updated
End Sub

GeertJanssens · January 12, 2019, 9:54am

Great! That does help a lot.

Edit Initially this comment also contained a python based equivalent of @Ratslinger’s Basic function. I think it deserves to be in an answer of its own though as python is less well documented and it may get more attention in a separate answer. So I am moving this python snippet. As @Ratslinger’s answer was the one that helped me create the python version, I will keep this answer marked as the solution though.

GeertJanssens · January 22, 2019, 4:00pm

As I’m working in python and I find there are far fewer python examples than LibreOffice Basic examples, I will add my python solution here for others as well. This one is based on @Ratslinger’s solution, with a minor tweak as explained in the snippet’s coment lines:

def BeforeUpdating (*args):
    # XSCRIPTCONTEXT is a global variable passed on to scripts by LibreOffice
    oDoc = XSCRIPTCONTEXT.getDocument()
    oForm = oDoc.DrawPage.Forms.getByName("YOUR_INTERNAL_FORM_NAME")
    # Note 'oForm.Columns'/'oColumn.getByIndex(INDEX)' should work as well below
    # I use the alternative 'oForm.findColumn' as I find it easier
    # to remember a column name than a column index
    # Important caveat though:
    # oColumn.getByIndex(idx) expects an idx value that starts from 0
    # oForm.getString(idx) expects an idx value that starts from 1
    # oForm.findColumn("NAME") returns an idx valud that starts from 1
    colIdx = oForm.findColumn ("TABLE_COLUMN_NAME")
    print (oForm.getString(colIdx))