Display "Last Transaction Date" 1

[blueboyz]

I am using Quantum 2009 with Crystal Reports v2008, Pervasive SQL v10.

I am creating a report to list all the inventory items, the quantity on hand and the Last Date the item was used in a transaction.

Quantum stores the Item ID in the LineItem table.
All the transactions are stored in the JrnlRow table (quantity, item id, journal description, date of transactions, unit price and amount).

I need to list all the items on the report whether they have been used in a transaction or not.
I need to display the most recent date the item was used in a transaction. My problem is twofold:

1) How do I only list the last date the item was used in a transaction and not every date the item has been used in a transaction?
2) How do I list all the inventory items - not just those used in a transaction.

Tables: JrnlRow and LineItem

The report is grouped by Item ID and I am only displaying the Group Footer so that I don't get the items listed more than once.
I tried creating a record selection formula to display only the last transaction date for an item:
{@Last Used} = {JrnlRow.RowDate} > Previous({JrnlRow.RowDate})

I get the error "This function cannot be used because it must be evaluated later".

Item ID Qty on Hand Last Used
A 100 4/3/09
B 200 2/3/09
C 500
D 250 5/1/09
E 125
F 102 1/15/09

 

[ianoctdec]

Hi,

Link LineItem and JrnlRow with a left outer join from LineItem to JrnlRow.
To show the last transaction date, insert a maximum on transaction date for the group Item ID.


Dana

 

[blueboyz]

Thank you so much, Dana. That suggestion did the trick.

 

[lbass]

Since you are using the group footer, you could add a record sort on transaction date-ascending. Then the date that appears in the group footer will either be null or reflect the last date for that item.

-LB

 

[blueboyz]

Thank you, LB. I thought about doing a record-sort as well.