modify data from a table to another

luciana

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

mohana_cse06

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;

luciana

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

I think it should be

UNTIL tableA.NEXT = 0;

and not

UNTIL Rec.NEXT = 0;

Savatage

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:

luciana

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

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;

MBerger

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

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

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?

luciana

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!