Is it possible to create a report that shows the difference between AP and AR from the same vendor. (i.e the vendor is also the customer).
Solved
AP and AR Difference
Best answer by bryanb39
Do you need a report? Or will a Generic Inquiry work? Either way, starting with a GI is an easy way for proof of concept.
When customers and vendors are extended they share the same BAccountID.
Start with the BAccount Table and join to Vendors and Customers, then left Join BAccount to the respective tablse, ARTran and APTran for example, or Payments using BAccountID.
To return only those that are extended, you can simply add an inner join between Vendor and Customer with BAccountID or add isCustomerOrCombined to the BAccount - Vendor and BAccount - Customer relationships.
If you did not extend all, you can leave the isCustomerOrCombined off and create relationships using Parent/Child relationships or even an attribute to get the rest, this may require some self joins. Let us know if you need help with that. This may also slow down your GI significantly with high volume of records.
Then you can join BAccount to APTran, ARTran, Payment, etc.
Once you feel good about your relationships and tested out some formulas, you can:
Create a Pivot Table from the GI for easy side by side comparisons.
Create a Dashbaord so you can plot some bar and line charts and display the Pivot Table.
Or export the GI as a report if you want to use a report for some more advanced comparisons.
You inspired me to do the same in my environment, this can be very useful. In my case many were not extended so I’ll likely be adding an attribute.
+19Hello,
Yes I think it’s possible for you to design a report that shows both AP and AR documents, but not easy.
Subreports may be needed, and useful for grouping & calculating AR versus AP then passing balances back to the main report. Look to the Aging and Balance reports in AR and AP for examples of how to identify open docs and calculate proper customer/vendor totals.
I hope every customer/vendor has been Extended to the opposite module, meaning they have the same ID in AR versus AP.
Good luck!
Laura
Laura ~ Klear Systems, Inc. Irvine, CA. ~ Click my logo to Follow me ~
+6Do you need a report? Or will a Generic Inquiry work? Either way, starting with a GI is an easy way for proof of concept.
When customers and vendors are extended they share the same BAccountID.
Start with the BAccount Table and join to Vendors and Customers, then left Join BAccount to the respective tablse, ARTran and APTran for example, or Payments using BAccountID.
To return only those that are extended, you can simply add an inner join between Vendor and Customer with BAccountID or add isCustomerOrCombined to the BAccount - Vendor and BAccount - Customer relationships.
If you did not extend all, you can leave the isCustomerOrCombined off and create relationships using Parent/Child relationships or even an attribute to get the rest, this may require some self joins. Let us know if you need help with that. This may also slow down your GI significantly with high volume of records.
Then you can join BAccount to APTran, ARTran, Payment, etc.
Once you feel good about your relationships and tested out some formulas, you can:
Create a Pivot Table from the GI for easy side by side comparisons.
Create a Dashbaord so you can plot some bar and line charts and display the Pivot Table.
Or export the GI as a report if you want to use a report for some more advanced comparisons.
You inspired me to do the same in my environment, this can be very useful. In my case many were not extended so I’ll likely be adding an attribute.
https://www.fiverr.com/acudatica
Thank you for your advices. I will try again following your advices and let you know if there is a problem. As a beginner, it is a big challenge for me.
1 person likes this
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.