Automatic Insertion

[Renel]

I have a form called “frm-Viewing”. This contains a control table named “cntr-Viewing”. This control table contains columns whose information comes from the following SQL query which is the data source of the form.

SELECT “FilmSeriesID”, “EnglishTitle1”, “Viewing”, “EpisodeNumber”, “NumberOfDays”, “UPDate”
FROM “TFilmsSeries”
WHERE “Viewing” = FALSE OR “Viewing” IS NULL

So, the control table has the following columns: “FilmSerieID”, “EnglishTitle1”, “Viewing”, “EpisodeNumber”, “NumberOfDays”, “Update”

I would like that when I enter a value in the “NumberOfDays” column of the control table, the corresponding “UPDate” column in the “TFilmsSeries” table is automatically updated according to the current date and the number of days entered.

I can’t understand how to achieve this, what method should I use to achieve this result, can this be done with SQL or does it require macros (Basic)?

Can anybody help me?

[RobertG]

Take a procedure like this:

SUB TimeStamp_BeforeUpdate(oEvent)
    REM Form Properties → Events → Before record action
    DIM oForm AS OBJECT
    oForm = oEvent.Source
    IF hasUnoInterfaces(oForm, "com.sun.star.form.XForm" ) THEN
        oForm.updateTimestamp(oForm.findColumn("Update"), CDateToUnoDateTime(Now))
    END IF
END SUB 

Save it inside the database file. Open the form for editing. Click on the control and open the form properties. Connect the procedure to Form Properties → Events → Before record action

Following function isn’t available in all databases. This code is for Firebird and could be set in Tools → SQL

CREATE OR ALTER TRIGGER BEFORE_IN_UP_SERIES FOR "TFilmsSeries"
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
 NEW."Update" = CURRENT_TIMESTAMP;
END;

[Renel]

This procedure works well with the right event. The “TFilmsSeries” table updates without problem after saving.
.
However, its current role is to enter the current date regardless of the number entered and recorded in the “NumberOfDays” column. Could this take into account the number entered as the number of days to add to the current date?
.
For example :

Today’s date + Number of days = New date to enter in the “Update” field.
.
And in the case where I empty the “NumberOfDays” cell, is that to update “UpdatedDate” is done using NULL instead of NOW.
.
There is a lot of newness for me in this. I’m using LibreOffice Base HSQLBD Embedded Version: 7.5.3.2 (X86_64). Can the SQL Trigger statement be executed in the context of a BASE document. I think I need a teacher here…

[RobertG]

Might be this is working well for you:

SUB TimeStamp_BeforeUpdate(oEvent)
    REM Form Properties → Events → Before record action
    DIM oForm AS OBJECT
    DIM inNumberOfDays AS INTEGER
    DIM daNew AS DATE
    oForm = oEvent.Source
    IF hasUnoInterfaces(oForm, "com.sun.star.form.XForm" ) THEN
        inNumberOfDays = oForm.getInt(oForm.findColumn("NumberOfDays"))
        IF inNumberOfDays = 0 THEN
            oForm.updateNULL(oForm.findColumn("Update"), NULL)
        ELSE
            daNew = DateAdd("d", inNumberOfDays, Now())
            oForm.updateTimestamp(oForm.findColumn("Update"), CDateToUnoDateTime(daNew))
        END IF
    END IF
END SUB 

Haven’t tested it before. I would have a look at the code and test it here if it doesn’t work for you. Might be NULL won’t be needed in oForm.updateNULL …

Code for the trigger in my first post only works with Firebird - also an internal database of LibreOffice, but could only created new if you turn experimental features on for creating the database.

[Renel]

@RobertG,
.
Your procedure works almost perfectly. The entry of the number of days is taken into account and the calculation on the date is done correctly. If I empty the “NumberOfDays” cell, the “Update” field is also empty. The “TFilmsSeries” table updates correctly too.
.
However, this error occurs every time I enter a new number:
.
BASIC runtime error.
Property or method not found: findColumn.
.
The “NumberOfDays” column is of type TextBox, so I thought that might be the cause of the error, so I changed ‘getInt()’ to ‘getStting()’, but that didn’t work, nothing to see. There appears to be no reference to this property in the oForm object.

[RobertG]

@Renel

Property or method not found: findColumn.

Seems it couldn’t find the method findColumn when creating a new row. Could you upload an example for this so I could reproduce. If there are personal data remove this data in the file you upload.

[Renel]

@RobertG,
.
There is nothing personal in this database. I enjoy designing it in my free time. I find it very personally useful, but if it can be useful for others, why not share it.
.
The form I’m currently working on is ‘Visionnements’. It is in the process of creation. However, before going any further, this problem needs to be resolved and I believe you can help me.

BD Films & Séries Coréennes - Test.odb (722.9 KB)

[RobertG]

@Renel : Have changed the code in my second post… findColumn is only available for “com.sun.star.form.XForm”. So

inNumberOfDays = oForm.getInt(oForm.findColumn("NumberOfDays"))

must be positioned inside the IF-condition.

[Renel]

@RobertG
.
I’m really sorry, it’s my mistake. I haven’t taken the time to look at all this carefully. Ultimately, everything works very well.
.
Thank you a lot. This is not the first time you have helped me and I greatly appreciate this help.
.
Each time, I discover features that I didn’t know about, like HasUnoInterfaces(). Even though I have read a lot of documents, as well as the LibreOffice help, I can’t make all the links, there are so many things. This is why this site and knowledgeable people like you are invaluable.
.
THANKS!