Copying Text Box Value to Table and Refreshing Table Control in LibreOffice Base

[goedible]

Hi all,

2Tables: PlotsForm (connected to ‘plots’ table) and PlotForm (‘plot’ table)
2Controls: PlotsTbl (PlotsForm) and PlotIDField (PlotForm)

‘plots’ table holds info + plot_id (pk) for each plot.

When I change records in the PlotsTbl control I want the value in the plots.plant_id column column to be copied to the first record of the only column in the table plot.plot_id.

Every-time I choose a new plot from the PlotsTbl control the first and only record in the PlotIDField should be changed so that queries can reference it and be able to see which is the currently selected plot_id and when I go to create a report it will create it based on the value in plot.plot_id.

Here is the database in dropbox in case that helps:
https://www.dropbox.com/scl/fo/wmbtn6o89ixk7swpet0e3/AAPw9J4s8nf0k6qAbeGdAMQ?rlkey=6s4holdg9jzsoanjj9yw5fn0e&st=coym4kbb&dl=0

[CRDF]

Hi.
Here unable to open the DB:

Please add a copy of the HSQLDB engine (hsqldb.jar) […]

[goedible]

@CRDF
to address

Please add a copy of the HSQLDB engine (hsqldb.jar) […]

Download all the files into a folder.
Open LANdpLAN.odb then tools>options>advanced>Class Path… path to hsqldb.jar

See the attached file list.

[CRDF]

Or simpler: you could copy tables plots + plot into an embedded, preferably Firebird, db.
Just this.
And we can test best way to do persistence of selected row id field…

[goedible]

I’ve been working with libre office base and its gui. I don’t know how to do what you are suggesting. I’m willing to learn if there is no other way and because I have a decent grasp on libreoffice base I’d rather do it there if it’s not to hard.

Jonathan Francoeur a.k.a. Kernel Hazelnut
Everything Edible Landscaping H.Q.
48.381980, -123.707835

Everything Edible Landscaping

250 642 0267
6411 Sooke Rd., Sooke, B.C., Canada, V9Z 0A8

[CRDF]

did an exercise here; but as I don’t have your form layout, it’s speculative…
[plot_id] persisted in table’s PLOT only (pk) ID field.
Say you have a TableControl for PLOTS.
Select the row. If update msg does not show up, insert cursor in the [plant_id] control and leave it (TAB or Enter).
Event = When losing focus

Sub PersistID(Evt As Object)
	REM DECLARE THE VARIABLES:
	Dim F As Object, ID As Object, Con As Object, Stmt As Object
	Dim sSQL As String, plot_id As String
	Dim resultado As Integer
	REM SET REFERENCES:
	ID = Evt.Source.Model
	F = Evt.Source.Model.Parent.Parent
	Con = F.ActiveConnection()
	Stmt = Con.createStatement()
	REM BUILD SQL UPDATE AND PERSIST Plot #:
	plot_id = CStr(ID.CurrentValue) ' plot id to persist 
	sSQL = "UPDATE ""PLOT"" SET ""ID"" = '" & plot_id & "'"
	resultado = Stmt.executeUpdate(sSQL)
	print resultado & " record(s) updated to " & plot_id
End Sub	

[goedible]

Thank you for trying. I got “BASIC runtime error.
Property or method not found: ActiveConnection.”

[CRDF]

ActiveConnection is the connection that your form that holds the controls – TableControl or text boxes – have to the DB.
Because of these things I suggested you to upload an embedded simplified version of your DB, with just what matters for the question.
Here in my “exercise” I have mine of course.
Tried to send a screen record .mkv but was not authorized

[CRDF]

@goedible

and then open the .odb

 
Here proofing in a dummy DB kindly sent by @cpb as a solution to

About the Filter Table Method

 

[goedible]

possibly this will solve the error:
*you need to tell the database where the files are: Go to Edit>Database>Properties: and change the file address to reflect your setup
for example:
hsqldb:file:////home/jonathan/Dropbox/LANdpLAN/LANdpLAN/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=c
becomes
hsqldb:file:////home/workaway/Dropbox/LANdpLAN/LANdpLAN/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=c

I look forward to trying the example you shared. Thank you

[CRDF]

@goedible

I look forward to trying the example you shared.

Here the DB modified for this thread.
Select a record and value in the id column is persisted in PLOT’s only field.
Rem: if msg does not show up, place cursor into field ID and leave it – TAB or Enter.
 
CopyingTB.odb (15.1 KB)

[goedible]

Thanks for trying again. I am not able to open it. I wonder what version Libre office it was created in? Here is what Chat GPT said:
" You’re running a newer LibreOffice version (7.6+) where embedded HSQLDB support was removed. That’s why “HSQLDB Embedded” doesn’t appear in the connection list anymore. LibreOffice 7.6+ only shows external connections (JDBC, Firebird, MySQL, etc.).

Your .odb file was made with an older version (6.x or earlier) that used embedded HSQLDB. That’s why you get:"

[CRDF]

CTBTT.odb (13.1 KB)

[goedible]

When I try to open CTBTT.odb and CopyingTB.odb I get these errors:

The connection to the data source “CTBTT” could not be established.
SQL Status: HY000

The connection to the external data source could not be established. No SDBC driver was found for the URL ‘sdbc:embedded:firebird’. at ./connectivity/source/commontools/dbexception.cxx:413
A connection for the following URL was requested “sdbc:embedded:firebird”.

[goedible]

Can you let me know what version Libre office this was made on?

Jonathan Francoeur a.k.a. Kernel Hazelnut
Everything Edible Landscaping H.Q.
48.381980, -123.707835

Everything Edible Landscaping

250 642 0267
6411 Sooke Rd., Sooke, B.C., Canada, V9Z 0A8

[Wanderer]

@goedible

Here is what Chat GPT said:
" You’re running a newer LibreOffice version (7.6+) where embedded HSQLDB support was removed.

That’s obviously nonsense, generated by an AI without the ability to write something simple like “I have no clue.”
.
The abillity to work with HSQLDB was not removed. There are versions - especially on linux - wich are installed without java etc.
.
There may be problems to use embedded and external HSQLDB at the same time/in the same configuration. Would need to read/google the details on this…

[robleyd]

ChatGPT says a lot of things. A lot of them also aren’t true. LLMs do not have a concept of “truth” as their entire function is to recomposite the words from their training data into decent-sounding word salad. Whether it was correct this time or not, I suggest you do not rely on ChatGPT or any other LLM for information, it will eventually mislead you.

[goedible]

I did:

sudo apt-get install libreoffice-sdbc-hsqldb

and it worked allowing me to open the tables once, but only once. After closing and reopening now I’m getting

The connection could not be established. The database was created by a newer version of LibreOffice. at ./connectivity/source/commontools/dbexception.cxx:41

and once again I can’t open the tables or the form but I was able to copy the macro

Option Explicit
Sub PersistID(Evt As Object)
	REM DECLARE THE VARIABLES:
	Dim F As Object, ID As Object, Con As Object, Stmt As Object
	Dim sSQL As String, plot_id As String
	Dim resultado As Integer
	REM SET REFERENCES:
	ID = Evt.Source.Model
	F = Evt.Source.Model.Parent.Parent
	Con = F.ActiveConnection()
	Stmt = Con.createStatement()
	REM BUILD SQL UPDATE AND PERSIST Plot #:
	plot_id = CStr(ID.CurrentValue) ' plot id to persist 
	sSQL = "UPDATE ""PLOT"" SET ""ID"" = '" & plot_id & "'"
	resultado = Stmt.executeUpdate(sSQL)
	print resultado & " record(s) updated to " & plot_id
End Sub	

[CRDF]

You should first check in another machine with an independent LO’s install.
 
If “plot_id” is numeric (INTEGER) no need for
CStr(ID.CurrentValue) and ’

Dim plot_id As Long ' or Integer
[...]
sSQL = "[...] SET ""ID"" = " & plot_id

 
By the way: no more need for -get.

[goedible]

CopyingTB.odb is doing what I want by copying the id to the correct place. Thank you!

I’d like to understand it better so that I can modify it and use it for my application. Some questions to help me understand:
When/how is it called to run? I was expecting to see it listed in the events tab tied to something like ‘when record change’.
I’ve added a table to the example that shows the plot_id on the plot table. I have to select the table and click refresh to see the change. Is there a way to automate this?