Using a form to dump data into two different table

asembereng

Now i am using a form to enter data into two different table.. First the code process loan information on the loan table then update the fields on the loan table and is should also insert into the loan history but should check wether the employee number and the the date field data before dumping the data into the loan_history table. So far this is the code i have written but i am having the error "loan_history.id 0 does not exist"

 loan.SETFILTER(balance,'>0');
 loan.SETFILTER("Freeze Loan",'No');
IF loan.FINDSET THEN BEGIN
 IF (loan.balance > 0.0) THEN BEGIN
  REPEAT
      op_bal := loan.balance;
      cl_bal := loan.balance - loan.monthly_pay;
      paidoff := loan.loan_amount - cl_bal;
      loan.paidoffamount := paidoff;
      loan.MODIFY;
      loan.balance := cl_bal;
      loan.MODIFY;
   { //insert into loan history table.
    history.id += 1;
      history.op_balance:= op_bal;
      history.cl_balance:=cl_bal;
     history.ded_amount := loan.monthly_pay;
     history.int_amount := loan.monthly_int;
     history.loan_id := loan.id;
     history.MODIFY; }
  UNTIL loan.NEXT = 0;
  END;
END;

//display result on a report
view.RUN;

can someone please help me?

kriki

Maybe something like this (I also cleaned up your code a little):

// Save the last ID of table History (I suppose it is unique)
history.RESET;
IF history.FINDLAST THEN
  LintHistory_ID := history.id;
  
loan.RESET;
loan.SETCURRENTKEY("Freeze Loan",Balance);
loan.SETRANGE("Freeze Loan",FALSE);
loan.SETFILTER(balance,'>0');
IF loan.FINDSET(TRUE,TRUE) THEN // TRUE,TRUE because you want to change the table
  REPEAT
    loan2 := loan; // if you change records inside a loop, you NEED a second variable in which you change the record
    op_bal := loan2.balance;
    cl_bal := loan2.balance - loan2.monthly_pay;
    paidoff := loan2.loan_amount - cl_bal;
    loan2.paidoffamount := paidoff;
    loan2.balance := cl_bal;
    loan2.MODIFY; // no need for 2 modifies of the same table
      
    //insert into loan history table.
    LintHistory_ID += 1;
    
    CLEAR(history);
    history.id := LintHistory_ID;
    history.op_balance := op_bal;
    history.cl_balance := cl_bal;
    history.ded_amount := loan2.monthly_pay;
    history.int_amount := loan2.monthly_int;
    history.loan_id := loan2.id;
    history.INSERT(FALSE); // the record doesn't exist, so you need to INSERT it and not MODIFY it
  UNTIL loan.NEXT = 0;

//display result on a report
view.RUN;

asembereng

thank you.. but i am having an error message "the loan table does not have an active key field freeze loan, balance" I included those fields on the keys but still the same error.

kriki

Maybe you didn't enable the key? Or you created the key on another client than the one you run the code from?
The code will also work without the SETCURRENTKEY, but probably it will be slower because the whole table will be scanned (even with the SETCURRENTKEY it is possible the whole table will be scanned on SQL. This depends on how many data and the data itself : if the number of records with "Freeze Loan" = FALSE is below 5% of the total, the index will probably be used)