How to configure a Low Inventory report

Owned by Timothy Hitchcock
Last updated: Nov 15, 2021 by Mike Nelson (Unlicensed)
2 min read

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

https://www.youtube.com/watch?v=OJPiMaEPnas

 

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