Adding a specific row value to another row value??? 1

[bluraz]

I am using Crystal XI. I have the following table set up...

PH Carrier Quantity Add/Subtract NewTotal
GH1 ALVP 200 (100) 100
GH1 RDWY 100 0 100

I need to use the the "Add/Subtract" value (100) from the ALVP row and add this to the RDWY Quantity to receive a NewTotal value of 200 for RDWY.

Desired Output:

PH Carrier Quantity Add/Subtract NewTotal
GH1 ALVP 200 (100) 100
GH1 RDWY 100 100 200

Add/Subtract column formula; also referenced in NewTotal formula as {@RoadwayShipperLifts}:

If {@T/F RoadwayShipper} = "True" then({WMTRANSACTIONDETAIL.QUANTITYACTUAL})

NewTotal column formula:

If GroupName ({IDMASTER_CARRIER.IDENTITYID}) = "ALVP" then
(Sum ({WMTRANSACTIONDETAIL.QUANTITYACTUAL}, {IDMASTER_CARRIER.IDENTITYID}) - Sum ({@RoadwayShipperLifts}, {IDMASTER_CARRIER.IDENTITYID}))
else Sum ({WMTRANSACTIONDETAIL.QUANTITYACTUAL}, {IDMASTER_CARRIER.IDENTITYID})



Any help will be greatly appreciated!!!

 

[synapsevampire]

Are you trying to say that these column formulas are sums at the group level?

Rather than trying to describe the requirements, try posting what's in the table(s), and the required output.

And you didn't post what's in {@T/F RoadwayShipper}

If this is just a detail section concern, then use:

whileprintingrecords;
numbervar NewTot;
if not(onfirstrecord) then
NewTot:=NewTot+{table.addsubtract}
else
NewTotal:={table.quantity}

-k

 

[bluraz]

Quantity is pulled from the db table, suppressed in the detail, and summed at the group.

Add/Subtract is suppressed in the detail and summed at the group.

Add/Subtract formula:
If {@T/F RoadwayShipper} = "True" then ({WMTRANSACTIONDETAIL.QUANTITYACTUAL})

The {@T/F RoadwayShipper} formula:
IF {IDMASTER_CARRIER.IDENTITYID} = "ALVP" and
{IDMASTER_SHIPPER.IDENTITYID} = "RDWY" THEN
"True" else "False"

The NewTotal is a formula placed in the group header. (If placed in the detail, returns the same result.)
NewTotal formula:
If GroupName ({IDMASTER_CARRIER.IDENTITYID}) = "ALVP" then
(Sum ({WMTRANSACTIONDETAIL.QUANTITYACTUAL}, {IDMASTER_CARRIER.IDENTITYID}) - Sum ({@Add/Subtract}, {IDMASTER_CARRIER.IDENTITYID}))
else Sum ({WMTRANSACTIONDETAIL.QUANTITYACTUAL}, {IDMASTER_CARRIER.IDENTITYID})

The required output is to deduct the 'Add/Subtract' amount (100) from 'Quantity' for Carrier ALVP to produce a 'NewTotal' and to add this amount to the 'Quantity' for Carrier RDWY to produce a 'NewTotal'

Desired Output:

PH Carrier Quantity Add/Subtract NewTotal
GH1 ALVP 200 (100) 100
GH1 RDWY 100 100 200

I hope this is what you were asking me;Thank you!!!!!!!!!!

 

[lbass]

I think it would help if you showed some detail level data that contributes to these summaries.

-LB

 

[bluraz]

This is the output with the current configurations:

PH Carrier Shipper Quantity Add/Subtract NewTotal
GH1 ALVP 200 (100) 100
D Kmart 50 0 100
D Rdwy 75 (75) 100
D Kmart 10 0 100
D Rdwy 25 (25) 100

GH1 RDWY 100 0 100
D LTL 50 0 100
D LTL 25 0 100
D LTL 25 0 100

The scenario will always be to deduct quantites from the Carrier ALVP that have a Shipper = Rdwy; and then to add these quantites to the Carrier RDWY's total.

Desired Output:

PH Carrier Quantity Add/Subtract NewTotal
GH1 ALVP 200 (100) 100
GH1 RDWY 100 100 200

Does this help??

 

[Madawc]

It looks like you need running totals, which can add conditional on a value.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Try these formulas:

whileprintingrecords;
numbervar addsubtr;
numbervar newtotal;

if {table.carrier} = "ALVP" then
(
addsubtr := sum({@addsubtract},{table.carrier});
newtotal := sum({table.qty},{table.carrier})-addsubtr
);
if {table.carrier} = "RDWY" then
newtotal := sum({table.qty},{table.carrier}) + addsubtr;

For display, move your fields to the group footer, and add these display formulas:

//{@addsubtr}:
whileprintingrecords;
numbervar addsubtr;

if {table.carrier} = "ALVP" then -addsubtr else
if {table.carrier} = "RDWY" then addsubtr

//{@newtotal}:
whileprintingrecords;
numbervar newtotal;

I noticed that your detail records don't add up to 200 in your example, but I'm guessing that was an oversight.

-LB

 

[bluraz]

Thank you very much LB!!!!! This worked perfectly!!!