Skip to Content

Only Pull Most Recent Record out of Table/Filter by Most Recent Crystal Report 16

I am trying to make a report that is combining two tables and currently, I am having a lot of trouble with it.

When the report opens, it asks for a Cutoff Date Input by the user (I will use 10/31/2017 for this example)

Then, the report will filter so it will only show records where both the DATE_HISTORY and DATE_ORDER are from before 10/31/2017 (I italicized the records in the table example that shouldn't be 'showing' in the report due to filters. It will also filter out if any NEW_ON_HAND = 0 or less.

Table_A : DATE_HISTORY , NEW_ON_HAND , PART
PART NEW_ON_HAND DATE_HISTORY
001 15 08/24/17
001 8 09/15/17
001 4 10/15/17
001 7 11/17/17
002 32 8/15/17
002 54 11/18/17

Table_B : DATE_ORDER , ORDER_NO , PART , QTY_ORDERED
PART ORDER_NO QTY_ORDERED DATE_ORDER
001 123456 5 09/23/17
001 123457 3 10/25/17
001 123458 2 11/31/17
002 125879 3 8/15/17
002 123587 4 11/31/17

Since I only want the report to take the date that is most recent to the Cutoff Date, I currently have the Report grouped by PART then ORDER_NO then by DATE_HISTORY.

The report currently print out something like (Where Inventory Count is If {TABLE_A.NEW_ON_HAND}<{TABLE_B.QTY_ORDERED} then {TABLE_A.NEW_ON_HAND} else {TABLE_B.QTY_ORDERED}:

PART ORDER NO NEW_ON_HAND QTY_ORDERED INV. COUNT
001 123456 4 5 4
001 123457 4 3 3
002 125879 32 3 3

However, I want to adjust/add a formula on the column NEW_ON_HAND, so if the part is listed twice in two different orders, it subtracts it for the record after it, showing that they in face do not have 4 for the next record. I am currently using the formula: f {TABLE_B.PART}=Previous({TABLE_B.PART}) then ({TABLE_A.NEW_ON_HAND}-(Previous({TABLE_B.PART}))) else {TABLE_A.NEW_ON_HAND} I would like it to work like the example below:

PART ORDER NO NEW_ON_HAND QTY_ORDERED INV. COUNT
001 123456 4 5 4
001 123457 1 3 1
002 125879 32 3 3

Because I have the records grouped together, it isn't seeing the GROUP as one record, so instead of looking like the above, it ends up looking like, It is counting the grouped records, as previous records (because there is more than one quantity on hand):

PART ORDER NO NEW_ON_HAND QTY_ORDERED INV. COUNT
001 123456 -1 5 4
001 123457 1 3 1
002 125879 29 3 3

How would I get the sheet to pull ONLY the first record that is most recent (to the CutOff Date) so then I can perform the inventory calculations on the site.

I'm thinking the best way to do that would be an SQL Command, but to be honest I know nothing about Crystal Report and am having a hard time finding a way to do it.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers