[Solved] Automatic timestamp assignment how?
[Solved] Automatic timestamp assignment how?
Hey guys,
I would like to have a timestamp value in my table and I would like it to be automatically assigned value at the time of record creation and/or edit... it shouldn't even appear in the form... kinda like the primary ID key...
How would I go about acomplishing said task? Cuz I have no idea...
I would like to have a timestamp value in my table and I would like it to be automatically assigned value at the time of record creation and/or edit... it shouldn't even appear in the form... kinda like the primary ID key...
How would I go about acomplishing said task? Cuz I have no idea...
Last edited by NiksaVel on Sat May 10, 2008 10:56 pm, edited 1 time in total.
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Re: Automatic timestamp assignment how?
try the SEARCH function - there has been posts on this topic.
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Automatic timestamp assignment how?
Well to be fair only half of the question has been answered before.
However that does not help with the second half of the question, " and/or edit".
There are really only two ways to go with that. The first and more common way is with a trigger. I say that is more common for database design overall, but with the Base file using HSQLdb this means you must write the trigger with Java.
The second way then, and more common way with Base, is to create a script that updates the field and call that script from any form that updates the record. This allows you to use basic to write the script - but there is a trade off. Using this script it only works when updating the record from a form and only when you remember to link it into the form. If you just open the table in a dataview window ( double click the table in the table view of the base file in other words, or run a query that displays the table and update it the query designer ) there is no way to call the script.
In other words using java to create a trigger you have the advantage that no matter how the record is updated, a dataview window, a query window, a form or even with a SQL Update statement in the SQL window the timestamp is guaranteed to be set to the current date time.
The question then is - Java or Basic? Let me know and I'll cover either one.
Looking at this thread http://user.services.openoffice.org/en/ ... f=13&t=573 you will see how to assign a default value to a timestamp field and that will take care of the "assigned value at the time of record creation" half of the question.NiksaVel wrote:Hey guys,
I would like to have a timestamp value in my table and I would like it to be automatically assigned value at the time of record creation and/or edit... it shouldn't even appear in the form... kinda like the primary ID key...
However that does not help with the second half of the question, " and/or edit".
There are really only two ways to go with that. The first and more common way is with a trigger. I say that is more common for database design overall, but with the Base file using HSQLdb this means you must write the trigger with Java.
The second way then, and more common way with Base, is to create a script that updates the field and call that script from any form that updates the record. This allows you to use basic to write the script - but there is a trade off. Using this script it only works when updating the record from a form and only when you remember to link it into the form. If you just open the table in a dataview window ( double click the table in the table view of the base file in other words, or run a query that displays the table and update it the query designer ) there is no way to call the script.
In other words using java to create a trigger you have the advantage that no matter how the record is updated, a dataview window, a query window, a form or even with a SQL Update statement in the SQL window the timestamp is guaranteed to be set to the current date time.
The question then is - Java or Basic? Let me know and I'll cover either one.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Automatic timestamp assignment how?
Thanks a lot for the informative reply! This is my first attempt at working with a database in general so I REALLY REALLY appreciate it
As for the second part, I never really worked with Java, and editing ONLY with forms is the way I plan to go, so if you would be so kind as to explain the Basic version that would be nice enough for me
As for the second part, I never really worked with Java, and editing ONLY with forms is the way I plan to go, so if you would be so kind as to explain the Basic version that would be nice enough for me
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Re: Automatic timestamp assignment how?
NiksaVel wrote:Thanks a lot for the informative reply! This is my first attempt at working with a database in general so I REALLY REALLY appreciate it
As for the second part, I never really worked with Java, and editing ONLY with forms is the way I plan to go, so if you would be so kind as to explain the Basic version that would be nice enough for me
With a mysql database, you can use a field definition like the following in your table creation statement that will automatically insert a timestamp value on creation of a new record, and then automatically update the timestamp to current date/time each time the record is modified :
`chg_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
Unfortunately, HSQLDB is rather lacking in easily accessible (i.e. common or garden user accessible) date/time functions. As Drew has said, you need to be able to program in Java or OOoBasic to get this functionality which adds a level of complexity.
Alex
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Automatic timestamp assignment how?
Alright so the basic script for this.
You have a few choices here. Choice one include your datetime field in the query that you build your form on, either displayed or not, of course if your form is based on the actual table this is what you have.
Choice two do not include the datetime field in the query.
I never quite know where to start on this.
I suppose just jump in with an example:
Attached to this post you will find a database file named "current_timestamp.odb".
It has one table, Table1
ID IDENTITY Primary Key, VAL_1 VARCHAR(50), LastEdit Timestamp
The command ALTER TABLE "Table1" ALTER COLUMN "LastEdit" SET DEFAULT CURRENT_TIMESTAMP was applied from the SQL window.It has one form, Table1 - based on the table Table1.
The form displays a grid with 3 columns - Val_1, LastEdit(Date), LastEdit(Time)
The ID field is hidden by default, LastEdit(Date), LastEdit(Time) are read only.
At this point you can open the form add a record by entering some data in Val_1 and you will see that when the record is written to the table the LastEdit fields are automatically filled with the current date and time. OK - you should have grabbed a copy of that database.
Now at this point if you edit an existing record the LastEdit value does not change, so that is what you will add now.
The first thing to do is to create our script. Open the database on your desktop. From the main window select 'Tools > Macros > Organize macros > OpenOffice.org Basic'
You will want to put your script ( macro ) in the standard library under 'Your Macros'
I want you to select "Module1"under "Standard" and click on 'Edit'.
In the Basic IDE window that opens you copy the following script ( macro )
You can close the Basic IDE.
Now from the Base window open the form 'Table1" for editing. Right click on the form, select edit.
With the form open for edit ( design mode on ) you can assign the macro.
To do this you want to open the form navigator - this is on the toolbar 'Form Design'. Notice that I have highlighted the entry "MainForm". This is the hidden dataform that is the link between the data table ( or query ) and the form controls. It is where we want to assign the macro. So right mouse click on this and select 'Properties'.
On the property editor select the Events tab.
You will find an event 'Before Record Actrion', click on the button just to right of this field.
Now browse to 'My Macros', Standard, Module1 and select the macro onUpdateRecord.
Close the forum and save the database.
Your done.
Try it out, you should be able to add a record in the form, the timestamp is set - edit a record and it changes to the current date time also.
What does the code mean - line for line...well, first you could check out the Basic Programmers Guide. You can find a reference to that at the top of the Macros forum on this site.
or
If you are going to need to do this on more then one form, maybe I could show you how to make that macro a bit more generic and dissect the lines as we go.
Drew
You have a few choices here. Choice one include your datetime field in the query that you build your form on, either displayed or not, of course if your form is based on the actual table this is what you have.
Choice two do not include the datetime field in the query.
I never quite know where to start on this.
I suppose just jump in with an example:
Attached to this post you will find a database file named "current_timestamp.odb".
It has one table, Table1
ID IDENTITY Primary Key, VAL_1 VARCHAR(50), LastEdit Timestamp
The command ALTER TABLE "Table1" ALTER COLUMN "LastEdit" SET DEFAULT CURRENT_TIMESTAMP was applied from the SQL window.It has one form, Table1 - based on the table Table1.
The form displays a grid with 3 columns - Val_1, LastEdit(Date), LastEdit(Time)
The ID field is hidden by default, LastEdit(Date), LastEdit(Time) are read only.
At this point you can open the form add a record by entering some data in Val_1 and you will see that when the record is written to the table the LastEdit fields are automatically filled with the current date and time. OK - you should have grabbed a copy of that database.
Now at this point if you edit an existing record the LastEdit value does not change, so that is what you will add now.
The first thing to do is to create our script. Open the database on your desktop. From the main window select 'Tools > Macros > Organize macros > OpenOffice.org Basic'
You will want to put your script ( macro ) in the standard library under 'Your Macros'
I want you to select "Module1"under "Standard" and click on 'Edit'.
In the Basic IDE window that opens you copy the following script ( macro )
Code: Select all
Sub onUpdateRecord( oEvent as object )
dim oDataForm as variant
dim oStatement as variant
dim oResultSet as variant
if oEvent.Source.SupportsService( "com.sun.star.form.component.DataForm" ) then
if oEvent.Action = com.sun.star.sdbc.ChangeAction.UPDATE then
oDataForm = oEvent.Source
oStatement = oDataForm.ActiveConnection.CreateStatement
oStatement.EscapeProcessing = False
oResultSet = oStatement.executequery( "CALL CURRENT_TIMESTAMP" )
oResultSet.next
oDataForm.updateTimeStamp( oDataForm.FindColumn( "LastEdit" ), oResultSet.getTimestamp( 1 ) )
end if
end if
End Sub
Edit: Corrected typo and added explicit call to turn escape processing off |
Now from the Base window open the form 'Table1" for editing. Right click on the form, select edit.
With the form open for edit ( design mode on ) you can assign the macro.
To do this you want to open the form navigator - this is on the toolbar 'Form Design'. Notice that I have highlighted the entry "MainForm". This is the hidden dataform that is the link between the data table ( or query ) and the form controls. It is where we want to assign the macro. So right mouse click on this and select 'Properties'.
On the property editor select the Events tab.
You will find an event 'Before Record Actrion', click on the button just to right of this field.
Now browse to 'My Macros', Standard, Module1 and select the macro onUpdateRecord.
Close the forum and save the database.
Your done.
Try it out, you should be able to add a record in the form, the timestamp is set - edit a record and it changes to the current date time also.
What does the code mean - line for line...well, first you could check out the Basic Programmers Guide. You can find a reference to that at the top of the Macros forum on this site.
or
If you are going to need to do this on more then one form, maybe I could show you how to make that macro a bit more generic and dissect the lines as we go.
Drew
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Automatic timestamp assignment how?
thanks a lot for the input! I'll play around with this when I get home from work
really appreciate it..
really appreciate it..
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Re: Automatic timestamp assignment how?
Very nice!!
I've just started using OpenOffice 2.3.1 20 minutes ago, and this page explained exactly what I needed; the reason I switched away from M$ Access.
However, I have one more question in this matter:
How can I make Base "skip" those date- and time-fields when the user is entering data ?
I need to create an automated database. Which I find is quite easy, except for the matter of skipping those 2 fields.
The user don't need them; but they need to exist. And the user should not be bothered by having to press Enter 2 times (or down-arrow).
I need to create a table with 3 or 4 fields, plus those date/time-fields.
I want Base to automatically jump to the next record, when those 3 or 4 fields has been entered - the user should never have to bother about the date and time; not even have to skip them. This should be fully automatic.
*NOTE*: I am not experienced with SQL. I prefer to script whatever can be scripted.
I've just started using OpenOffice 2.3.1 20 minutes ago, and this page explained exactly what I needed; the reason I switched away from M$ Access.
However, I have one more question in this matter:
How can I make Base "skip" those date- and time-fields when the user is entering data ?
I need to create an automated database. Which I find is quite easy, except for the matter of skipping those 2 fields.
The user don't need them; but they need to exist. And the user should not be bothered by having to press Enter 2 times (or down-arrow).
I need to create a table with 3 or 4 fields, plus those date/time-fields.
I want Base to automatically jump to the next record, when those 3 or 4 fields has been entered - the user should never have to bother about the date and time; not even have to skip them. This should be fully automatic.
*NOTE*: I am not experienced with SQL. I prefer to script whatever can be scripted.
Last edited by Ubehage on Mon Jan 28, 2008 11:58 am, edited 1 time in total.
Re: Automatic timestamp assignment how?
I didn't even create the form entry for timestamp - after using the sql command provided there the database does that automatically as soon as the record has been entered - the user never sees anything time related, until the query/report gets generated. Very nice
OOo 3.1.X on Ubuntu 8.x + Linux Mint 7, Win XP, Vista
Re: Automatic timestamp assignment how?
Thismacro fails on my XP under 2.3.0 and OOH m4 with same error: Basic runtime error, Object variable not set, and stops on oResultSet.
I did something wrong?
I did something wrong?
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Automatic timestamp assignment how?
Are you using a Base file with an embedded HSQLdb database?
The macro as it stands is using a fairly standard SQL statement and CURRENT_TIMESTAMP is part of the SQL standard, but not all databases follow the standard.
You can use a OOBasic funciton such as now() instead, but then you need to convert the basic time variable type to the Base timestamp struct type. That would work across all database types however. My preference is to ask the database engine for the timestamp but it is preference only.
The macro as it stands is using a fairly standard SQL statement and CURRENT_TIMESTAMP is part of the SQL standard, but not all databases follow the standard.
You can use a OOBasic funciton such as now() instead, but then you need to convert the basic time variable type to the Base timestamp struct type. That would work across all database types however. My preference is to ask the database engine for the timestamp but it is preference only.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Automatic timestamp assignment how?
I tried on example file: current time_stamp.odt.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Automatic timestamp assignment how?
well - just tired it with 680M4 also - nope it fails.
It works under 2.3.1.
Anyway I know why, need to add a line to the macro to force the Base query parser off.
Give me a second and I'll update the macro in the original post.
It works under 2.3.1.
Anyway I know why, need to add a line to the macro to force the Base query parser off.
Give me a second and I'll update the macro in the original post.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Automatic timestamp assignment how?
OK - fixed the actual macro - there was a typo and I added the line to set escape processing FALSE.
Tested it under 680M4, XP no problem any longer.
Tested it under 680M4, XP no problem any longer.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Automatic timestamp assignment how?
Just working on 2.3.0, too.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: [Solved] Automatic timestamp assignment how?
Ok, i am a newbie, so it is very well possible that i am totally wrong here, but i just tried DrewJensen example and it seems that it doesn't need a macro to work with the timestamp. Maybe applying the sql statement SET DEFAULT CURRENT_TIMESTAMP was enough to do the trick. Is this true or am i overlooking something? It worked for me without a macro...
I was looking into this, because i wanted to see how a macro would work. I am working on a base front end for a mysql database. The database is used on different places, with different front ends. Mostly PHPmyadmin. That's the reason why the guy who asked me said he would be very happy if i didn't change anything on the mysql database to get things working on openoffice base. On the mysql table there is a timestamp column with the attribute 'On update current timestamp', no null value allowed and default is current timestamp. When i try to add a new record in base i get troubles because mysql doesn't allow null values in the timestamp column. Changing the the column to allow null values solves the problem, (mysql adds the systemdate fine) but as stated above i would like to avoid changing values in mysql. So i hoped it might be possible to avoid this, when i can make base enter the system time/date, when a record is saved. That's where i thought a macro could be usefull. Any ideas on this?
BTW I am not a coder, but the code and explanations on this forum are often so good and easy to understand that i have been very successful in editing other macros and adjusting them to my own needs. Big thanks to DrewJensen and others for all their useful posts.
I was looking into this, because i wanted to see how a macro would work. I am working on a base front end for a mysql database. The database is used on different places, with different front ends. Mostly PHPmyadmin. That's the reason why the guy who asked me said he would be very happy if i didn't change anything on the mysql database to get things working on openoffice base. On the mysql table there is a timestamp column with the attribute 'On update current timestamp', no null value allowed and default is current timestamp. When i try to add a new record in base i get troubles because mysql doesn't allow null values in the timestamp column. Changing the the column to allow null values solves the problem, (mysql adds the systemdate fine) but as stated above i would like to avoid changing values in mysql. So i hoped it might be possible to avoid this, when i can make base enter the system time/date, when a record is saved. That's where i thought a macro could be usefull. Any ideas on this?
BTW I am not a coder, but the code and explanations on this forum are often so good and easy to understand that i have been very successful in editing other macros and adjusting them to my own needs. Big thanks to DrewJensen and others for all their useful posts.
Libre Office on Ubuntu 12.10
Re: [Solved] Automatic timestamp assignment how?
I'm curious about how to implement the java trigger to update the timestamp field.
OOo 3.0.X on Mac OSx Leopard + Windows XP, Ubuntu
Re: [Solved] Automatic timestamp assignment how?
You will get more response if you start a new thread. This thread has been marked as 'Solved' so a lot of people might not even open it to read new posts.
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Gurkha Welfare Trust
Re: [Solved] Automatic timestamp assignment how?
Just to update this excellent example by DrewJensen (above) using the latest, macro-storage location added with OOo 3.1
Note: Moved the original code to the database document for inclusion in the .odb file.
Note: Just for variation, this implementation is actually using a second macro which takes the Timestamp from the local system 'Now()'. This requires a conversion factor (at least on my Win7 64-bit system). I used 'Now-2', which is the current timestamp (as a Double data-type) minus 2 (days) -- for whatever reason -- which results in the current day.
Note: Moved the original code to the database document for inclusion in the .odb file.
Note: Just for variation, this implementation is actually using a second macro which takes the Timestamp from the local system 'Now()'. This requires a conversion factor (at least on my Win7 64-bit system). I used 'Now-2', which is the current timestamp (as a Double data-type) minus 2 (days) -- for whatever reason -- which results in the current day.
- Attachments
-
- Current_timestamp2.odb
- uses a better Basic to SQL Timestamp conversion than the original below
- (14.91 KiB) Downloaded 806 times
-
- Current_timestamp (updates with macro).odb
- (14.72 KiB) Downloaded 987 times
Last edited by DACM on Wed Nov 16, 2011 7:13 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Re: [Solved] Automatic timestamp assignment how?
Here is a much easier method. I've tested this with HSQLDB 2.2.5. Not sure if it runs with the version of HSQLDB that comes with Base [edit: it looks like it doesn't].
(1) To update a timestamp column automatically whenever a row is updated, all you need to do is execute the following SQL statement:
CREATE TRIGGER MyTableUpdateDateTrigger BEFORE UPDATE ON "MyTable"
REFERENCING NEW ROW AS newrow OLD ROW AS oldrow
FOR EACH ROW
BEGIN ATOMIC
SET newrow."MyDateTimeColumn" = CURRENT_TIMESTAMP;
END
Just replace all the names starting with "My" with your own names. This creates a trigger that will run just before any update to your table is done and set the timestamp appropriately.
The above SQL is written in a general way so it's easy to edit: You can access the old version of the row through "oldrow" and the new version through "newrow", and you can add extra statements between BEGIN ATOMIC and END to do other stuff if desired. You can also add a WHEN clause to update only when certain conditions are met. See the Triggers section of the HyperSQL User Guide.
Some other notes:
(2) If you make a mistake and want to get rid of the trigger, run:
DROP TRIGGER MyTableUpdateDateTrigger
(3) As others have posted: to set only the default value of a timestamp column, run:
ALTER TABLE "MyTable" ALTER COLUMN "MyDateColumn" SET DEFAULT CURRENT_TIMESTAMP
If you want, you could create two columns, one for the date & time the record was created with default value as set in (3); and another with the date & time the record was last updated, updated by the trigger above in (1).
Hope this is useful!!
(1) To update a timestamp column automatically whenever a row is updated, all you need to do is execute the following SQL statement:
CREATE TRIGGER MyTableUpdateDateTrigger BEFORE UPDATE ON "MyTable"
REFERENCING NEW ROW AS newrow OLD ROW AS oldrow
FOR EACH ROW
BEGIN ATOMIC
SET newrow."MyDateTimeColumn" = CURRENT_TIMESTAMP;
END
Just replace all the names starting with "My" with your own names. This creates a trigger that will run just before any update to your table is done and set the timestamp appropriately.
The above SQL is written in a general way so it's easy to edit: You can access the old version of the row through "oldrow" and the new version through "newrow", and you can add extra statements between BEGIN ATOMIC and END to do other stuff if desired. You can also add a WHEN clause to update only when certain conditions are met. See the Triggers section of the HyperSQL User Guide.
Some other notes:
(2) If you make a mistake and want to get rid of the trigger, run:
DROP TRIGGER MyTableUpdateDateTrigger
(3) As others have posted: to set only the default value of a timestamp column, run:
ALTER TABLE "MyTable" ALTER COLUMN "MyDateColumn" SET DEFAULT CURRENT_TIMESTAMP
If you want, you could create two columns, one for the date & time the record was created with default value as set in (3); and another with the date & time the record was last updated, updated by the trigger above in (1).
Hope this is useful!!
Last edited by eyesopen on Fri Oct 21, 2011 7:41 pm, edited 1 time in total.
LibreOffice 3.4.3, Mac OS X, Linux
HSQLDB 2.2.5
HSQLDB 2.2.5
Re: [Solved] Automatic timestamp assignment how?
No, OOo use HSQLDB 1.8.0.10.eyesopen wrote:Here is a much easier method. I've tested this with HSQLDB 2.2.5. Not sure if it runs with the version of HSQLDB that comes with Base.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: [Solved] Automatic timestamp assignment how?
Not sure what you mean, but looking at the docs for HSQLDB 1.8 it doesn't look like you can do this. However, for 2.2 it works very well!r4zoli wrote: No, OOo use HSQLDB 1.8.0.10.
Chris
LibreOffice 3.4.3, Mac OS X, Linux
HSQLDB 2.2.5
HSQLDB 2.2.5
Re: [Solved] Automatic timestamp assignment how?
It works in HSQLDB 2.2, but OOo Base use HSQLDB 1.8.0.10, as built in database server.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: [Solved] Automatic timestamp assignment how?
Thank you Chris for the SQL Procedure.
Here's some considerations: http://user.services.openoffice.org/en/ ... 27#p195427
.
Here's some considerations: http://user.services.openoffice.org/en/ ... 27#p195427
.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
-
- Posts: 1
- Joined: Tue Jul 08, 2014 11:18 am
Re: [Solved] Automatic timestamp assignment how?
Hi I'm sure I'm going to here asked and answered and get links, I have been at this for 4 days now. I've taugh myself the basics of sql in a weekend. not bad for someone that works on car all day. I'm trying to build a database that will hold 5 years work of invoices from the previous owner of my shop so I can have someone start marketing to them. I have most of it written, but my only problem is when I go to set up the macros or triggers I can get the auto update on create and edit to work in the form when I have it in the list view, but when I go to use the block style in the form it takes a crap on me. I have written one table the has a unique constraint for the first and last name. now I'm trying to write the next set of lines to auto fill and update atleast the date everytime a record is changed or created. I would like the time stamp too, but I can live without. I know where to put in the macros but I don't know exactly where to put in the triggers. if anyone can help I'm going to attach what I have done so far. I made a zip file with 7zip with all the info im working with thank you
apache openoffice 4.1.0 and win 7 ultimate
Re: [Solved] Automatic timestamp assignment how?
1) for reference activation:
in A1 formula put:
or even
then, if u modify B1 cell, A1 will get permanent timestamp
u must put also the following module into standard macro section (e.g. in document space):
2) for global macro triggering:
It would be better, more universal option, but there is no easy implementation of cell_onChange (as easy as in excel). It is longer, becouse it double checks if timestamp wasnt allready put
insert this into standard macro section:
And bind it to button
in A1 formula put:
Code: Select all
=PUTTIMESTAMP("B1")
Code: Select all
=IF(B1<>"";PUTTIMESTAMP();"")
u must put also the following module into standard macro section (e.g. in document space):
Code: Select all
Function putTimeStamp (Cell1 as Variant)
putTimeStamp = Format(Now, "YY-MM-DD HH:MM")
End Function
It would be better, more universal option, but there is no easy implementation of cell_onChange (as easy as in excel). It is longer, becouse it double checks if timestamp wasnt allready put
insert this into standard macro section:
Code: Select all
Sub putTimeStamp
Dim Doc
Dim sourceCell
Dim destCell
Dim Cell
Dim Sheet
Sheet = ThisComponent.CurrentController.ActiveSheet
Cell = ThisComponent.CurrentController.Selection
If Cell.getColumns().getCount() > 1 Or Cell.getRows().getCount() > 1 Then Exit Sub
If Cell.getRangeAddress.StartColumn <> 1 Then Exit Sub '1 is the column number which is being listened for changes (B)
r = Cell.getRangeAddress.StartRow
destCell = Sheet.getCellByPosition( 0, r ) ' 0 is the column number where timestamp should be placed
If destCell.Type <> com.sun.star.table.CellContentType.EMPTY Then Exit Sub
destCell.setValue(now())
End Sub
LibreOffice 3.5.7.2 Ubuntu 12.04 lts