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

English
#1

Opening disclaimer: I’m just a novice on Libroffice Base and SQL, so please be kind to me :wink:

I have the table “Project” that contains a/o the following datafields:

  • Budget
  • Adjusted Budget
  • Spent
  • Forecast
  • Balance

When saving a new record or a changed record I want the field Balance being updated.
I have created the following SQL that provides me with the desired result:

UPDATE “Project”
SET “Balance” =
CASE WHEN “Adjusted Budget” = 0 THEN “Budget” - “Spent” - “Forecast”
ELSE “Adjusted Budget” - “Spent” - “Forecast”
END

And I think that this translates to the following line in a Basic Macro:

sql = “UPDATE”“Project”“SET”“Saldo”" = ““CASE”” WHEN ““Bijgesteld Budget”” = ““0"” THEN ““Budget”” - ““Spent”” - ““Forecast”” ELSE ““Bijgesteld Budget”” - ““Spent”” - ““Forecast”” END”

However, I’m just not able to find a working method the have this SQL being executed when saving a new Project record or when saving changes to a record.

Please note that I am using a form to add/change/delete records to the table.

I’ve done a lot of searches in tutorials and on sites but haven’t been able to find the solution.

Can you please help me with that?

#2
  • First you need to write your Makro. For testing I suggest at first not to use the actual UPDATE, but show the command in a MsgBox.
  • Next you “bind” the macro to an Event in your form. Check the Navigator and properties of your form. You could use a button or execute, when a field is modified (property of the field) or when you jump to the next record - as you need.
  • When this works change from MsgBox to actually calling createStatement and executeUpdate(sql)
  • Check the result…

One additional hint: You may need to include a WHERE to change only one record…

#3

You will need the primary key from the just saved record. You could get this key from the form if you add the macro “Before record action…” to get the value for an already saved row, which has been updated.
It will be available by something like this:

GLOBAL stID AS STRING

SUB IDSave(oEvent AS OBJECT)
stID = ""
oForm = oEvent.Source
stID = oForm.getString(oForm.findColumn("ID"))
END SUB

If the row is new inserted there isn’t any value for ID available.
So you should get int “After record action…”. This is the code for HSQLDB

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 ""ID"" = '"+stID+"'"
oSQL_Statement.executeUpdate(stSql)
END SUB
#4

Thanks for the swift replies guys.

@RobertG I’ll try your suggested Macro’s and report back if it worked.

#5

@RobertG I get the error message “BASIC runtime error. Property or method not found: activeConnection.”… What do I need to do?

#6

Might be you haven’t started the macro by form event “After record action…”. So oForm isn’t really a form of a database document.

#7

I’m almost sure I did add the first macro to the “before record action” of the subform with which I add/change/delete “Project” records, and the second macro to the “after record action” of the same.
Does the fact that it is a “subform” maybe has anything to do with it?

I’m at work now, hence will do a check/double check tonight.

Thanks for your continued support!

#8

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()
#9

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.

#10

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?

#12

@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…

#14

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 :wink:

#17

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

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 :wink:

#22

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

#11

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.

#13

@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.

#15

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.

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.

#18

@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 :innocent: 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?

#19

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.

#20

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?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.