Simplified Order Guides for Sales Orders

Use this query to display a list of items that a customer likes to buy. It was made to be placed on the Item Number field on the Sales Order as a Formatted Search. The User will press Shift + F2 to display the window as opposed to pressing tab to display the entire catalog of items.

Changing the Where clause to be a parameter will enable you to produce a printed order guide for a customer. If you would like to try out the query without making it a formatted search, just change the $[$-4.0.0] to your favorite customer number surrounded by single quotes.

SELECT DISTINCT
dtl.itemcode 'Item'
,itm.itemname 'Item Description'
,ROUND(AVG(quantity),0) 'Avg Ord'
,AVG(dtl.Price) 'Avg Price'
,AVG(ROUND(CASE WHEN dtl.linetotal = 0 THEN 0 ELSE dtl.grssProfit/dtl.linetotal END,2))*100 'AvgGP%'

,Count(docnum) 'Total Orders'
,MAX(hdr.docdate) 'Last Ordered'
,hdr.CardCode

FROM rdr1 dtl
INNER JOIN ordr hdr on dtl.docentry = hdr.docentry
INNER JOIN oitm itm on dtl.itemcode = itm.itemcode
WHERE HDR.cardcode = $[$-4.0.0]
GROUP BY hdr.cardcode, dtl.itemcode, itm.itemname
FOR BROWSE