[base] How to fill a field with string + other field + string

[RobBob]

Hi everyone,

I’m pretty new to working with databases but not to programming.

When I create a new record in a table using a form I want to automatically fill a field with a relative path to an image based on a field in the record I’m creating. That way I can display images automatically in a from based on the folder structure I’m using.

The folder structure is:

/test_db/photos/[ID]/full.jpg

The SQL code to do this is:

UPDATE "tbl_1" SET "path_photo" = 'photos/' || "ID" || '/full.jpg'

Where [path_photo] and [ID] are both fields in the [tbl_1] table.

When I run the code through the “Execute SQL Statement” tool it works perfectly.

I made this macro to do it for me when I change records:

Sub UpdateRecordPhotoPath
    Dim oStatement As Object
    Dim oResult As Object
    sql = "UPDATE ""tbl_1"" SET ""path_photo"" = 'photos/' || ""ID"" || '/full.jpg';"
    oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
    oStatement.execute(sql)
End Sub

I tried several options in the events tab of the Form Properties window:
Before submitting, Before/After record action, Before/After record change
But none have worked.

The ideal solution would result in real time updating of field and image control object assigned to that field. But I would also be very happy with a solution that updates the field after changing the record or if I have to press a specific button for the image to appear.

Any help would be greatly appreciated!

Thanks,
RobBob

P.S. I’m using Windows 11, LibreOffice version 24.8.5.2 the database is HSQLDB Embedded

[Villeroy]

You don’t need any macro to update the path. Just run the SQL code via Tools>SQL… and every will be fine.
If the paths (relative to the database document) are correct, and you bind a picture control to the “path_photo” field, the picture control should display the photo.

Alternatively, you can calculate the path instead of storing it in the database:

SELECT <some fields>, 'relative_path/'|| "file_name" as "pic path" FROM <somewhere>

[Gbp]

Change oStatement.execute(sql) for oStatement.executeUpdate(sql)