Hello, does anyone have an inquiry that lists bills and the payments applied to the bills? I am having trouble with my inquiry. The joins seem find but not all results are populating.
Best answer by aaghaei
View original
+18
Hello,
Please post screen shot of your inquiry and results so that we may help you with it.
Userlevel 1
Hi Laura, Thank you for the response. I am having trouble gathering all the fields in a screen capture. I’ve attched the xml file.
+18
Hello Lindsey,
In your results, I think you’ll want DocType and Refnbr (not “display”) in your results.
Do you have more than one Company or Branch. If so, join the tables on Company/Branch fields as well as doctype and refnbr.
If you see duplicates, can you confirm whether there are multiple payments to one bill, where you see multiple lines? Thank you.
Userlevel 1
I made the recommended changes and it did not seem to work as expected.
Relations:
Resutlts: I added AdjdDocType instead because DocType is not available:
Adding AdjdDocType and AdjdRefNbr returned the AP Bill Document type and Reference Number rather than the payment.
With this inquiry there are actually less records than in my AP Bills and Adjustments GI. I expected more as you mentioned - I thought it would list multiple records for documents with multiple payments applied.
Userlevel 7
+8
As the APAdjust name suggests it contains ONLY the adjustments (including Payments) made to the original Bill/DebitAdj/CreditAdj that change the original document balance. If a bill doesn’t have a posted subsequent transaction, then there will be no trace of the orig doc in APAdjust.
+18
I recently completed a “Bills with Payment Details GI” shown below. I remembered the “display” fields being blank until I made the change but, you’re correct, I was using Adjusted refnbr. Our customer doesn’t use Contracts so I was able to successfully show Bills & adjustments with 3 tables. If this doesn’t help you then someone smarter than I will come along!
+18
Aaghaei is saying, you need a left join where APInvoice is connected to APAdjust. His recommended change will pick up unpaid bills, showing blanks for payment fields. (That’s what I think he is saying :-)
Userlevel 7
+8
What
You might be able to make it work if you aggregate on ALL of the non-numeric fields you will be displaying from APInvoice and DO NOT use any non-numeric field from APAdjust. Also, your APAdjust numeric values will need SUM and APInvoice numeric values MAX or MIN in your GI.
Userlevel 7
+8
Aaghaei is saying, you need a left join where APInvoice is connected to APAdjust. His recommended change will pick up unpaid bills, showing blanks for payment fields. (That’s what I think he is saying :-)
That is correct. You read my mind and well expressed my broken English. Thanks 🤣
+18
A little mind reading helps us to answer questions on Acumatica Community! 😃
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.