Inventory Movement

Hi,

You need to modifiy the existing view with following codE: CREATE VIEW ViewItemMovement AS SELECT Department.Name as DepartmentName, Category.Name as CategoryName, Supplier.SupplierName as SupplierName, Item.ItemLookupCode AS ItemLookupCode, SupplierList.ReorderNumber as ReorderNumber, Item.Description AS ItemDescription, Item.LastSold as LastSold, Item.LastReceived as LastReceived, Item.Cost as Cost, InventoryTransferLog.Type AS Type, 0 as QuantitySold, ISNULL (InventoryTransferLog.Quantity, 0) AS Quantity, 0 as PriceSold, 0 as CostSold, InventoryTransferLog.DateTransferred AS DateTransferred, 1 AS Moved, CASE InventoryTransferlog.Type WHEN 2 THEN InventoryTransferLog.ReferenceID ELSE NULL END AS TransactionNumber FROM InventoryTransferLog LEFT JOIN Item ON InventoryTransferLog.ItemID = Item.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID LEFT JOIN Category ON Item.CategoryID = Category.ID LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID LEFT JOIN SupplierList On Item.SupplierID=SupplierList.SupplierID and Item.ID=SupplierList.ItemID UNION ALL

SELECT Department.Name as DepartmentName, Category.Name as CategoryName, Supplier.SupplierName as SupplierName, Item.ItemLookupCode AS ItemLookupCode, SupplierList.ReorderNumber as ReorderNumber, Item.Description AS ItemDescription, Item.LastSold AS LastSold, Item.LastReceived as LastReceived, Item.Cost AS Cost, 99 AS Type, ISNULL (TransactionEntry.Quantity, 0) AS QuantitySold, 0 as QuantityTransferred, ISNULL (TransactionEntry.Price, 0) as PriceSold, ISNULL (TransactionEntry.Cost, 0) as CostSold, [Transaction].Time AS DateTransferred, CASE WHEN [Transaction].Time IS NULL THEN 0 ELSE 1 END AS Moved, [Transaction].TransactionNumber AS TransactionNumber FROM Item LEFT JOIN TransactionEntry ON TransactionEntry.ItemID = Item.ID LEFT JOIN Department ON Item.DepartmentID = Department.ID LEFT JOIN Category ON Item.CategoryID = Category.ID LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber LEFT JOIN Supplier ON Item.SupplierID = Supplier.ID LEFT JOIN SupplierList On Item.SupplierID=SupplierList.SupplierID and Item.ID=SupplierList.ItemID

Then you go down and modify the group by clause GroupBy = "ViewItemMovement.ItemLookupCode, ViewItemMovement.ReorderNumber, ViewItemMovement.LastSold, ViewItemMovement.LastReceived,

ViewItemMovement.Cost,ViewItemMovement.SupplierName, ViewItemMovement.DepartmentName, ViewItemMovement.CategoryName, ViewItemMovement.ItemDescription"

Then you go again down to add this field to print on the report:

Begin Column FieldName = "ViewItemMovement.ReorderNumber" DrillDownFieldName = "SupplierList.Reordernumber" DrillDownReportName = "" Title = "ReOrder" VBDataType = vbString Formula = "" ColHidden = false ColNotDisplayable = False FilterDisabled = False ColWidth = 2160 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Please Rate it.

"Ihcada" wrote:

Thank you. This is exactly what I needed.