If you are looking to find all PO Receipts released since yesterday your only option is resort to LastModifiedDateTime. But this isn’t foolproof as i believe a note being added to a released PO Receipt will update the LastModifiedDateTime. If you don’t add notes to released receipts then you might be OK.

Unfortunately, the last modified date time on the purchase receipt is updated when AP creates a bill from the receipt. Might just require a process & technical solution here.

Thank you!

On the Other Information tab of PO Receipt is IN Ref Nbr. This only gets populated on Release. Join this number to INRegister to get the IN Receipt associated with the PO Receipt, then look at the CreatedDateTime on the INRegister record. That should tell you when the PO Receipt was released.

In 2018 R1, the screen is not updated. However, the tables have the required field to join to INRegister!

Now if only I could manage the joins so that only one row would show up.

Here we see all the joins for all the rows matched on the POReceiptLine table:

and here are the joins for the tables:

any advice?

When joining parent to child tables you should always include all keys - you missed ReceiptType. But as you’ve also joined POReceiptLine, then you will get multiple rows for each line unless you’ve applied aggregation to the POReceiptLine records. If you’re not sure where the additional lines are coming from then its always best to build your GI incrementally by adding 1 record after the next to see where the data explosion is coming from.

Yup, the duplicate lines are for all the rows on each receipt. I’ve tried aggregation but have failed thus far as this is all in report designer.

You need to set the Grouping fields appropriately and make sure all POReceiptLine fields included in the Results tab have an aggregate function selected. Is there any reason why you’re doing aggregates of the Lines? Most of the totals are already available on the POReceipt record.

How is that done in report designer?

If that’s your report output above, then you don’t need POReceiptLine at all.

Ortherwise perhaps you want to look at the Report Designer training on open uni. It depends on how you want to present the data in the report.

https://openuni.acumatica.com/courses/reporting/s130-inquiries-reports-and-dashboards/

The POReceiptLine table is there to get the PO Order Number field. In 2018 R1 the POReceipt table does not have the PO Order Number field.

Hi Michael!  For this situation it could get a little complex to get the result you’re looking for, but I think the easiest way is going to be using the Audit History DACs to snag the time of release.

You can make a SQL view to join to the Audit History and then use this DAC in the report to be able to filter on the value in the Audit date being greater than Today()-1.

https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/

I think this would be the cleanest way to implement this request

Thanks Ben! I’ve dabbled in DAC’s before and this makes sense. 

The update I have for this query is that I’ve gone back to the business and am accomplishing their request with training on the use of the purchase receipt primary list. Reporting this sort of information contradicts the purpose of an ERP… IMHO.

I would be so much simpler if there was a POReceipt.ReleasedDate field that we could tap into.