Making change to database as leaving form

I have two tables, one a header, the other the line, along with the appropiate forms (Kind of like the standard Sales Order tables / forms). When I leave the current header record (In the form I have created) I want Navision to automatically create a line for this header record that I am leaving. Now I have managed to do this when I close down the form pretty easily (by putting some code in the OnCloseForm() trigger), but am having some difficulty in making it do this when move from one header record to another. The triggers I want to use for this dont seem to allow modifying the database. Can anyone give me a hint how I can do this? Thanks. Shanon

Shannon, Rather than creating the new record in the lines when you move to a new header record or Close the form why dont you add some code to the OnInsert trigger of the Header table so you create the line record at that time every time you make a new Header record. If you also require other details from the header to go to the lines these could be done on the OnValidate triggers of those fields. Simon

Thanks Simon, but that still wont do quite what I need to do. Basically, we are trying to keep a track of how long people keep the form open for on a particular record. So whenever the user closes the form or moves to another (header) record, we want a new line record to be created to record this time. Shanon

Hi Shannon, I think I have got the solution after some puzzling. You were right about that the triggers you will have to use can not modify any data, but what if you pull the OnCloseForm trigger instead? Well, here is the code. I hope you can use it.


**OnInit()**
FormCanClose := TRUE;
OpeningTime := TIME;

**OnQueryCloseForm() : Boolean**
LinesRec.INIT;
LinesRec.Header := PreviousCode;
LinesRec.Seconds := (TIME - OpeningTime) / 1000;
OpeningTime := TIME;
LinesRec.INSERT;

IF NOT FormCanClose THEN BEGIN
  FormCanClose := TRUE;
  EXIT(FALSE);
END;

**OnFindRecord(Which : Text[260]) : Boolean**
IF NOT FIND(Which) THEN
  EXIT(FALSE);

IF (xRec.Code <> '') AND (xRec.Code <> Code) THEN BEGIN
  PreviousCode := xRec.Code;
  FormCanClose := FALSE;
  CurrForm.CLOSE;
END ELSE
  PreviousCode := Code;

EXIT(TRUE);

**OnNextRecord(Steps : Integer) : Integer**
PreviousCode := Code;
FormCanClose := FALSE;
CurrForm.CLOSE;
NEXT(Steps);
EXIT(Steps);

Regards, Reijer

Shanon, If understand correctly, you want to insert a record into a table, recording the time an user has been looking at another record. Usage-time logging, so to speak. So you need two times: the time at entering and the time at leaving (be careful, though, for crossing midnight). Question is if the users can only look at existing records, or can they insert/delete records too? For simplicity, let’s assume the users can read records only. Then you could do your logging in the following way: StartTime = Global var of type Time EndTime = Global var of type Time OnOpenForm() StartTime := TIME; OnCloseForm() EndTime := TIME; MESSAGE(‘FORM CLOSE\Start time: %1\End time: %2’,StartTime,EndTime); OnAfterGetRecord() IF (xRec.“No.” <> Rec.“No.”) and (xRec.“No.” <> ‘’) THEN BEGIN EndTime := TIME; MESSAGE(‘NEW RECORD\Start time: %1\End time: %2’,StartTime,EndTime); StartTime := TIME; END; Where the messages are in this example, you should place the call to your logging record insertion routine (make this a seprate function). John

John, That was the first thing I tried too but, when you use a separate function instead of the message, the active trigger will still be the OnAfterGetRecord-trigger and Navision will error ‘You cannot make any changes in the database from this trigger’. The trick in this case is to move the insert-routine to the OnQueryCloseForm. Anyhow, looking at your code and realising that the Find and NextRecord triggers are not necessary at all, I made a combination of our two options:

**OnOpenForm()**
FormCanClose := TRUE;
StartTime := TIME;
"CurrentNo." := "No.";

**OnQueryCloseForm() : Boolean**
LinesRec.INIT;
LinesRec.Header := "PreviousNo.";
LinesRec.Seconds := (TIME - StartTime) / 1000;
LinesRec.INSERT;

StartTime := TIME;
"PreviousNo." := "CurrentNo.";

IF NOT FormCanClose THEN BEGIN
  FormCanClose := TRUE;
  EXIT(FALSE);
END;

**OnAfterGetRecord()**
IF "No." <> "PreviousNo." THEN BEGIN
  FormCanClose := FALSE;
  CurrForm.CLOSE;
END;
"CurrentNo." := "No.";

Reijer.

Reijer, You’re right. I overlooked this error. However, your sample code is not recording the time each record has been showed on the form, it’s recording the time between opening and closing the form. That’s okay, but not fully the answer to the question of the poster. So I looked at another way. With a little bit more code, and a big trick, you can accomplish what’s been asked - Logging of the time each record has been displayed. The trick in this is to avoid making changes to the database in the OnAfterGetRecord trigger, but create new TimeLogging records nevertheless. How? Create a temporary table to hold the logging records while the user browses, and first at closing of the form copy the temporary records to the database. Sample code:

 
**Global Variables:**
Name         DataType
StartTime    Time		
EndTime      Time		
TimeLogging  Record_Time Logging **with property Temporary = Yes**
StoreTimeLog Record_Time Logging **with property Temporary = No**
NextNo       Integer		
ClosingForm  Boolean		

“Time Logging” is a new table with the following fields:


Field Name Data Type Length
Log No.	   Integer		
User	   Code      10	
Table Name Text      30	
Record No. Code      10	
StartTime  Time		
EndTime    Time		


**OnOpenForm()**
StartTime := TIME;


**OnCloseForm()**
//Log time for last record
EndTime := TIME;
ClosingForm := TRUE;
LogTime;
//Copy the temp logging records to database
StoreLogTime;


**OnAfterGetRecord()**
//Log time if it's not the first (OpenForm) record
IF (xRec."No." <> Rec."No.") AND (xRec."No." <> '') THEN BEGIN
  ClosingForm := FALSE;
  EndTime := TIME;
  LogTime;
  StartTime := TIME;
END;


function: **LogTime()**
NextNo := 1000;
IF TimeLogging.FIND('+') THEN
  NextNo := TimeLogging."Log No." + 1000;
TimeLogging.INIT;
TimeLogging."Log No." := NextNo;
TimeLogging.User := USERID;
TimeLogging."Table Name" := TABLENAME;
IF NOT ClosingForm THEN
  TimeLogging."Record No." := xRec."No."
ELSE
  TimeLogging."Record No." := "No.";
TimeLogging.StartTime := StartTime;
TimeLogging.EndTime := EndTime;
TimeLogging.INSERT;


Function: **StoreLogTime()**
//Copy the temporary records to the database
TimeLogging.RESET;
NextNo := 0;
IF TimeLogging.FIND('-') THEN BEGIN
  StoreTimeLog.LOCKTABLE;
  IF StoreTimeLog.FIND('+') THEN
    NextNo := StoreTimeLog."Log No.";
  REPEAT
    NextNo := NextNo + 1000;
    StoreTimeLog.INIT;
    StoreTimeLog := TimeLogging;
    StoreTimeLog."Log No." := NextNo;
    StoreTimeLog.INSERT;
  UNTIL TimeLogging.NEXT = 0;
END;

Of course you can further expand the logging information, but I leave that as an exercise to you :slight_smile: John Edited by - John Tegelaar on 2001 Sep 29 12:32:15

John, very nice. That is another way to do it and probably more elegant for not abusing the OnQueryCloseForm-trigger. But a little disadvantage is that the data is not available for other users until the form is closed. Actually, my example does record the time that each record is showed on the form. Whenever a new record is active Navision tries to close the form and the previous ‘recordtime’ will be saved. After that the CloseTrigger is exited with False and the form stays open. Reijer.

It great to see discussions like this that end up in a general resolution. I think that there is another issue missed here though. What happens when the user opens multiple forms, and clicks between them. I would suggest using the OnActivate and OnDeactivate triggers to generate the temporary entries. Such as in the attached object: oops UPLOAD does not work… — anyway, basically the object I just tried creates the entries into a temp table on GetRecord, Activate and Deactivate. It inserts the entries on close form. You do need to compress these entries but you can measure the amount of time that any particular record was displayed as the active window. I will try to attach the object later. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________

Thank you Reijer and John. I have managed to get Navision to do exactly what I want it to do (mainly using Reijer’s code). I should have been more specific. I already had to functionality to calculate the time the form was open at a particular record, and to insert the line. At the moment (Or before I implemented Reijer’s code) the user had to press a button to make it work. The problem was with making the code run upon changing records. But its all working fine now. Thanks. Shanon.