Querying the QuoteWerks Database for selected Line Items

In our years of working with QuoteWerks and providing integration solutions, it’s been a fairly common requirement to query the QuoteWerks database for Line Items on a Document which are either non-optional or optional and selected. How to do this relies on understanding the bitwise logic around the Document Items table LineAttributes field. More information on the LineAttributes field values can be found in the QuoteWerks Help and API guides; more information on evaluating bitwise login in T-SQL can be found in Microsoft online documentation; for anyone just wanting an example of how to achieve this, then just give this a try:

SELECT DocID, ManufacturerPartNumber, Description, LineAttributes
FROM DocumentItems WITH (NOLOCK)
WHERE (LineType = 1) --Product/Service
    AND (
        (LineAttributes & 16) <> 16) --not optional
        OR (
            ((LineAttributes & 16) = 16) --optional and selected
            AND ((LineAttributes & 256) = 256)
    AND (LineAttributes & 1) <> 1) --not excluded

PLEASE NOTE however that the above DOES NOT cater for Line Items that are part of a Group where the Group Header Line Item is optional and not selected… if you need to cater for this scenario, then please email development@hilltopsit.co.uk and we’ll see what we can come up with, but it’s probably something that needs to be done in code, rather than being a “simple” T-SQL statement.