Formula help- comparing values 1

[hencyt]

I have a report that lists all the receipts for a given item. I want to narrow it to include only the receipts up to the current qty on hand amount. So if item A currently has 1000 OH, then I want to see a list of the last receipts up to 1000. The problem I am having is that the reciepts do not add up to 1000 exactly, of course. I have a running total, adding up the receipt qtys. I have used that running total in this formula:
if {#RTotal0}<= {IM1_InventoryMasterfile.TotalQtyOnHand} then "1"
else "2"

Then I can use section formatting to hide the "2" values. Except I really need to see the first of the "2" values to make the list correct.
example:
OH = 1000

list:

recpt 400 RT:400
recpt 200 RT:600
recpt 250 RT:850
recpt 100 RT:950
recpt 250 RT:1200

my formula will mark the first 4 values with "1" and the last with "2".

What change can I make to be able to see the last value?

CR 8.5

Thanks,
Sunny

 

[Madawc]

Use a pair of running totals, counts, one based on your '1' rule and the other on the '2' rule. Then use these counts for line suppression, greater than 4 and greater than 1.

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

 

[hencyt]

Thanks Madawc for the reply.
I guess I should have mentioned this was just an example and it will take varying amounts of receipts to equal the on hand qty. All of the values (number of receipts and QOH)will vary from item to item.

Somehow the formula will have to compare the QOH to the running total of receipts...and show me all the receipts up to the first on that is great than or equal to the QOH.

Hope that helps. Thanks in advance!

 

[lbass]

Try the following:

if {#RTotal0} <= {IM1_InventoryMasterfile.TotalQtyOnHand} or
(
{#RTotal0} > {IM1_InventoryMasterfile.TotalQtyOnHand} and
{#RTotal0} - {table.recpt} < {IM1_InventoryMasterfile.TotalQtyOnHand}
) then "1"

Although you could use 1, not "1" as the result.

-LB

 

[hencyt]

Thanks LB!! That worked great.

Seems so simple after you see the solution, but I didn't think of it!

hencyt