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
[Solved] Dated notes
[Solved] Dated notes
Last edited by leo1 on Sat May 31, 2008 1:52 am, edited 1 time in total.
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: dated notes
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.
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
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: dated notes
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
Thanks
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: dated notes
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?
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
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: dated notes
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
Thanks
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: dated notes
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
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
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: dated notes
That seems to do it. In my sub form can I do that also or will that mess things up?
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: dated notes
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
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: dated notes
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
Thank you