modify data from a table to another

luciana
luciana Member Posts: 43
edited 2011-06-08 in NAV/Navision Classic Client
Hi to all.

I should modify a value in a field of table A modifying a value in table B.
E.g.

I have a form with a subform. In subform I have multitple records with a field called Quantity.
If I press button "Confirm" , code is supposed to read the value in field Quantity and sum (or decrease , this depends on an option) it to the value that is in another table , called table A, that has a field called Quantity too, record after record.
I'll try to explain in a better way:
if in the subform I have three records , e.g.
name quantity
subject1 5
subject2 10
subject3 1

and in table A I have

name quantity
subject1 1
subject2 4
subject3 25

when I press the button, I should take Quantity of subject1 and modify quantity in table A, then I take quantity of subject2 and modify quantity in table A, then I take quantity of subject3 and modify quantity in table A, et cetera.

subjects of subform and table A are the same.
This looks like a warehouse but I cannot use standard table and "item ledger entries" table like.

As result I should have quantity modified as :

subject1 6
subject2 14
subject3 26
in case of sum, obviously.

I tried to use flowfield but I realized that flowfield sums the whole column quantity.
How could I do this?

Thanks
Luciana

Comments

  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    You should code something like below in OnPush trigger of Confirm Button
    TableA.Reset;
TableA.Setrange(Subject,Rec.Subject); // As you are in Subform that will be table B only
IF TableA.FINDFIRST THEN BEGIN
  IF Sum THEN BEGIN
    TableA.Quantity := TableA.Quantity + TableB.Quantity;
    TableA.MODIFY;
  END ELSE BEGIN
    IF decrese THEN BEGIN
      TableA.Quantity := TableA.Quantity - TableB.Quantity;
      TableA.MODIFY;
    END;
  END;
END;

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • luciana
    luciana Member Posts: 43
    uhmm
    I try to write:

    tableA.RESET;
    tableA.SETRANGE(subject,Rec.Subject); --> find the subject in tableA that's equal to subject in subform
    IF tableA.FINDSET(TRUE,TRUE) THEN BEGIN
    REPEAT
    tableA.Quantity := tableA.Quantity +Rec.Quantity;

    tableA.MODIFY;
    UNTIL Rec.NEXT = 0;
    END;

    the findset + repeat until should slide records and modify field tableA.Quantity one by one, but it sums all values in field quantity of the subform and adds the result to the value of field quantity in tableA that's focused on subform.

    what's wrong?
  • tonym
    tonym Member Posts: 11
    I think it should be
    UNTIL tableA.NEXT = 0;

    and not 
    UNTIL Rec.NEXT = 0;
  • Savatage
    Savatage Member Posts: 7,142
    luciana wrote:
    tableA.RESET;
    tableA.SETRANGE(subject,Rec.Subject); --> find the subject in tableA that's equal to subject in subform
    IF tableA.FINDSET(TRUE,TRUE) THEN BEGIN
    REPEAT
    tableA.Quantity := tableA.Quantity +Rec.Quantity;
    tableA.MODIFY;
    UNTIL Rec.NEXT = 0;
    TableA - setrange
    TableA - Findset
    Rec.NEXT = 0; ????

    Why not TableA.Next
    **Edit Too Slow :oops:
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • luciana
    luciana Member Posts: 43
    Savatage wrote:
    luciana wrote:
    tableA.RESET;
    tableA.SETRANGE(subject,Rec.Subject); --> find the subject in tableA that's equal to subject in subform
    IF tableA.FINDSET(TRUE,TRUE) THEN BEGIN
    REPEAT
    tableA.Quantity := tableA.Quantity +Rec.Quantity;
    tableA.MODIFY;
    UNTIL Rec.NEXT = 0;
    TableA - setrange
    TableA - Findset
    Rec.NEXT = 0; ????

    Why not TableA.Next
    **Edit Too Slow :oops:

    I thought that I should step record by record on subform.
    But even if I put tableA.NEXT it doesn't work. It continues to add quantity to record that is focused.
  • mohana_cse06
    mohana_cse06 Member Posts: 5,506 
    TableB.RESET;
TableB.SETANGE(XXX,Rec.XXX); // Primary key Fields shd be replaced with XXX and YYY
TableB.SETANGE(YYY,Rec.YYY); // Primary key Fields shd be replaced with XXX and YYY
IF TableB.FINDSET THEN
  REPEAT
    TableA.Reset;  
    TableA.Setrange(Subject,TableB.Subject);
    IF TableA.FINDFIRST THEN BEGIN
      IF Sum THEN BEGIN
        TableA.Quantity := TableA.Quantity + TableB.Quantity;
        TableA.MODIFY;
      END ELSE BEGIN
        IF decrese THEN BEGIN
          TableA.Quantity := TableA.Quantity - TableB.Quantity;
          TableA.MODIFY;
        END;
      END;
    END;
  UNTIL TableB.Next = 0;

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • MBerger
    MBerger Member Posts: 413
    I think you are forgetting to loop through REC too. I think you need something like this 
    subformrec.copy(rec) ;
if subformrec.findset then
  repeat
    TableA.setrange(subject,subformrec.subject) ;
    if TableA.findset(true,true) then
      repeat
        TableA.quantity += Subformrec.quantity ;
        TableA.modify(true) ;
      until TableA.next = 0 ;
  until subformrec.next = 0 ;

    This code is not tested, so there might be small errors.
  • luciana
    luciana Member Posts: 43
    MBerger wrote:
    I think you are forgetting to loop through REC too. I think you need something like this 
    subformrec.copy(rec) ;
if subformrec.findset then
  repeat
    TableA.setrange(subject,subformrec.subject) ;
    if TableA.findset(true,true) then
      repeat
        TableA.quantity += Subformrec.quantity ;
        TableA.modify(true) ;
      until TableA.next = 0 ;
  until subformrec.next = 0 ;

    This code is not tested, so there might be small errors.

    this is very very cool. it works!

    thanks
    mohana_cse06 wrote:
    TableB.RESET;
TableB.SETANGE(XXX,Rec.XXX); // Primary key Fields shd be replaced with XXX and YYY
TableB.SETANGE(YYY,Rec.YYY); // Primary key Fields shd be replaced with XXX and YYY
IF TableB.FINDSET THEN
REPEAT
TableA.Reset; 
TableA.Setrange(Subject,TableB.Subject);
IF TableA.FINDFIRST THEN BEGIN
IF Sum THEN BEGIN
TableA.Quantity := TableA.Quantity + TableB.Quantity;
TableA.MODIFY;
END ELSE BEGIN
IF decrese THEN BEGIN
TableA.Quantity := TableA.Quantity - TableB.Quantity;
TableA.MODIFY;
END;
END;
END;
UNTIL TableB.Next = 0;

    mohana, your suggestion is cool too, but I haven't understood why pk field should be replaced..

    last question: rather than sum and decrese I have a type Option to select on header of the form.
    I wrote this :
    CASE FormHeader.Option OF
    FormHeader.Option :: Option1:
    BEGIN
        code with sum
    END;

    FormHeader.Option :: Option2:
   BEGIN 
          code with difference 
 END;

    but, if I put a MESSAGE('%1', FormHeader.Option) in each case, I see only Option1, even if I select Option2.
    It's important because sum or difference depends on the kind of option selected.
    why case of doesn't work?
  • mohana_cse06
    mohana_cse06 Member Posts: 5,506
    luciana wrote:
    mohana, your suggestion is cool too, but I haven't understood why pk field should be replaced..
    In Order to get all the records in Form B.
    You can replace that with 
    subformrec.copy(rec) ;
    luciana wrote:
    last question: rather than sum and decrese I have a type Option to select on header of the form.
    I wrote this :
    CASE FormHeader.Option OF
    FormHeader.Option :: Option1:
    BEGIN
        code with sum
    END;

    FormHeader.Option :: Option2:
   BEGIN 
          code with difference 
 END;

    but, if I put a MESSAGE('%1', FormHeader.Option) in each case, I see only Option1, even if I select Option2.
    It's important because sum or difference depends on the kind of option selected.
    why case of doesn't work?

    Where did you write this code?
    where did you get header Record?

    -Mohana
    http://mohana-dynamicsnav.blogspot.in/
    https://www.facebook.com/MohanaDynamicsNav
  • luciana
    luciana Member Posts: 43
    mohana_cse06 wrote:
    Where did you write this code?
    where did you get header Record?

    I wrote it in a function in a subform, that is called from OnPush trigger with:
    CurrForm.NameOfTheSubform.FORM.NameOfTheFunction

    To call header record I created a variable record on table MyTableHeader.


    EDIT: Solved. I created two functions in subform, one to sum and one to decrease.
    Then, in FormHeader I wrote: 
    IF Option = 0 THEN   BEGIN
 
    CurrForm.SubformName.FORM.function1;
END
ELSE
BEGIN

  CurrForm.SubformName.FORM.function2;
END;

    That works fine!
    Thank you very much! :D

Categories