Skip to main content
Solved

Report Designer - Present All Released Transactions From Yesterday


Howdy Acumatica Community,

I am looking at scheduling a daily report to Accounts Payable listing all released purchase receipt documents from the date before. 

Fun twist on this one; the POReceipt.ReceiptDate field isn't working for me as this is using the document date. We have segregation of duties and the date the receipt is released is not always the same date of the document. I fear that by hardcoding a daily report using the POReceipt.ReceiptDate field will present bad data to our AP team.

image

Any thoughts/advice out here on how to present all released PO receipts from yesterday?

Thanks!

Best answer by BenjaminCrisman

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

View original
Did this topic help you find an answer to your question?

13 replies

Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • September 22, 2020

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.


  • Author
  • Freshman I
  • 9 replies
  • September 22, 2020
RoyceLithgo wrote:

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!


Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • September 22, 2020

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.


  • Author
  • Freshman I
  • 9 replies
  • September 23, 2020

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?


Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • September 23, 2020

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.


  • Author
  • Freshman I
  • 9 replies
  • September 23, 2020

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.


Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • September 23, 2020

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.


  • Author
  • Freshman I
  • 9 replies
  • September 24, 2020

 

How is that done in report designer?


Forum|alt.badge.img+3
  • Pro II
  • 76 replies
  • September 24, 2020

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/


  • Author
  • Freshman I
  • 9 replies
  • September 24, 2020

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.


BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 630 replies
  • Answer
  • October 8, 2020

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


  • Author
  • Freshman I
  • 9 replies
  • October 8, 2020

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.


rodman
Freshman I
  • Freshman I
  • 7 replies
  • November 7, 2020

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


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings