Skip to main content
Solved

Multiple payments applied to an AP invoice

  • 22 May 2024
  • 4 replies
  • 45 views

I would like to have a generic enquiry that lists out any AP invoices that are still open and have multiple payments applied.  Can someone advise how to get started.  I don’t know which tables I would need and how to join them.

Thanks

Here is an example of the data I am looking for:

 

4 replies

Laura02
Rank
Badge +18

Hello,

I recommend starting with APAdjust.  APAdjust holds the relationship between AP Bill documents and the documents that pay them (Debit Adjustments, Payments, Prepayments).

To identify bills that have multiple payments, you’ll want to add a Count field to count the number of times the same Bill-Reference number appears in the results.

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~
iqraharrison
Rank
Userlevel 7
Badge +8

@pcraske31 if you have the customizer security role you can use the “inspect element” option to find out what tables fields show up:

here’s the table info @Laura02 was spot on!

Iqra ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Naveen Boga
Rank
Userlevel 7
Badge +19

@pcraske31  Below is the SQL query, and you can refer this query and add the JOINS in the GI to fetch the AP Bill and Payment details.

 


Select * from APInvoice AI Inner join APAdjust AA ON
AI.DocType = AA.AdjdDocType and AI. RefNbr = AA.AdjdRefNbr
Where AI.RefNbr='005295'

 

 

Hope this helps!

Naveen B
P
Rank
Userlevel 2

Thank you everyone for your replies.  I’ll get to work and update you on my progress.

Reply


Terms & ConditionsCookie settings