FORMULA TO FIND A PREVIOUS RECORD

[DAVE3334]

CR Ver 8.5/WinXP/Pervasive DB

Need help finding a formula I can use to find a previous record that matches a value in the current record.

Example:

Current Record:

order #: 3344
item: CDR34
qty ordered: 1
qty on hand*: previous 'qty on hand' for this item -1

*I need to insert a formula for 'qty on hand' that finds the qty on hand for the last order containing item CDR34 and subtracts from that the qty ordered on this order (in orther words, a running total of qty on hand for this item).

Can someone assist? Thanks in advance.

 

[lbass]

Where does the initial qty on hand amount come from? Please show some sample data for several records, using a horizontal display, that shows the initial amount, along with the number that should appear for qty on hand. If the initial qty on hand is a formula, please show the contents.

-LB

 

[synapsevampire]

Why would you need to do math against the previous qty on hand if it's already in the table as a field?

Please think through your post and you'll be better served to show example data and expected output as descriptions rarely convey requirements well, hence technical documentation is created.

You might try sorting or grouping the data by the part number and the datetime stamp.

Then I guess you might check to see if the previous qty on hand is empty and if not generate one:

if isnull({table.qtyonhand}) then
previous{{table.qtyonhand})-table.qty
else
{table.qtyonhand}

This is probably way off, so try posting what you have and what you need rather than trying to describe it.

-k

 

[DAVE3334]

Sure. Thanks Ibass.

Using column names for this example of:
Order #, Item #, Qty Ordered, Updated Qty On Hand

The result should be...

1000, RD34, 7, 14
1001, RX99, 1, 5
1002, RD34, 6, 8 (14-6=8)
1003, RD34, 3, 5 (8-3=5)

All values come directly from the source data, except the 'Updated Qty on Hand' field; which needs to be this formula that subtracts the Qty Ordered on the current line from the Updated Qty on Hand of the most recent previous order containing the same item. Thanks~

 

[DAVE3334]

Thanks synapsevampire,

The data for Qty on Hand isn't in the table, so I have to calculate it. And sometimes the previous record won't be for the same item - so I need it to go back until it finds the last record with the same item and returns that line's qty on hand.

-Dave

 

[lbass]

You still have not answered the question about where the very first quantity on hand comes from or what it is based on.

-LB

 

[DAVE3334]

That number is in the data table; in the 'QTY ON HAND' field.

Thx
Dave

 

[lbass]

You did just say it wasn't in the table. If it is in the table, then is it always the same for a particular item?

-LB

 

[DAVE3334]

No. It changes (gets reduced) after each order that contains that item. Only the first order containing the item should use the value from the data table. All lines after that should use this formula I am trying to build.

-Dave

 

[synapsevampire]

Great, post the result but not what you currently have in the table...

I urge you to take the time to understand what is being asked, and respond accordingly, you're making conflicting statements and not providing what is asked.

You're wasting everyone's time.

-k

 

[synapsevampire]

So the first row has the data that you need for the formula. Read through your posts and see if you indicated that anywhere...

Group by the part number, then sort by the date.

Group header formula:

whileprintingrecords;
numbervar MyQty:= {table.qtyonhand};
numbervar X:=0

Details formula
whileprintingrecords;
numbervar MyQty:= {table.qtyonhand};
numbervar X:;
if x > 0 then
(
MyQty := MyQty - {table.ordered};
x := x+1;
)
else
x := x+1;
MyQty

-k

 

[DAVE3334]

My apologies. Let me start over.

I have the following two tables as source data:

ORDERS
{Orders.order#},{Orders.item#},{Orders.qty_ordered}

1000, RD34, 7
1001, RX99, 1
1002, RD34, 6
1003, RD34, 3

ITEMS
{Items.item#},{Items.orig_qty_on_hand}

RD34,21
RX99,18


The tables are linked by Item#

I need to get to the following output:

fields= order#, item#, qty_ordered, updated_qty_on_hand

1000, RD34, 7, 14
1001, RX99, 1, 17
1002, RD34, 6, 8
1003, RD34, 3, 5

See how the updated_qty_on_hand changes on each line for a given item? I just need to find a formula that lets me lookup the updated_qty_on_hand
for the last order containing this item and subtract the current line's qty_ordered


Thanks,
Dave

 

[lbass]

First, you should group on customer and then on item #, and sort ascending on order ID. Then insert a running total {#qtyordered} that is a sum of quantity on hand, evaluate for each record, reset on change of group (itemID).

Then create a formula {@qtyonhand}:

{item.origqtyonhand} - {#qtyordered}

Place this in the detail section.

-LB

 

[synapsevampire]

I think LB meant group on ORDER, not customer.

The method described will do so by the order and then item, which seems correct.

-k

 

[lbass]

No, actually, I don't think there should be a group on order, since he wants to compare item numbers across orders.

-LB