Help using the first record in a formula for Crystal XI

[bslaast]

I need to refer to the first record in a details section for a formula.

Currently, my formula reads:

if previous({PURCHASE_RECEIPT_INVOICE.PART_NO}) = {PURCHASE_RECEIPT_INVOICE.PART_NO} then
((previous({PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE}) - {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE}))*({PURCHASE_RECEIPT_INVOICE.QTY_ARRIVED})
else
0

My results are:

QTY: 58 Price: 3.22 Savings: 0.00
QTY: 50 Price: 8.42 Savings: -260.00
QTY: 56 Price: 3.22 Savings: 291.20

Instead of referring to the previous record, I would like it to refer the first record. Because the third line returns to the orignal price, the savings should be $0.00. Instead of the change of the second and third records.

 

[lbass]

Please show me with sample data. Did you read my post where I asked you to add a reset formula and some test formulas? Please follow through on that.

-LB

 

[bslaast]

Yes, I added all 3 formulas.

So for baseprice = 0:

Part 1
Price: 20.75 Qty 10 Savings: -8 Baseprice: 19.95

Part 2
Price: 19.95 Qty 10 Savings: 70.50 Baseprice: 27.00

Part 3
Price: 27.00 Qty 5 Savings: 3209.05 Baseprice: 668.81

The price for part 4 is 668.81.


Part 5
Price: 493 Qty: 1 Savings: 0 Baseprice: 493
493 2 0 493
493 1 0 493

Part 6
Price: 2,334 Qty: 1 Savings: -155 Baseprice: 2,179
2,173 1 0 2,179
2,179 1 0 2,179

 

[lbass]

I don't see the first price formula results. Are the baseprice formula results correct?

-LB

 

[lbass]

Okay, I did a mockup of your report. Your sample data indicates to me that you didn't add the reset formula to the Part No Group footer. You should also add the reset formula to the report header.

Then change the {@savings} formula to:

whileprintingrecords;
numbervar firstprice;
shared numbervar baseprice;
if baseprice <> 0 then
firstprice := baseprice else
if {PURCHASE_RECEIPT_INVOICE.PART_NO} <> previous({PURCHASE_RECEIPT_INVOICE.PART_NO}) then
firstprice := {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE} else
firstprice := firstprice;
(firstprice - {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE})*{PURCHASE_RECEIPT_INVOICE.QTY_ARRIVED}

If the subreport returns a 0 baseline price, then this formula uses the first value in the main report as the first price. Is that what you wanted?

-LB

 

[bslaast]

Yes!!! Thank you! Thanks for being patient. I know I wasn't very clear what I wanted the report to do. But it seems to be right now.

 

[bslaast]

Ok, one more thing. It is using the correct prices. But not with the quantities.

example:
Part One
base: 0 first: 23.20 price: 23.20 Qty: 100 Savings: 0
Qty: 55 Savings: 0
Qty: 155 Savings: 0

Part Two
base: 205 first: 205 price: 191.00 Qty: 1 Savings: 14
Qty: 9 Savings: 126
Qty: 10 Savings: 140

I need the savings to also reflect the change in quantity. Part One doesn't do this because the firtprice and price are the same so I am multiplying the qty with 0, giving a savings of 0. The second part has a differnece between firstprice and baseprice, so I am not multiplying the quantity by 0, instead the difference.

 

[lbass]

Please show the content of {@savings}. This works in my tests, so there is still something wrong with your formula.

-LB

 

[bslaast]

whileprintingrecords;
numbervar firstprice;
shared numbervar baseprice;
if baseprice <> 0 then
firstprice := baseprice else
if {PURCHASE_RECEIPT_INVOICE.PART_NO} <> previous({PURCHASE_RECEIPT_INVOICE.PART_NO}) then
firstprice := {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE} else
firstprice := firstprice;
(firstprice - {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE})*{PURCHASE_RECEIPT_INVOICE.QTY_ARRIVED}

 

[lbass]

Actually your results from two posts ago look correct to me. What results were you expecting instead?

-LB

 

[bslaast]

The savings needs to refect the change in quantity. Even if it is the same price, if more or less parts are purchased it would be a different amount "spent" or saved. The only time savings should be 0 is if the firstprice, baseprice and quantities are the same on all purchases. If the prices stay the same, but the quantities change, then the savings should not be 0.

 

[bslaast]

Referring to my post a few back,

Part one says:
base: 0 first: 23.20 price: 23.20 Qty: 100 Savings: 0
price: 23.20 Qty: 55 Savings: 0
price: 23.20 Qty: 155 Savings: 0

It SHOULD say:

base: 0 first: 23.20 price: 23.20 Qty: 100 Savings: 0
price: 23.20 Qty: 55 Savings: 1044
price: 23.20 Qty: 155 Savings: -1276

On the second purchase, the qty is 45 less, so we saved (100-55)*23.20 = 1044. On the thrid purchase, 55 more were bought so we lost (100-155)*23.20 = -1276

 

[lbass]

What would you expect to see in these two cases for Savings:

base: 0 first: 23.20 price: 23.20 Qty: 100 Savings:
price: 35.00 Qty: 55 Savings:
price: 45.00 Qty: 155 Savings:

base: 25 first: 25.00 price: 23.20 Qty: 100 Savings:
price: 10.00 Qty: 55 Savings:
price: 20.00 Qty: 155 Savings:

-LB

 

[bslaast]

Nevermind. The users changed their minds and like the report as is. Thank you so so much for all of your help!

 

[lbass]

And just when I finally thought I understood what you were looking for! I think it was just a matter of setting a variable equal to the firstprice * qty and then comparing that with price * qty in each row.

-LB

 

[bslaast]

Well, I may just be able to use that when they change their minds again