Group By In Progress not like sql?

T

TBone2342

New Member
hello,

I have multiple records with the same product code and different price values. I am looking to sum the price values and group by product

Break by doesn't seem to work for what i am looking for.

Example.
FH 1.00
FH 3.00

EP 5.00
EP 2.00

should return.

FH 4.00
EP 7.00


Code I have now.

FOR EACH rpm.coitem NO-LOCK
WHERE (rpm.coitem.stat = "F" AND rpm.coitem.ship-date > 01/01/2009 AND rpm.coitem.ship-date < 02/06/2009),EACH rpm.item NO-LOCK
OF rpm.coitem
BREAK BY rpm.item.product-code:
v_row = v_row + 1.
hWorkSheet:Cells(v_row, 1):VALUE = rpm.item.product-code.
hWorkSheet:Cells(v_row, 2):VALUE = rpm.coitem.price * rpm.coitem.qty-shipped.

thanks ahead time
 
C

Casper

ProgressTalk.com Moderator
Staff member
Sounds like break by should work.
If you work with break by, you have to use first-of and last-of to count the total amount and fill the excel in last-of.

Casper.
 
E

enoon

Member
TBone2342 said:
hello,

I have multiple records with the same product code and different price values. I am looking to sum the price values and group by product

Break by doesn't seem to work for what i am looking for.

Example.
FH 1.00
FH 3.00

EP 5.00
EP 2.00

should return.

FH 4.00
EP 7.00


Code I have now.

FOR EACH rpm.coitem NO-LOCK
WHERE (rpm.coitem.stat = "F" AND rpm.coitem.ship-date > 01/01/2009 AND rpm.coitem.ship-date < 02/06/2009),EACH rpm.item NO-LOCK
OF rpm.coitem
BREAK BY rpm.item.product-code:
v_row = v_row + 1.
hWorkSheet:Cells(v_row, 1):VALUE = rpm.item.product-code.
hWorkSheet:Cells(v_row, 2):VALUE = rpm.coitem.price * rpm.coitem.qty-shipped.

thanks ahead time
Click to expand...

You have to sum them up, after that display them or use the ACCUM function which is pretty awkward.
Summing up all the records in a variable inside the for each and use the LAST-OF to display it on the last record of that particular type.
 
Z

zee

New Member
Something like this should get you going:

Code: 
FOR EACH rpm.coitem NO-LOCK
 WHERE (rpm.coitem.stat = "F" 
             AND rpm.coitem.ship-date > 01/01/2009 
             AND rpm.coitem.ship-date < 02/06/2009),
    EACH rpm.item NO-LOCK OF rpm.coitem
   BREAK BY rpm.item.product-code:

    if first-of(rpm.item.product-code) then dTotal = 0.
    dTotal = dTotal + (rpm.coitem.price * rpm.coitem.qty-shipped).

    if last-of(rpm.item.product-code) then do:
        v_row = v_row + 1.
        hWorkSheet:Cells(v_row, 1):VALUE = rpm.item.product-code.
        hWorkSheet:Cells(v_row, 2):VALUE = dTotal.
    end.
 
You must log in or register to reply here.
Top