[Solved] Need some guidance on my first Base database app.

[RevenantOne]

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

[QuazzieEvil]

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

Code: Select all

ALTER TABLE <table name> ALTER COLUMN <colum name> SET DEFAULT CURRENT_TIMESTAMP

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.

[DrewJensen]

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

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 );

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

[RevenantOne]

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 );

Thank you very much, that seems to be exactly what I need. The rating field is working perfectly. The timestamp field though only seems to want to populate as you are moving onto the next entry. For example, if i'm in the table and am manually entering data into the table and go from one field to the next by hitting tab, when i get to the last field in a record it jumps down to the new record and the current timestamp pops into the last record i was working. This doesn't seem to want to work with a form. The date field (which i realistically want to be read only, but have tested this logic with read only off and on and it is the same result either way) is just blank the whole time and when i try to add a new record after setting a rating and typing a description i get an error that says my date field is empty and record can't be saved. I click ok and the focus is set to the blank date field.

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

[RevenantOne]

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

[RevenantOne]

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

[Hagar Delest]

and vwallah! (is that how you spell vwallah?)

No, it comes from French and the right spelling is : voilà.

NB: I tag your thread as Solved as it seems it has been fixed.

[horst.graben]

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

Code: Select all

ALTER TABLE "Table1" ALTER COLUMN "TimeStamp" SET DEFAULT CURRENT_TIMESTAMP;

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

[DrewJensen]

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.