Hi guys,

I'm just after a little bit of help with a report I am writing for work. I'm a relative newbie when it comes to Crystal Reports. I'm using CR XI. Here is my problem...

I am trying to write a report that lists how much usage we have used on certain stock, whilst ignoring any reverse transactions.

Whenever an item is issued, the transaction itself gets a Unique Transaction Number. If (for whatever reason) the transaction is reversed and added back into stock, our system gives the reversal its own transaction number and also writes the original transaction number to a field called 'originaltransactionid'. So for example...

Qty Description Type Transactionid Originaltransactionid

20 M6 Nuts Standard 12345
50 M6 Nuts Standard 67890
20 M6 Nuts Reversal 98765 12345

where the 3rd line is the reversal of transactionid 12345.
I would like the report to disregard any of the REV items (which is easy) but also to disregard the original transaction based on the 'originaltransactionid' number stored in the reversal transaction (so on my example above, only the 2nd line would show up on my report and only 50 nuts would be deducted from stock).

Thanks in advance for your help.

That would be an unmatched query, with a self join... Something like so:

Select Yourtable.*
from yourtable
left join yourtable as yourtable_1 on yourtable.Transactionid = yourtable_1.originalTransactionid
Where yourtable_1.originalTransactionid is null

P.S. Welcome to AWF

Namliam, thanks for the reply and the welcome to the forums.

I really think I'm now officially 'in over my head' here :o

Ok. My report uses two tables. One called stockbatches & one called stocktransactions. Data is collected from both tables but the transactionid & originaltransactionid fields are stored in the stocktransactions table.

Could you replace the 'yourtable' bits with my table names please? Also where do I put this code (told you I was a noob :))? Do I put it in the select record formula editor (or just in the 'select expert' bit).

Thanks again

It is a query...

Try this:
Make a new query, add your table "StockTransactions" 2 times
Now from the one table "drag" the "TransactionID" to the "OriginalTransactionID" of the other table.
Double click on the line that is created and change in the popup screen from "1" to "2". Upon returning you should see an Arrow going from TransactionID to Original. If not, double click again the line and change it to "3"
Now add the "OriginalTransactionID" to the query (double click it) and add in the criteria "Is Null"

Now add the columns from the first table, this should result in what you are looking for...

Fantastic, thank you ... worked like a treat.
V much appreciated

oh and sorry for being a little bit dim :D