Options

modify data from a table to another

lucianaluciana Member Posts: 43
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

  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
    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;
    
  • Options
    lucianaluciana 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?
  • Options
    tonymtonym Member Posts: 11
    I think it should be
    UNTIL tableA.NEXT = 0;
    

    and not
    UNTIL Rec.NEXT = 0;
    
  • Options
    SavatageSavatage 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:
  • Options
    lucianaluciana 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.
  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
    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;
    
  • Options
    MBergerMBerger 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.
  • Options
    lucianaluciana 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
    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?
  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
    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?
  • Options
    lucianaluciana Member Posts: 43
    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
Sign In or Register to comment.