Update a table field, at table level (no query)

[JohnB47]

My system info is :

Version: 7.5.5.2 / LibreOffice Community
Build: caf8fe7424262805f223b9a233
Environment: CPU Threads: 4;OS: Windows 10.0 Build 19405
User Interface: UI render: Skia/Raster; VCL:win
Locale: en.GB (en_GB); UI: en-GB
Misc Calc: threaded

I seem to remember being able to do this in Access. Edit: just found a video describing how to add a Calculated Column in Access. That’s essentially what I want to do in Base.

If I have a Table with a date field called ‘D’ (DD/MM/YY) and another field called ‘DM’ (text), is there a way, each time a new record is created in that table, for field DM to be updated with the month part in field D.

So each time a new record is created and field D becomes, say, 23/08/23 then field DM automatically becomes August.

I’m talking about this happening at table level, no query or SQL needed. THanks.

[RobertG]

Depends on the database you use. LibreOffice Base isn’t a database. It’s the GUI for working with data of a database. This function you want isn’t implemented in GUI. So you would need code for the special database you are using. Updating a field depending on update of a whole row might not be a problem, but updating a field depending on only one field? I would solve such a problem in a form by macro.

[JohnB47]

Thank you. The database I’m using is HSQLDB Imbedded. I’ll add that to my system info for future posts.

Sorry if my post wasn’t clear. OK, it looks like there’s no equivalent to the Access Calculated Column method in the Base GUI.

It’s not that I want to update a field depending on update of a whole row. I want to take a Table and in every record have a field called, DM filled with the month name from the Field D in that record.

So if one record in a Table has the value 23/08/23 in Field D, I need to get August into Field DM and so on for each record. Field D is a Date field and DM is text. So the Table records would start looking like:

ID D DM
1 14/04/22
2 02/07/20

and would end up looking like:

ID D DM
0 23/08/23 August
1 14/04/22 April
2 02/07/20 July

Sorry, the post doesn’t keep those values in separate columns but I hope you get the idea.
I suppose I need to run some sort of UPDATE sql but I don’t have the knowledge to create that for this case. Can you help with that? Thanks.

[JohnSUN]

@JohnB47 I don’t know much about databases, but I sincerely want to help.
Do you think that if you combine this information

and this built-in function

you will get the desired result?

And one more question, just curiosity - why do you need to store two Augusts (April, July) in each record of the table?

[Villeroy]

You want to store month names that can be calculated from the date values in column “D”? Why that? Makes no sense to me.

[JohnB47]

Thanks. I’ll have a look at your suggestion. Not sure what you mean by two Augusts. Record ID=0 would have DM value as August, Record ID=1 would have DM as April, Record ID=2 would have July. Three different records and three different dates and month names.

[JohnB47]

Hmmm. I’m obviously not explaining this then. I was given some info (on this site?). I used it in one of my queries as the attached screenshot shows. Essentially I want to take the value produced by the fourth column in the query window (MONTHNAME( [Date] ) Alias MonthName) and have that added to the appropriate field MONTH in each record. At the moment the query is just showing that as an extra field in each resultant row. I suppose I need the equivalent in UPDATE code.

Does that explain it?

[JohnSUN]

@JohnB47

Not sure what you mean by two Augusts. Record ID=0 would have DM value as August

At the same time, the D in this entry contains 23 AUGUST 2023. Yes, yes - the D field of this entry already contains August and you can get it any time you want. Even just viewing the contents of the table in Base, you can right-click on the heading of the D field, choose Column Format and set the dates to be displayed as month names.

And if you are getting data from this table to work with using a query (and this is the right way, this is how databases work), then you can convert the D field to the name of the month using the method that you already know - adding to the query any of the functions from your screenshot. Honestly, storing duplicate information in table entries is not the best way to use computer resources - each text name of the month will take up extra space.

[Villeroy]

You would need to run the UPDATE command every time when an existing record has been changed. With a query you always get the right month name based on the information that is already stored in the date field.
Sometimes there are fairly good reasons* to store calculated values, but in this scenario I can not see any benefit.

[*] When looking up article prices from a simple price list to generate invoices, you may store the looked up price for each sale. Otherwise you would have to keep a price list with all prices of all times in order to look up the right prices at invoice date. The second solution is a bit tricky, but safer against manipulation.

[JohnB47]

OK, thanks for the replies everyone. Looks like I need to adjust my thoughts.

[RobertG]

And if you are looking special for English monthnames:

SELECT "ID", "D", MONTHNAME("D") AS "DM" FROM "tbl_Date"

You could input data and will get the name of the month in the table of the query.