Find the most recent transaction of an ID

[davidanthonyb]

Hello,

I am looking to find the most recent transaction/historic record per ID/primary key. Obviously I can filter to show all transactions per ID, or any within a specified date range, but how would I go about finding the most recent?

For instance if I have Inventory ID 001, and want to see only the most recent addition or subtraction of quantity - how would I do that?


Thanks,
David

[Alex K.]

Hello David

Sorry, maybe we did not exactly understand your task. Could you explain your issue in more details?

Thank you.

[davidanthonyb]

Hi, thanks for the reply, and absolutely.

I need to run a report that shows only the most recent transaction PER Inventory IDs for ALL Inventory IDs

Say I have Inventory ID 001
On Monday 1/30/17 I received 20 units
On Tuesday 1/31/17 I received 50 units
On Wednesday 2/1/17 I received 10 units

I also have Inventory ID 002
On Monday 8/8/16 I received 10 units
On Tuesday 8/9/16 I received 20 units
On Wednesday 8/10/16 I received 30 units

I need to run a report showing me only the most recent transactions PER Inventory ID:
ID Date Quantity
001 2/1/17 10
002 8/10/16 30

Hopefully that makes more sense(sorry for the US date formatting), but this would be very helpful if possible. Is there a way through filtering I can say to only show Table1.InventoryID.MaxDate or something along those lines?

Thanks so much in advance, as this would be a huge help!
David

[HighAley]

Hello, David.

You could add A Group Header and make all calculations there.
After this, remove all components from the Data Band and change its Height to zero.

Please, look at the attached report template.

Report.mrt

(6.48 KiB) Downloaded 345 times

Thank you.

[davidanthonyb]

Hi HighAley,

Worked great, thank you very much!

Thanks,
David

[Andrew]

Thank you, David, for he reply.

Have a nice day!

[DustinDeTorres]

Thanks needed this.

[Andrew]

Hello,

I am happy that this forum thread was helpful for you.