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]

Use a formula like this, assuming a group on partno:

whileprintingrecords;
numbervar firstprice;
if onfirstrecord or
{table.partno} <> previous({table.partno}) then
firstprice := {table.price} else
firstprice := firstprice;
(firstprice - {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE})*{PURCHASE_RECEIPT_INVOICE.QTY_ARRIVED}

-LB

 

[bslaast]

Thanks, that worked. But if the first record = 0 then I need to have the next record treated as if it would be the first record. Does that make sense?

 

[lbass]

Why don't you eliminate 0 records from your report?

-LB

 

[bslaast]

What the report is doing is comparing purchase history prices within a given time period:

(firstprice - nextprice) * quantity

I first need a base price, which is the last purchase date outside of the the time frame. I treat this base price as the first record and use that price to see if subsequent purchases are at a gain or loss. But if the part was never purchased before, the base price is 0. Then I need the first date that is with IN the time parameters to be the base price. I can't eliminate 0 records because I need to see if they really haven't been purchased before, or if there is no change in the price.

 

[lbass]

Okay try this:

whileprintingrecords;
numbervar firstprice;
if onfirstrecord or
{table.partno} <> previous({table.partno}) then
(
if {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE} <> 0 then
firstprice := {table.price} else
firstprice := next({table.price})
) else
firstprice := firstprice;
(firstprice - {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE})*{PURCHASE_RECEIPT_INVOICE.QTY_ARRIVED}

-LB

 

[bslaast]

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

It is still using the first record as the firstprice. Does it make a difference that the first record is a formula passed from a subreport?

 

[lbass]

You mean the price is a shared variable? Because you can't use previous/next with variables. Please explain more and show the content of any variable formulas. You would also need to explain where the subreport is located and how it is linked to the main report, how the main report is grouped, etc.

-LB

 

[bslaast]

Group 1: Supplier
Group 2: Part No with a subreport in the header to get the Base Price outside of the date parameters. It is linked by Part No and Beginning Date. The subreport has 2 formula fields. One to get the maximum date outside of the time frame and the other to pass a shared variable (the price at that max date) to the main report.
Details: Date, price and quantity. Two formulas: Baseprice and Savings. Baseprice receives the shared variable from the subreport. Savings calculates the price difference between the detail price and the baseprice. ((base - price)*qty)

If the part has never been purchased before, the baseprice would be 0. I would then need it to use the first record in the details as the base price. It works fine if the part has been purchased outside the time period.

 

[bslaast]

Here is the content of the formula fields:

baseprice (in main report)

whileprintingrecords;
shared numbervar baseprice;
baseprice

savings (main report)

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

max date (subreport)

if isnull ({PURCHASE_RECEIPT_INVOICE.ARRIVAL_DATE}) then "None"
else
ToText(maximum({PURCHASE_RECEIPT_INVOICE.ARRIVAL_DATE}))

baseprice (subreport)

whileprintingrecords;
if not isnull({PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE}) then
shared numbervar baseprice := ({PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE})
else
baseprice := 0

 

[lbass]

What happens if you use this formula placed in the detail section?

whileprintingrecords;
numbervar firstprice;
shared numbervar baseprice;

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

-LB

 

[bslaast]

I get an error that the remaining text does not appear to be part of the formula

else
firstprice := firstprice;
(firstprice - {PURCHASE_ORDER_LINE.FBUY_UNIT_PRICE})*{PURCHASE_RECEIPT_INVOICE.QTY_ARRIVED}

 

[lbass]

Sorry. Try:

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

-LB

 

[bslaast]

No. Still using the baseprice.

 

[lbass]

Do you mean it is still using the basedprice even when it is 0? Because this is set up to use the baseprice unless it is zero.

Can you verify that baseprice is appearing correctly in the detail section of the main report?

Also, I don't understand why you are linking on a date field. What fields are you actually linking, and why?

-LB

 

[bslaast]

I take that back. It uses the next price if there are multpile purchases in the time period. But if there is only one in the time period, it uses the base price.

for example:

Part One
Price: 23.20 Qty: 100 Savings: 0.00
23.20 55 0.00
23.20 155 0.00

It should still take the quantity into consideration. Even though they are the same price, the second purchase was for 45 parts less. It should still show a savings of $1044. And on the third time 55 more were purchased, so a loss of -$1276.

Part Two
Price: 20.75 Qty: 10 Savings: -8.00

I'm not even sure were the -8 is coming from.

 

[lbass]

You need to add a reset formula for your shared variable in the group #2 footer:

whileprintingrecords;
shared numbervar baseprice := 0;

Are you placing my formula in the detail section?

To see what's going on, please also add these formulas to the detail section:

whileprintingrecords;
shared numbervar baseprice;

whileprintingrecords;
numbervar firstprice;

Then report back with results.

-LB

 

[bslaast]

It is working correctly if the base price is not 0. If it is 0 uses the first price when there are multiple purchases. If it is 0 and there is only one purchase, I have no idea what it is doing.

The baseprice is appearing correctly in the details.

I am likning dates, because when users run the report they want to choose start and end dates. In order to find the base price, the subreport needs to know when the last purchase was outside of the date parameters. I am linking on the beginning date parameter and purchase receipt part no. I have a filter to find dates < beginning date. Then a formula field to find the maximum date.

 

[lbass]

Please try what I suggested in my last post and report back as I don't see why you wouldn't be getting non-zero values for the savings (my formula), even if the baseprice is 0.

-LB

 

[bslaast]

Yes, your formula is in the detail section.

It looks like when baseprice is 0 and it was only purchased once, it is using the next detail record as the firstprice.