Logging date on check box

Discuss the database features

Logging date on check box

Postby sqw68 » Tue Feb 21, 2017 12:44 pm

I've got a number of checkboxes that are ticked as an when certain tasks are done.

Is there a way to easily record the date when these were ticked, without having to add adate field to the form?
Or will I need the date field?
OpenOffice 4.1.3
Windows 7
sqw68
 
Posts: 8
Joined: Tue Jan 31, 2017 5:46 pm

Re: Logging date on check box

Postby Nocton » Tue Feb 21, 2017 6:22 pm

You could write a bit of code to do an SQL update of a date field when the check box is ticked. It's easy, but you may not have written code before?
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 495
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Logging date on check box

Postby sqw68 » Tue Feb 28, 2017 10:16 am

No, Sadly never written code.
I'll just build a date field into the table rather than a tick box.
OpenOffice 4.1.3
Windows 7
sqw68
 
Posts: 8
Joined: Tue Jan 31, 2017 5:46 pm

Re: Logging date on check box

Postby Villeroy » Tue Feb 28, 2017 11:52 am

The attached document is a demo on default values with a default date in the "Data" table and a time stamp in the related "Check" table. This is a one-to-one relation where every record in "Data" may have one or zero records in table "Check".

How I added the default date in "Data" using Tools>SQL... (not possible in the graphical user interface):
Code: Select all   Expand viewCollapse view
ALTER TABLE "Data" ALTER COLUMN "Date" SET DEFAULT CURRENT_DATE;

How I added the time stamp
Code: Select all   Expand viewCollapse view
ALTER TABLE "Check" ALTER COLUMN "TS" SET DEFAULT CURRENT_TIMESTAMP;

How I linked the two tables by adding a foreign key to the "Check" table (can be very tricky to do in the GUI):
Code: Select all   Expand viewCollapse view
ALTER TABLE "Check" ADD FOREIGN KEY ("ID") REFERENCES "Data" ("ID");


The form:
The subform shows the related "Check" record for the selected "Data" record in the parent form.
The forms are linked by their common ID fields, the time stamp controls are read-only, the save button marked as default button (reacts on Enter key).
When you check the box of an empty "Check" record and either push the save button or move back to the parent form, then you store a new record in the "Check" table which marks the related "Data" record as checked. The new record gets an automatic time stamp.
You can uncheck a record either by unchecking the boolean field (which keeps the original time stamp) or by deleting the "Check" record alltogether.

The default date in the "Data" form is today's date if the user does not enter any other date.

Unfortunately, I don't know any way to show this one-to-one relation in a single grid AND keep the grid editable. You can merge both tables in a read-only query as shown by query "qMerge".
Attachments
one2one_stamp.odb
One-to-one demo with check box and time stamp.
(13.46 KiB) Downloaded 41 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests