BAQ Vendors with Open POs

Kinetic ERP
1

Hello~

I have a BAQ here for APInvHed and Vendor which shows me suppliers, their location, the last invoice date, etc.

I want to see if they have any open POs out there. How can I do this? I attached POHeader to Vendor but when I brought up OpenOrder field it gave me a bad SQL error.

Thank you.

VendorCleanup.baq (20.1 KB)

2

Did you add OpenOrder to the GroupBy?

2 Likes
3

If you’re looking to return only open orders (PONums etc.) you may also want to set a Table Criteria on POHeader where OpenOrder = true.

4

If you want to know how many open POs each vendor has, you could create a subquery of open PORel’s. Group by VendorNum and COUNT the number of PORels.
Then LEFT OUTER JOIN that subquery to your current query on vendornum.

If, on the other hand, you want to list the open PO’s, then instead make the current query a subquery. In the new Toplevel query, LEFT OUTER JOIN the subquery to the PORel table and display PONum, POLine, PORel, etc.