Well I finally got a copy of Sesame, there seemed to have been a delay for us beyond the usual. I have been experimenting with it and I can't seem to find a way to generate note lines in a separate database.

To give an idea of my background I have done quite extensive work in SQL (Oracle, MySql) and I have been developing in Q&A for about 8 months now. I also have experience programming in Basic, C++ etc.

What I am trying to do is have a database for clients and a second database for the note lines on the file. I want the program to automatically insert the date, time, user information, a counter and then accept a noteline. The catch is I want unlimited number of notes so I need to store it in another database. I have tried using subforms to allow me to do this - i used the table view as I can't think of any other way to view all the related records. The only problem is I can't do programming on the subform and I can't seem to get it to reverse the order of the notes (I want the newest one at the top).

The only other way I can think of to do it would be very heavy programming where it automatically creates the records in the other database but it doesn't resolve the problem of listing more than one note line at a time. For that route I was thinking I would have a few entry fields and then have a "ADD note" button that automatically creates the note into the other database and then use a subform to show all the notes.

Anyone have any ideas?

Here is a diagram of what it should look like (with hidden indicating fields that would not be shown to the users):

Client Screen
|-------------------	-------------------------
| client #	_____________
| client Name	_____________
| ... misc info ...

Notes:

| **	hidden **	| ******	visible	****	********|
| #	client #	date	time	user	note
| 5	2001	01/28/2004	10:58	JM	newmsg
| 4	2001	01/27/2004	13:50	RG	old msg
| 3	2001	01/25/2004	11:58	JM	older msg
| 2	...............
| 1	...............
|---	------------	--------------	---------------

Any help would be grealy appreciated

Justin

Quote:


Justine, I am not a programmer and do not understand much about programming either. But I understand, you can sort the value of the subform as you look into it. Sort spec works in the subform.  I tried out that in Countries.db for trying out. Moreover, there is examples of using arrays to Sort Values on Appendix 2 of programming Guide. You may have your answer somewhere there if ordinary sorting does not do the job for you.

If you do Descending Sorting on date field in subform (table view) should do the job. Moreover you can add the record in subform while viewing the record.

Or may be this is all too high for me to understand....

BobScott,

What i meant by programming on a subform I forgot to explain that I meant programming on it in table form, what I want to do is that whenever a new record is created some information automatically gets filled in, I want it to get the active Client # from the main form, automatically increment the Note # field, then automatically enter the current date, time and user into appropriate fields. I am doing this with relational linking as I will need to be exporting this information and including the client # from the parent form when doing the export - it would be easier if that information is just stored in the notes database.

If i open up the programming window while in the main form layout design I am unable to select the subform as an object I can apply OnChange programming to. I still have a lot more experimenting to do though.

I will also see if I can get that sorting to work, hopefully there will be somewhere where I can define a sort automatically upon loading the record.

Justin

Justin,

I was experimenting with the SubForm using Sbasic and Sesame tools to achieve what you are trying to do. As I understand, what you want are the followings:

1. Take the ClientID automatically from the mainform to subform and assign it ClientID in Subform.
2. Enter Date automatically in Subform and so is the time.
3. Sort the records in SubForm in descending order of dateofnotes automatically.

(You cannot make a field invisible in subform, I settled it for having read only)

Here how I have achieved all of the required.

In the mainform, in ClientID on Form entry event, please put the following code

if @update then
     {

           Globalvalue ("main ClientID", ClientID)
           writeln (@globalvalue ("main ClientID") + " is new Globalvalue")

     }

//for new Client

In the mainform, in ClientID on Element Exit event, please put the following code

if @Isnew then
     {

           Globalvalue ("main ClientID", ClientID)
           writeln (@globalvalue ("main ClientID") + " is new Globalvalue")

     }


You may remove writeln line after you are confident that the code is working.

=========

Now goto the subform (not in the Form-Subform setup, but only subform by itself), in your case, I think it is notes.db

On form entry goes following code (you can put this in the form name that is also listed as an element in the program editor drop-down menu for elements)

ClientID = @globalvalue ("Main ClientID")

On Record exit event in ClientID, please put following Code:

If DateOfNote = "" then
      {
               DateOfNote = @Date
      }

you can use the same method to get present time entry automatically.

This much should achieve our first two goals.

For the third and Final, I had used Macro:

(As such the third step could have been achieved with programming with @SelectTreeItem( ) and @LoadSortSpec ( ) but I could not get @LoadSortSpec to work.  I believe the problem is already fixed for Sesame, version 1.0.2)

First Make the Sort Spec that will Sort DateOfNote field in Notes subform and save that. We will use this macro. Search the main Database with all record and hit F10 to creat the record set of all records.

Now start recording Macro,  first take the cursor to one of the main form element, hit <Alt -f8>, open dropdown menu in specmanager choose sort, and then load the sort spec you had earlier created.  make sure the cursor is in the mainform element and hit <alt R>, (you will see that this arranged records in descending order of the dateOfNote.  You can assign this macro to a button in the main form.

Justin,

Just a quick note. If you are using any Tableview subforms with the code Bharat posted to populate your client ID field do not panic if it does not work properly, there is a known problem that has been rectified in the next version of Sesame. Bharats code works great with form style subforms.