[Solved] Dated notes

Creating and using forms

[Solved] Dated notes

Postby leo1 » Mon Jan 07, 2008 5:13 pm

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
Last edited by leo1 on Sat May 31, 2008 1:52 am, edited 1 time in total.
leo1
 
Posts: 85
Joined: Mon Jan 07, 2008 5:11 pm

Re: dated notes

Postby DrewJensen » Mon Jan 07, 2008 5:22 pm

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.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: dated notes

Postby leo1 » Mon Jan 07, 2008 5:48 pm

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
leo1
 
Posts: 85
Joined: Mon Jan 07, 2008 5:11 pm

Re: dated notes

Postby DrewJensen » Mon Jan 07, 2008 5:58 pm

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?
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: dated notes

Postby leo1 » Mon Jan 07, 2008 6:16 pm

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
leo1
 
Posts: 85
Joined: Mon Jan 07, 2008 5:11 pm

Re: dated notes

Postby DrewJensen » Mon Jan 07, 2008 6:36 pm

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
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: dated notes

Postby leo1 » Mon Jan 07, 2008 6:52 pm

That seems to do it. In my sub form can I do that also or will that mess things up?
leo1
 
Posts: 85
Joined: Mon Jan 07, 2008 5:11 pm

Re: dated notes

Postby DrewJensen » Mon Jan 07, 2008 6:58 pm

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. 
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: dated notes

Postby leo1 » Tue Jan 08, 2008 2:05 am

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
leo1
 
Posts: 85
Joined: Mon Jan 07, 2008 5:11 pm


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests