looking up into another table base on a filter

asembereng
asembereng Member Posts: 220
edited 2009-03-23 in NAV/Navision Classic Client
I have two tables to collect data from while running a report. The report is design on the History table and it needs to get data also from the loan_history table based on two fields it should compare; the empid and the MonthYear. But when running the report. How can i get data from the loan_history table when both the empid and the MonthYear is the same?

for example
select loan_history.monthly_deductions from loan_history, History where history.empid = loan_history.empid and history.MonthYear= loan_history.MonthYear;
If it was in mysql..
thanks

Comments

  • matttrax
    matttrax Member Posts: 2,309
    select loan_history.monthly_deductions from loan_history, History where history.empid = loan_history.empid and history.MonthYear= loan_history.MonthYear;
    IF history.FINDSET THEN
  REPEAT
    loan_history.SETRANGE(empid, history.empid);
    loan_history.SETRANGE(MonthYear, history.MonthYear);
    IF loan_history.FINDSET THEN
      //Do your thing
  UNTIL history.NEXT = 0

    Does that get you started?
    Matt Traxinger - ArcherPoint
  • asembereng
    asembereng Member Posts: 220
    thank you very much.. now i want to loop into this table if base on empid and the monthYear if it exit it should display and error or else execute the following 
    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.empid := loan2.empid;
    history."Run Date" := RunDate;
    history.MonthYear :=FORMAT(RunDate,0,'<Month text> <Year4>');;
    history.INSERT(FALSE); // the record doesn't exist, so you need to INSERT it and not MODIFY it
  UNTIL loan.NEXT = 0;

Categories