[Solved] Need some guidance on my first Base database app.
-
- Posts: 4
- Joined: Wed Dec 05, 2007 12:32 am
[Solved] Need some guidance on my first Base database app.
So my company is making a serious consideration to change from MS Office to OpenOffice. Because I'm the company trainer the job of testing it's compatibility with all of our current docs was left up to me. I've worked my way through testing Writer and Calc and have moved on the Base. Which is where I'm stuck. I'm trying to our re-create our very simple field app and have not had any success in makeing a filed autopop the current timestamp (oddly enough I read that it used to but that functionality was change).
The app basically only needs 4 fields:
EntryID (Primary, Auto Incrementing), this was easy enough to make work
Rating (Numeric, Max 2 places, populated from a drop down box, between 1 and 10 only) Easy enough, but I can't figure out the base validation so that only valid options are entered.
Description (Text, 1000 Characters) this is where the description of the problem is entered, sometimes lengthy.
Timestamp (date, timestamp,) I want the field to automatically populate each time into a locked text box or label. I can't seem to get this to work.
We currently use an access database for this app and I suggested that our field techs could use portable openoffice, it would be a lot easier...So what am i missing exactly?
Thanks in advance.
Keith
The app basically only needs 4 fields:
EntryID (Primary, Auto Incrementing), this was easy enough to make work
Rating (Numeric, Max 2 places, populated from a drop down box, between 1 and 10 only) Easy enough, but I can't figure out the base validation so that only valid options are entered.
Description (Text, 1000 Characters) this is where the description of the problem is entered, sometimes lengthy.
Timestamp (date, timestamp,) I want the field to automatically populate each time into a locked text box or label. I can't seem to get this to work.
We currently use an access database for this app and I suggested that our field techs could use portable openoffice, it would be a lot easier...So what am i missing exactly?
Thanks in advance.
Keith
Last edited by Hagar Delest on Tue Jun 10, 2008 3:02 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
Reason: tagged the thread as Solved.
-
- Volunteer
- Posts: 283
- Joined: Tue Dec 04, 2007 6:38 pm
- Location: Houston, TX
Re: Need some guidance on my first Base database app.
Rating (Numeric, Max 2 places, populated from a drop down box, between 1 and 10 only) Easy enough, but I can't figure out the base validation so that only valid options are entered.
=========>you can use validation at the form level. look at the numeric field form control. this allows you to only accept numbers as well as specify a range of valid numbers.
Timestamp (date, timestamp,) I want the field to automatically populate each time into a locked text box or label. I can't seem to get this to work.
========>a couple of things you need to do. first is to set the default for the field to the current_timestamp like this
the easiest way to execute simple SQL in Base is to select Tools | SQL... and enter the SQL in the dialog that pops up.
the second step is to distable the corresponding control in the form or make it read-only.
hope this helps.
=========>you can use validation at the form level. look at the numeric field form control. this allows you to only accept numbers as well as specify a range of valid numbers.
Timestamp (date, timestamp,) I want the field to automatically populate each time into a locked text box or label. I can't seem to get this to work.
========>a couple of things you need to do. first is to set the default for the field to the current_timestamp like this
Code: Select all
ALTER TABLE <table name> ALTER COLUMN <colum name> SET DEFAULT CURRENT_TIMESTAMP
the second step is to distable the corresponding control in the form or make it read-only.
hope this helps.
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Need some guidance on my first Base database app.
Basically you have two choices here.
The first is simply to set the field to automatically populate the field with the current data or date and time whenever a new record is inserted into the database. This then would be handled at the database engine and not at the form level. What that means is that when you insert a new record in a form the control would be EMPTY - because the database the record has not been posted to the database engine, but as soon as it is the field is updated with the current date and or time. You would see this next time you viewed the record.
Now - to do this you must add this default behavior to the table using a standard SQL command. You can not do it, at the moment, using the GUI table design editor. To do this you use the SQL window to enter the command - AFTER the table has been created with the GUI table designer or the New Table Wizard.
As for constraining the allowed values for the Rating field to 1 - 10, this is again is entered with a standard SQL command in the SQL window.
These two commands can be entered together as long as each ends with a semi-colon.
SO - Assuming your table was named Table1 you would:
Open the SQL window with TOOLS > SQL
Enter the SQL Data Description Language command as
Click on the execute button
That should give you what you need to get started. If you have further questions please feel free to ask - You may also want to get a copy of the HSQLdb User Manual from http://hsqldb.org
HTH
Drew
EDIT LOL, looks like you got a 2 for 1 answer on this..
The first is simply to set the field to automatically populate the field with the current data or date and time whenever a new record is inserted into the database. This then would be handled at the database engine and not at the form level. What that means is that when you insert a new record in a form the control would be EMPTY - because the database the record has not been posted to the database engine, but as soon as it is the field is updated with the current date and or time. You would see this next time you viewed the record.
Now - to do this you must add this default behavior to the table using a standard SQL command. You can not do it, at the moment, using the GUI table design editor. To do this you use the SQL window to enter the command - AFTER the table has been created with the GUI table designer or the New Table Wizard.
As for constraining the allowed values for the Rating field to 1 - 10, this is again is entered with a standard SQL command in the SQL window.
These two commands can be entered together as long as each ends with a semi-colon.
SO - Assuming your table was named Table1 you would:
Open the SQL window with TOOLS > SQL
Enter the SQL Data Description Language command as
Code: Select all
ALTER TABLE "Table1" ALTER COLUMN "TimeStamp" SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE "Table1" ADD CONSTRAINT "cnst_rating" CHECK ( "Rating" >= 1 AND "Rating" <= 10 );
That should give you what you need to get started. If you have further questions please feel free to ask - You may also want to get a copy of the HSQLdb User Manual from http://hsqldb.org
HTH
Drew
EDIT LOL, looks like you got a 2 for 1 answer on this..
Last edited by DrewJensen on Wed Dec 05, 2007 6:54 am, edited 1 time in total.
Reason: 2 for 1
Reason: 2 for 1
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
-
- Posts: 4
- Joined: Wed Dec 05, 2007 12:32 am
Re: Need some guidance on my first Base database app.
Code: Select all
ALTER TABLE "Table1" ALTER COLUMN "TimeStamp" SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE "Table1" ADD CONSTRAINT "cnst_rating" CHECK ( "Rating" >= 1 AND "Rating" <= 10 );
Am i missing a Base onLoad.populate statement for the date text box? The sql statement from above should cover this problem I believe but it just ain't jivin'.
Thanks a bunch, this has been really educational working with a system I have never worked with before at all (well I've worked with SQL ALOT but figuring out how Base uses it has been interesting).
Keith
-
- Posts: 4
- Joined: Wed Dec 05, 2007 12:32 am
Re: Need some guidance on my first Base database app.
Ok here's a charming development....i just finished the post above and had closed base and restarted it, and then reloaded my saved form. Now when i click create a new record the date field is populated with 09/26/02 12:00 AM which from experience with MS Excel tells me that the field is formatted as a date but then an invalid numeric entry is placed into the field, in the case the number 1000. (You can test that in Excel and maybe Calc too. Format a cell as timestamp, then enter 1000 in that cell. The value will display as 09/26/02 12:00 AM). So what the heck am I missing???
Thanks again, again,
Keith
EDIT: You know after thinking about it I don't even really need the date to be displayed on the form, it just needs to be updated in the database as I go from one new record to the next. The only real input the field techs need is rating and description. Shouldn't I just be able to leave the date field out of the form all together and it will update all by itself when I go onto the next record? Well i tried it and i get a date must be filled in error...hmm...
Thanks again, again,
Keith
EDIT: You know after thinking about it I don't even really need the date to be displayed on the form, it just needs to be updated in the database as I go from one new record to the next. The only real input the field techs need is rating and description. Shouldn't I just be able to leave the date field out of the form all together and it will update all by itself when I go onto the next record? Well i tried it and i get a date must be filled in error...hmm...
-
- Posts: 4
- Joined: Wed Dec 05, 2007 12:32 am
Re: Need some guidance on my first Base database app.
Ok, for those of you interested I think I solved my own problem with a work around...maybe its just how base works. I went back and set my date field to Not be a required field...and what do you know...i tested it by entering 3 new records from a form that only had rating and description entry points then went to the table to view the data and vwallah! (is that how you spell vwallah?) the current date and time were in the date field for all 3 records.
So I guess I'd like any further clarifications that anyone has to offer and any thoughts.
Thank you very much for showing me in the door though!
Keith
So I guess I'd like any further clarifications that anyone has to offer and any thoughts.
Thank you very much for showing me in the door though!
Keith
- Hagar Delest
- Moderator
- Posts: 32653
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Need some guidance on my first Base database app.
No, it comes from French and the right spelling is : voilà.RevenantOne wrote:and vwallah! (is that how you spell vwallah?)
NB: I tag your thread as Solved as it seems it has been fixed.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
-
- Posts: 7
- Joined: Fri Nov 30, 2007 12:10 pm
Re: [Solved] Need some guidance on my first Base database app.
I too needed to "time stamp" a record and the solution posted:
Open the SQL window with TOOLS > SQL
Enter the SQL Data Description Language command as
works beautifully for me!
But, one problem remains, a psychological one: I don't understand where this code "lives" once it is executed. How can I look at the table properties (or whatever it is that is ALTERed) ?
By the way, I am completely ignorant of BASE, SQL, and OpenOffice BASIC --- I am just limping along with tips like the one posted above!
Thank you and all my bests,
Horst Graben
Open the SQL window with TOOLS > SQL
Enter the SQL Data Description Language command as
Code: Select all
ALTER TABLE "Table1" ALTER COLUMN "TimeStamp" SET DEFAULT CURRENT_TIMESTAMP;
But, one problem remains, a psychological one: I don't understand where this code "lives" once it is executed. How can I look at the table properties (or whatever it is that is ALTERed) ?
By the way, I am completely ignorant of BASE, SQL, and OpenOffice BASIC --- I am just limping along with tips like the one posted above!
Thank you and all my bests,
Horst Graben
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: [Solved] Need some guidance on my first Base database app.
One way is to extract the full database ( table structures, indexes, and data ) to a text file you use the SCRIPT command.
Open the SQL window again - enter the command
SCRIPT 'C:\TMP\MYDB_SCHEMA.SQL'
When executed this will create the file MYDB_SCHEMA.SQL in the c:\tmp directory. This will have all of the SQL commands necessary to fully recreate the database ( NOTE this is only for HSQLdb embedded databases ). Part of which is of course the CREATE TABLE statements, and in these you will find all the column level constraints.
Open the SQL window again - enter the command
SCRIPT 'C:\TMP\MYDB_SCHEMA.SQL'
When executed this will create the file MYDB_SCHEMA.SQL in the c:\tmp directory. This will have all of the SQL commands necessary to fully recreate the database ( NOTE this is only for HSQLdb embedded databases ). Part of which is of course the CREATE TABLE statements, and in these you will find all the column level constraints.
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