Cross Referencing QuoteWerks Quote, Order and Invoice Numbers

We’ve been asked several times by clients how to cross-reference the Quote, Order and Invoice numbers that are processed through QuoteWerks. While you can get the Converted Ref in QuoteWerks (so the Order or Quote an Invoice was Converted from or the Quote and Order came from) there’s no a way within the reporting to get all three numbers together.

However, if you’re feeling adventurous and would like to do a report in Excel with the QuoteWerks database as a remote data source, then try copy/pasting the following query in. It should return you all Invoice Document Numbers with the relevant Quote and/or Order Numbers.

We hope this is of use,

Steve and the Hilltops IT QuoteWerks team.

SELECT ISNULL(DHQ.DocNo, ‘None’) AS [Quote Ref], DHO.DocNo AS [Order Ref], DHI.DocNo AS [Invoice Ref]
FROM QuoteWerks.dbo.DocumentHeaders AS DHI WITH (NOLOCK)
INNER JOIN QuoteWerks.dbo.DocumentHeaders AS DHO WITH (NOLOCK)
ON (DHI.DocNo = DHO.ConvertedRef)
LEFT JOIN QuoteWerks.dbo.DocumentHeaders AS DHQ WITH (NOLOCK)
ON (DHO.DocNo = DHQ.ConvertedRef)
WHERE (DHI.DocType = ‘INVOICE’)
AND (DHO.DocType = ‘ORDER’)
AND ((DHQ.DocType = ‘QUOTE’) OR (DHQ.DocType IS NULL))
UNION
SELECT ISNULL(DHQ.DocNo, ‘None’) AS [Quote Ref], ‘None’ AS [Order Ref], DHI.DocNo AS [Invoice Ref]
FROM QuoteWerks.dbo.DocumentHeaders AS DHI WITH (NOLOCK)
LEFT JOIN QuoteWerks.dbo.DocumentHeaders AS DHQ WITH (NOLOCK)
ON (DHI.DocNo = DHQ.ConvertedRef)
WHERE (DHI.DocType = ‘INVOICE’)
AND ((DHQ.DocType = ‘QUOTE’) OR (DHQ.DocType IS NULL))