Sql to add the result of a calculation to a table when saving record

[RobertG]

@Qique

Does the fact that it is a “subform” maybe has anything to do with it?

No, it doesn’t make any difference.
You could get the connection to datasource in different ways. So try this, but first have a look if the macro is really started from form “After record action…”

 DIM oDatasource AS OBJECT
 DIM oConnection AS OBJECT
 oDatasource = thisDatabaseDocument.CurrentController
 IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
 oConnection = oDatasource.ActiveConnection()

This code should do the same as

oForm = oEvent.Source
oConnection = oForm.activeConnection()

[cpb]

the before record action event fires twice:
.
first the form controller
“org.openoffice.comp.svx.FormController”
.
second the database form
“com.sun.star.comp.forms.ODatabaseForm”
.
the form controller does not posses the method ‘findColumn’.
.
@Qique,
what you are proposing is impractical and violates the principles of normalisation i.e. do not store values which can be calculated.
.
imagine that you insert 10 records into your database and the first record includes a typo “Spent” = 20 when it should = 30.
result:
10 inaccurate records.
remember that your form will fire your macros every time you INSERT, UPDATE or DELETE a record.
how do you intend to correct these errors? do you have the code? is it practical?
.
sorry to be so unhelpful but it’s important to consider the negatives.

[Qique]

I’m so sorry… You were right, I switched both subroutines…

However, now I get the error: Property or method not found: findColumn

And checking if I understand it correctly: in your example I need to replace “ID” with the name of the column I use in table “Project” to store the automatically generated ID. In my case that is the column “ProjectID”. I’ve done that in both places (findcolumn statement and in the SQL. Correct?

Do I need to change anything else?

[RobertG]

@Qique : right, both should be “ProjectID” as autogenerated values.
To get only the one Service, which offers findColumn, take this:

IF oForm.supportsService("com.sun.star.form.component.DataForm") THEN
stID = oForm.getString(oForm.findColumn("ProjectID"))
END IF

As others mentioned here: First have a look if the values couldn’t be better calculated by a query. If you will change tables directly, not inside the form, the macro won’t recalculate the values, but a query will…

[Qique]

Thanks @RobertG - will make the adjustments and test again.

The whole thing is that record changes will be made only through the forms, hence the risk mentioned I see as minimum/none. Additionally, the whole thing will mainly be a tool used on-line walking through a portfolio on-line, making notes and remarks, entering critical items with actions, etc. Hence, there’s far more that only this “Project” table, but I don’t want to cloud any discussions/solutions with that

[Qique]

Thank you so much @RobertG - the calculation is providing me with the correct result in the database!

The only thing missing now is that it’s not showing on-screen through the form… It does when I close the form and re-open.

Is there a function/command with which I can ‘refresh’ the form after having completed the ‘save’ of the record.

Again, apologies for maybe asking for things that are obvious for skilled Libroffice Base users, but I’m still on a learning curve

[Wanderer]

@Qique

refresh’ the form after having completed the ‘save’ of the record.

See the following thread and look for Commit() and especially updateRow() at the end:

Reload a form completely using a macro

Testdb.odb (187.0 KB) I have a form in base that uses multiple display elements. It is refreshed / reloaded using a button that is part of the form structure. Works fine for the table and the subforms with text fields displaying some SQL results that are part of that form. Now I am trying to reload this form using a macro instead of asking the user to press the button. When I reload the form element that contains the table, the subform and the textfields using a macro only part of the form (t…

[UnklDonald]

Something to consider. In a normalized relational database values than can be calculated are NOT usually saved in a database field, they are calculated at runtime.

Saved values such as balances can result in auditing issues if any of the values used in the calculation are changed after that balance has been saved.

[Qique]

@UnklDonald and @cpb - Thank you both for the time and effort to react on my request. I really appreciate that and respect your opinion and Libroffice experience.

However, I may be asking something against data normalization, but still would like to make this work.
I’ve seen and used big and famous enterprise solutions (like Workday) use calculation fields just for the reason to be able to store simple calculations (like mine in this request) in the database just to prevent having to spread calculation logic over ‘display’ functions like screens, reports, dashboards, etc.

Knowing that Libroffice just not provides these type of fields, I still hope you or somebody is willing to help me get it right through the use of Macro/SQL?

Thanks in advance for that.

[Villeroy]

LibreOffice Base is not a database. It is a tiny addition to this office suite working with various databases. What Base is able to create from scratch is either HSQL 1.8 (2005) or a Firebird database or dBase.
With no Java support Base creates a dBase database.
With Java support Base creates a HSQLDB.
With experimental features turned on you get Firebird as an additional option.

@Qique

Knowing that Libroffice just not provides these type of fields

The capabilities of a database depend entirely on the database engine in use. If you find some database which supports the required field type together with a JDBC or ODBC driver, you might be able to connect this database with LibreOffice. I’ve never seen a field type which stores something like spreadsheet formulas. And yes, storing calculatale values calls for trouble.

[Qique]

@Villeroy - I also thank you for sharing your experience. Much appreciated. You help me understanding more and more about Libroffice, which I really appreciate as a free but elaborative solution. It’s great!

Please allow me to be stubborn in for the time being pursuing the route I’ve taken Whenever I run into the problems mentioned by you and the others I will gracefully acknowledge my mistake, and will try to walk the ‘save’ route!

The ‘unsave’ route is providing me with the desired result in the “database” now, the only thing missing is that I need something that helps me ‘refresh’ the information provided by the form. I sure hope that Libroffice has a save/native solution for that?

[Villeroy]

Base lets you log-in at your database if it is an external one. In case of embedded database, you are the one and only almighty user of your database without any log-in. In any case you will be dealing with a classical, relational SQL database.
Once there is some database visible and editable in your Base document window, you have forms as a user interface for writing relational data into the database. Forms allow you to edit one-to-one relations, one-to-many relations and many-to-many relations by means of a sufficient but still very limited toolset as it was typical for the desktop applications of the 90ies. The logical forms and form controls are the same as in StarOffice 5 around 1995. For output you have office documents (Writer and Calc) and the reports that are embedded in the Base document. That’s it. This is far away from being a mature database development suite and the API is a nightmare. You are all alone when you add your own code to your own database solution.
When executing some SQL command by code, you need to ensure that the same SQL command executes in Base’s SQL dialog or in the preferred database development suite of your choice (e.g. SQuirreL for JDBC connections). In case of SQL statements executed in the dialog or SELECT executed in direct SQL mode, the error messages come from the database application.
As a rule of thumb, office macros can not do anything that can’t be done on the user interface. Macros are not the silver bullet to heal design flaws.

[Qique]

Again @Villeroy, I really respect and appreciate your knowledge, expertise and your warnings. But like said, I’m very close to having it as I would like. The macro’s provided by @RobertG are giving me the desired result. The only thing missing is a ‘refresh’ of the subform that returns me to the record I’ve just been adding/changing…

Could you help me with that?

[Villeroy]

FIrst thing I would try is LibreOffice: XRowLocate Interface Reference

[Qique]

Thanks @Villeroy and @Ratslinger - sofar unfamiliar with ‘Firebird’ - never heard of it… At the moment at work, so will pick up tonight.

[RobertG]

var = oForm.getBookmark
loRow = oForm.getRow
oForm.reload
oForm.MoveToBookmark(var)
IF loRow = 0 THEN
	oForm.MoveToInsertRow()
END IF

Will run well here and move to new row if cursor has moved to new row.

[Ratslinger]

@Qique
Not to agree or disagree with your direction but why use macros? Can do with Query or different database.

Samples:
HSQLDB embedded using query → ComputeQuery.odb (12.9 KB)
.
Firebird embedded using computed column → ComputeFirebird.odb (15.1 KB)
.
Edit:

@Qique

sofar unfamiliar with ‘Firebird’ - never heard of it

It’s been around for some time. See:

Firebird (database server)

Firebird: The true open source database for Windows, Linux, Mac OS X and more

.
The embedded version is available within LO but experimental features must be turned on to create (can execute existing such as attached sample without enabling experimental features) a Firebird embedded database within Base. See → Cannot create a new database - need JRE - #4 by Ratslinger

[Qique]

With some great additional help of @RobertG (through DM) I have the solution as I was looking for working as required. Hence I will continue this way for now. I have stored the links and information as provided by @Ratslinger and when I run into issues with my Libroffice solution, I can pick this up.

I would like to thank everybody for your time and effort to support me. Really appreciated.

Topic can be closed from my point of view. Cheers!

[Villeroy]

Would you please share the solution?

[Qique]

@Villeroy et all, sorry, sure. See below. All credits to @RobertG

Macro to be used in the ‘before record action’ of my form:

SUB IDSave(oEvent AS OBJECT)
	stID = ""
	oForm = oEvent.Source
	IF oForm.supportsService("com.sun.star.form.component.DataForm") THEN
		stID = oForm.getString(oForm.findColumn("ProjectID"))
		loRow = oForm.getRow
	END IF
END SUB

And in the ‘after record action’ of my form:

SUB IDGet(oEvent AS OBJECT)
	oForm = oEvent.Source
	oConnection = oForm.activeConnection()
	oSQL_Statement = oConnection.createStatement()
	IF stID = "" THEN
		stSql = "CALL IDENTITY()"
		oResult = oSQL_Statement.executeQuery(stSql)
		WHILE oResult.next
			stID = oResult.getString(1)
		WEND
	END IF
	stSql = "UPDATE ""Project"" SET ""Saldo"" = CASE WHEN ""Bijgesteld Budget"" = 0 THEN ""Budget"" - ""Spent"" - ""Forecast"" ELSE ""Bijgesteld Budget"" - ""Spent"" - ""Forecast"" END WHERE ""ProjectID"" = '"+stID+"'"

	oSQL_Statement.executeUpdate(stSql)
	oForm.reload
	IF loRow > 0 THEN
		oForm.absolute(loRow)
	ELSE
		oForm.last
	END IF
END SUB

And as ‘GLOBAL’ declaration (is that the right term) of variables within my ‘Main’ macro file:

GLOBAL stID AS STRING
GLOBAL loRow AS LONG

The above results both in calculating the 'Saldo" (= Balance), storing it in the ‘Saldo’ field of my ‘Project’ table when I save the record, as in showing me the updated ‘Saldo’ field on the screen (form) by returning to the new or changed record (row) of the ‘Project’ table.