The following example will display the line items for a specific Purchase Order number.

Microsoft SQL Query Sample 1

SELECT AP7PURCHASEORDERS.PURCHASEORDERNUMBER, AP7RECEIPTS.DESCRIPTION AS [Receipt Description],
AP7LINEITEMS.DESCRIPTION AS [LineItem Description], AP7LINEITEMDISTRIBUTION.GL7ACCOUNTSID,
GL7ACCOUNTS.ACCOUNTNUMBER, GL7ACCOUNTS.DESCRIPTION, GL7PROJECTS.DESCRIPTION

FROM (((AP7LINEITEMDISTRIBUTION RIGHT JOIN(((AP7PURCHASEORDERATTRIBUTES RIGHT JOIN (AP7PURCHASEORDERNOTES
RIGHT JOIN AP7PURCHASEORDERS ON AP7PURCHASEORDERNOTES.PARENTID = AP7PURCHASEORDERS.AP7PURCHASEORDERSID) ON AP7PURCHASEORDERATTRIBUTES.PARENTID = AP7PURCHASEORDERS.AP7PURCHASEORDERSID)
LEFT JOIN AP7LINEITEMS ON AP7PURCHASEORDERS.AP7PURCHASEORDERSID = AP7LINEITEMS.AP7PURCHASEORDERSID)
LEFT JOIN AP7RECEIPTS ON AP7PURCHASEORDERS.AP7PURCHASEORDERSID = AP7RECEIPTS.AP7PURCHASEORDERSID) ON AP7LINEITEMDISTRIBUTION.PARENTID = AP7LINEITEMS.AP7LINEITEMSID)
LEFT JOIN GL7ACCOUNTS ON AP7LINEITEMDISTRIBUTION.GL7ACCOUNTSID = GL7ACCOUNTS.GL7ACCOUNTSID)
LEFT JOIN BBTRANSACTIONDISTRIBUTIONS ON AP7LINEITEMDISTRIBUTION.BBDISTRIBUTIONSID = BBTRANSACTIONDISTRIBUTIONS.BBDISTRIBUTIONSID)
LEFT JOIN GL7PROJECTS ON BBTRANSACTIONDISTRIBUTIONS.GL7PROJECTSID = GL7PROJECTS.GL7PROJECTSID

WHERE AP7PURCHASEORDERS.PURCHASEORDERNUMBER=23423

Microsoft SQL Query Sample 2

SELECT
PO.[PURCHASEORDERNUMBER],
REQUESTED_BY.DESCRIPTION as 'REQUESTED_BY', -- Requisitioner for Purchase Order
PROJ.[PROJECTID],
ACCT.[ACCOUNTNUMBER],
ACCT.[DESCRIPTION],
GLTRAN.ENCUMBRANCESTATUS

FROM
DBO.AP7PURCHASEORDERS AS PO
LEFT OUTER JOIN DBO.AP7LINEITEMS AS LINEITEMS ON PO.AP7PURCHASEORDERSID = LINEITEMS.AP7PURCHASEORDERSID
LEFT OUTER JOIN DBO.TABLEENTRIES AS REQUESTED_BY ON LINEITEMS.REQUESTEDBY = REQUESTED_BY.TABLEENTRIESID
LEFT OUTER JOIN DBO.AP7LINEITEMDISTRIBUTION AS LINEITEMDIST ON LINEITEMS.AP7LINEITEMSID = LINEITEMDIST.PARENTID
LEFT OUTER JOIN DBO.BBTRANSACTIONDISTRIBUTIONS AS BBTRANDIST ON LINEITEMDIST.BBDISTRIBUTIONSID = BBTRANDIST.BBDISTRIBUTIONSID
LEFT OUTER JOIN DBO.GL7PROJECTS AS PROJ ON BBTRANDIST.GL7PROJECTSID = PROJ.GL7PROJECTSID
LEFT OUTER JOIN DBO.GL7ACCOUNTS AS ACCT ON LINEITEMDIST.GL7ACCOUNTSID = ACCT.GL7ACCOUNTSID
LEFT OUTER JOIN DBO.BBDISTRIBUTIONS AS BBDIST ON BBTRANDIST.BBDISTRIBUTIONSID=BBDIST.BBDISTRIBUTIONSID
LEFT OUTER JOIN dbo.GL7TRANSACTIONS AS GLTRAN ON BBDIST.GL7TRANSACTIONSID=GLTRAN.GL7TRANSACTIONSID

WHERE
PO.[PURCHASEORDERNUMBER] IS NOT NULL

Disclaimer: Blackbaud provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Blackbaud Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.