Skip to main content
Solved

Need Help Table Relationship to Get the Payment of all invoices which is Closed


Forum|alt.badge.img

I am Trying to  create a Report to Find out the List of invoices Paid in Full /status closed based on Payment Date  with Following Fields SO Order number SO Type , Payment Date Customer , Invoice # , Date etc   Following tables were Used ARTran>SOorder, ARTran> ARInvoice, ARInvoice>ARADjust , I grouped it by Customer and when trying to get Subtotal for Customer , The result is getting Multiplied with the Number of Line Items in the Invoice Document .  How to resolve this ?

 

 

 

Best answer by Laura02

Hello,

Yes it is possible to link from AR Invoice to Shipment and Sales Order. Join on Order Type and Order number as in this example:

I recommend starting with Invoices and Payments: make sure you have correct totals for your date range.  Then proceed to join additional tables one at a time. Good luck!

Laura

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

5 replies

Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • April 12, 2024

Hello,

To show a list of paid invoices in full by payment date, I do not think you need ARTran table. (Do your customers pay invoices by the Line?)

Did you notice, and try the solution in this previous post asking the same question?

https://community.acumatica.com/financials%2D7/payments%2Dfor%2Da%2Dgiven%2Dperiod%2D17700

If your report/GI is not working, please show screen shots of your Table Relations, what you expect to see, and the incorrect, duplicated data in the results.

Thank you.

Laura

 

 


Forum|alt.badge.img
  • Author
  • Freshman II
  • 44 replies
  • April 12, 2024

Thank You i Just got noticed to this and working on my report  , is it possible to include  Sales Order Type and Sales order number to  it . I added ARTRAN Table  ,  which is multiplying the Payment values with number of line items when am Grouping and Adding sub totals 


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • Answer
  • April 12, 2024

Hello,

Yes it is possible to link from AR Invoice to Shipment and Sales Order. Join on Order Type and Order number as in this example:

I recommend starting with Invoices and Payments: make sure you have correct totals for your date range.  Then proceed to join additional tables one at a time. Good luck!

Laura


Forum|alt.badge.img
  • Author
  • Freshman II
  • 44 replies
  • April 13, 2024

@Laura02 Thank You for the Support , It worked !!!! One thing is when adding SO order and Shipment Tables  , Loading time of report  extended  it showing  searching all records across the tables and line items  i tried in GI  by Extending the Limit to 2000 still not loading ‘System was not able to get record Count’

 


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • April 15, 2024

Hello @jvarughese23 ,

I recommend starting a new post for the performance issues - this way more people will see your question.  Show all of your joins with screen shots. Estimate the volume of transactions for the helpers here on the community and someone will have suggestions.

Thank you.

Laura


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