How can I add the PO # to the Bills and Adjustment generic inquiry screen?
I added the related table APTRAN but then I had duplicate invoices.
What would the proper “join” be to get this info?
Answer
AP3010PL - Add PO # to Bills and Adjustment
Best answer by wfilipiak67
Hi Sarah,
Here are 2 ways of doing this. Both assume that there is only 1 PO per Invoice.
You can have the Join only read the first line number.
Or you can do a grouping on the APInvoice.RefNbr.
If you do the second, remember to set the Aggregate Function to Average on the Numberic fields to avoid adding the document total for each line. (6 lines * doc toal of 1000 would show 6000 not 1000)
Hope this helps!
+3Hi Sarah,
Here are 2 ways of doing this. Both assume that there is only 1 PO per Invoice.
You can have the Join only read the first line number.
Or you can do a grouping on the APInvoice.RefNbr.
If you do the second, remember to set the Aggregate Function to Average on the Numberic fields to avoid adding the document total for each line. (6 lines * doc toal of 1000 would show 6000 not 1000)
Hope this helps!
Thank you - I will give these a try and see if I can get the PO # to show up on the inquiry.
We do only have one PO per invoice but there may be multiple Invoices for one PO.
Thanks for the screenshots, they are very helpful!
Sarah Bond
What if you have more than one invoice on the same PO? Is there a join that works for that, or is this the only option?
+3
I think that should work still.
Each Invoice would have an APTran line that would tie to a the PO.
So different invoices could reference the same PO.
Thanks for the reply. I am trying to get the PO # on our ACH remittance report that goes out to the vendor when we make payments. We are in distribution and there are many times that we get multiple invoices on the same PO because it doesn’t ship complete, or we have a scheduled release. Right now, it appears to be working as you described earlier where there can only be 1 PO per Invoice, or only 1 line item on the PO. If the same PO # is used on another invoice on the same ACH payment, then it only shows on the first invoice paid and is blank on the others. Not sure if there is any other way to do this the way it is structured.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.