The report you have shared does not include GRN_ITEM in the joins. What tables/fields are you trying to join it to? Very likely this is where the problem is as I am guessing you have added a join that is not specific enough.

I'm assuming that you'll want to join GRN_ITEM to POP_ITEM on the ORDER_NUMBER and ITEM_NUMBER to ORDER_NUMBER and ORDER_ITEM fields respectively. That should produce no duplicates and allow you to filter on the DATE field of GRN_ITEM

Hope that helps

I took out the GRN join because i tried various ways, POP_ITEM to GRN_ITEM using just order number, then description, item number, anything that was or looked similar (needless to say, I am very much a notice as sage report designer), tried similarly to join PURCHASE_ORDER to GRN_ITEM with no luck, either broke the report entirely or got anywhere between a single duplicate or a whole page, I think it was basically listing everything bit of activity for the product that was on the order, it was MESSY.

Anyway, I think I've done what you've suggested.

Didn't know you could do two joins on the same table, I got an error at first

Then sort of guess at what it was telling me, just typing out the 2nd join into the field boxes

Is this right?

It seems to be doing what I was hoping for upon an initial test.

Sorry, this got a bit wordy.

Thank you Darron!

Hi Ross,

Thanks for suing Community Hub.

I checked this with my colleagues in the Reporting team. They advised initially that it might be easier to start with customising a report that already includes the GRN_item table, like one of the Goods Received reports. 

If you continue to encounter issues, you can log a case online for this here: Submit a query online >

Regards,

Andy
Sage UKI

Hi Andy,

I did try that at one point, went back and forth trying to find the best base report to use and edit.

I encountered a similar problem with the Goods Received report, I needed to add POP_ ITEM to the joins and had the same issue.

Thanks

Yes, that is right and what I was assuming you were after. The UI is not has helpful as it could be when adding multiple joins to multiple fields but it looks correct from your screen shot. If you were only joining on the ORDER_NUMBER field previously you would have 'duplicates' as you'd be getting one row for every GRN_ITEM for that order e.g. 5 items on the order split across 2 deliveries would result in 10 rows in total.

Looks like you have the right joins now though so hopefully all good