How to configure a Low Inventory report

An Alert in Crows Nest can be configured to send out an email containing all of the Part list items that are at or below the minimum level.

The body of the email would look like this:

Here is a video that will show you how to configure this low inventory report in your Crows Nest

How to Setup a Custom SQL Alert in Crows Nest

And here is the query that I refer to in the video that you will need to copy and paste in when prompted.

SELECT Part_List.Description AS "Part List"
  , Part.Description + '; '
  + COALESCE (ColorMfg1.ScreenDescription + '; ' + Color1.ColorDesc, Color1.ColorDesc, '')
  + COALESCE (ColorMfg2.ScreenDescription + '; ' + Color2.ColorDesc, Color2.ColorDesc, '') AS Description
  , '<p style="text-align: right;">' + CONVERT(nvarchar(20), Part_List_Item.MinQty) + '</p>' as MIN
  , '<p style="text-align: right;">' + CONVERT(nvarchar(20), ISNULL(Inventory.Available, 0)) + '</p>' as AVL
  , '<p style="color:red; text-align: right;">' + CONVERT(nvarchar(20), ISNULL(Inventory.Available, 0) - Part_List_Item.MinQty) + '</p>' as Delta
FROM Part_List_Item
INNER JOIN Part_List ON Part_List_Item.List = Part_List.ID
INNER JOIN Part ON Part_List_Item.Part = Part.ID
LEFT OUTER JOIN dbo.Part_Category as Category ON Part.Category = Category.ID
LEFT OUTER JOIN dbo.Part_SubCategory as SubCategory ON Part.SubCategory = SubCategory.ID
LEFT OUTER JOIN Business ON Part_List_Item.Vendor = Business.ID
LEFT OUTER JOIN Color AS Color1 ON Part_List_Item.Color1 = Color1.ID LEFT OUTER JOIN Business AS ColorMfg1 ON Color1.Mfg = ColorMfg1.ID
LEFT OUTER JOIN Color AS Color2 ON Part_List_Item.Color2 = Color2.ID LEFT OUTER JOIN Business AS ColorMfg2 ON Color2.Mfg = ColorMfg2.ID
JOIN
(
SELECT Inventory.ID, PCC, ISNULL(Modifier, '') as Modifier, SUM(CASE WHEN ISNULL(Project, 0) = 0 AND ISNULL(Phase, 0) = 0 THEN Qty ELSE 0 END) as Available, SUM(Qty) as OnHand
FROM Inventory INNER JOIN Inventory_Transaction ON Inventory.ID = Inventory_Transaction.Inventory
GROUP BY Inventory.ID, PCC, ISNULL(Modifier, '')
) AS Inventory ON
           Part_List_Item.PCC = Inventory.PCC
AND ISNULL(Part_List_Item.Modifier, '') = Inventory.Modifier

WHERE MinQty > 0 AND Inventory = 1 AND ISNULL(Inventory.Available, 0) <= MinQty

ORDER BY Part.Description, Color1.ColorDesc, Color2.ColorDesc, Part_List_Item.Modifier