[Solved] Dated notes

[leo1]

I have a contacts database that I want to add notes to that are unique to each contact. I might need a subform to do this. The notes need to be dated and I need to be able to enter notes unique with each date. How would I do this? I am pretty new to the database scene. I know that MS Access had a template which included dated sub form notes. Does Open office have one?
Thank you for your help

[DrewJensen]

Hello leo1,

Base doesn't have quite the template system that MSA has, but none the less this is fairly straight forward.

What you want to do is create ( one solution anyway ) a separate table that will hold your notes and have this table related to the main contact table.

Let's say you have a primary key field in your Contact table named ID.

You would create a new table called, maybe, ContactNote and it would have fields such as

ContactNoteID - Primary Key for this table
ContactID - this is the field that will link this table to the Contact table, it must be of the same data type as the Primary key field in the Contact table ( normally an INTEGER )
EntryDate - This should be a field of type TIMESTAMP
Note - of type MEMO

Once you have created this table then you can create a form that uses the Contact table and the ContactNote table as a sub-form.

[leo1]

I did the wizard to set up the form but it doesn't let me put in any data the fields are greyed out for some reason. Any ideas

Thanks

[DrewJensen]

Well, it could be a couple of things - especially given my rather terse comments about how to set this up.

First - just possibly. If you don't have a contact record you can not enter notes - but I assume you have contact records.

Second - the most common problem would be the step where it asked you to link fields from the Main form to the sub-form. You needed to link the PK in the Contact table to the ContactID field in the sub-form.

Actually let me ask you - on the sub forum do you have the option for adding a record - the bottom of the table grid would have a data navigator bar and that has a asterisk type icon - if you can add a record this is colored yellow?

[leo1]

I got it to work the link in the wizard wasn't right. Another question how do I get the PK to auto populate in the Main contacts table? Because in the form I made its always asking me to put a number in.

Thanks

[DrewJensen]

Well, the easiest way was to have set it that way when you created the table - but since the table is created you will need to use the SQL window.

From the Base main window - TOOLS > SQL

Now you need to enter a command something like

ALTER TABLE "Contacts" ALTER COLUMN "ID" IDENTITY

Of course change the names as needed
Click on execute and you should get a confirmation.

At that point you should enter this command ( just to be safe )
SHUTDOWN SCRIPT

click on execute again.

Assuming no problems are reported at that point you must close the database and re-open it. You should have an auto_increment field now. One thing on that however, one person reported recently that this did not work under Kubuntu 7.1 and OpenOffice.org 2.3.1 and I have not had the chance to test that report yet.

Drew

[leo1]

That seems to do it. In my sub form can I do that also or will that mess things up?

[DrewJensen]

You can do it to the table for Notes also, yes. Just remember you are only doing it for the PK field in the Notes table - not the field that links back to the Contact table.

Edit: For completeness I should mention that many Data base admins would do this differently, using the actual timestamp field as the primary key field for this table - there are pros and cons about that for sure, but using a separate ID field is a good idea for you.

[leo1]

It works great! How can I get the dateentry field to auto format so that all I have to type is for example: 081307 to auto format to 08/13/07. Itried to change the field in the notes table to date format to that but it doesn't seem to work. Any ideas?

Thank you