Solved

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

  • 12 April 2024
  • 5 replies
  • 41 views
J
Rank
Userlevel 2
Badge

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 ?

 

 

 

icon

Best answer by Laura02 12 April 2024, 22:44

View original

5 replies

Laura02
Rank
Badge +18

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

 

 

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
J
Rank
Userlevel 2
Badge

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
Rank
Badge +18

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

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
J
Rank
Userlevel 2
Badge

@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
Rank
Badge +18

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

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved