getting data from different tables and inserting to another

asembereng

I want to get data from the employee, allowance and loan table base on the employee number and insert them them to the history table. How can i do that please?

DaveT

Hi,
I'd need a little more detail but the idea is:

Create a report off the employee tables
in the onaftergetrecord use record variables for the allowance and loan table to get the info.
insert the data into the history table using a record variable.

If you can give a full description, I can can put some meat on these bones

asembereng

the employee table i only need to get the empid, use the empid to go through the allowance table select all allowances related to this employee, sum it, store it a variable, like wise the loan table then insert it into the history table. the history table has fields; line no., empid, allowances, loan, month, basic pay and net. the net will be (allowances + basic pay) - loan.
Is this explanation good? let me know if you need more explanation. thank you.

DaveT

Hi,

Create a report off the employee table.
Create a record variable for Allowance and Loan then in the onaftergetrecord put

totalallowance := 0;
Allowance.reset;
Allowance.setrange( empid, employee."No." );
if allowance.findset then
  repeat
    totalallowance += allowance.value;
  until allowance.next = 0;

If the value is a sumindex field then you can use calcsums function.
Do the same for loan and then insert into the history file.

asembereng

Thanks alot.. I tested it and it inserted the allowance into the table. Loan History table has a field called called id and it should auto increment. I did that on the field property but whenever i want to insert something it tells me the id no exist. is there anyway that i can make the field auto increment to the next number anytime i want to insert something there? Thanks once more

DaveT

Hi,

From a form yes you can use the Autosplitkey property but you will have to code it here.

use similar code to

GetNextLineNo(SalesLine : Record "Sales Line") : Integer
SalesLine.SETRANGE("Document Type",SalesLine."Document Type");
SalesLine.SETRANGE("Document No.",SalesLine."Document No.");
NextLineNo := 10000;
IF SalesLine.FIND('+') THEN
  NextLineNo := SalesLine."Line No." + 10000;
EXIT(NextLineNo);

asembereng

I tried that but i am having an error message " id 10000 does not exist"

DaveT

Hi,

Can you post the code so we can have a look

asembereng

Documentation()

Employee - OnPreDataItem()

Employee - OnAfterGetRecord()
his.empid := empid;
totalallowance := 0;
Allowance.RESET;
Allowance.SETRANGE( empid, Employee.empid );
IF Allowance.FINDSET THEN
  REPEAT
    totalallowance += Allowance.Amount;
  UNTIL Allowance.NEXT = 0;
his.amount := totalallowance;
his.id := GetNextLine(his);
his.MODIFY;

Employee - OnPostDataItem()

GetNextLine(his : Record his) : Integer

his.SETRANGE(empid, Employee.empid);
his.SETRANGE(id,his.id);
NextLineNo := 10000;
IF his.FIND('+') THEN
  NextLineNo := his.id + 10000;
EXIT(NextLineNo);

DaveT

Hi,

I think your problem is that you have a his.MODIFY instead of a his.INSERT

asembereng

Thank you very much.. I really appreciate the help. Its working now..
Sorry but how can i prevent default value like zero being stored on a field that has a relations to another table. what happens is this; by default that field stores zero and in the relation table zero is not an id. So when i run the report it complains of id zero does not exist. I tried changing the minimum value on the field to 1 but that doesnt help. Is there anyway i can handle this?

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?

DaveT

Hi,
Without seeing the full code I am assuming that you are getting the error in the code commented out by the {}. This is because you have a a MODIFY command instead of an INSERT. Basically use INSERT to add a record and MODIFY to amend an existing record.