System Five Add-Ons & Integrations help simplify the way you do things.
Our catalog of services has topic-based, role-based, or technical plans that can help you maximize your software investment.
Copyright © 1984-present
Windward Software Systems Inc.
All Rights Reserved.
Privacy Policy
This is an old revision of the document!
The overall goal is to import inventory from RPSC to System Five and then add matrix parents to System Five linking them to the matrix child parts.
The goal is to
update inventry set KitType = 0
update Inventry set Part = Item where InvUnique < 10
Just make sure you replace 10 with the unique number of the first Parent Matrix Part Number you loaded. Remember there are lots of parts that were not matrix parts that still have only a part number as the primary search method.
We use a couple queries to produce a count of items in stock, and compare this with the items that are not in stock (qty = 0). This is a good indicator to bring your attention to missed steps or improper information being added to system five. As a rule of thumb if the percentage items stocked is less than or equal to 50% of the total number of inventory being imported the data has not been cleaned. It may contain items that should be in the auxilliary database, or items that are no longer used and need to be removed. Here is a way to quickly check this figure:
=B3/SUM(B2:B4)*100&"%"
The qryInfo-Percentage of inventory items in stock query relies on the qtyOnHand query to exist unchanged in order to work. Cleanup of these extra parts can be done within system five using the Remove non moving stock Toolbox routine. This way you can remove anything that has not moved, and anything that has history would be marked for deletion. The reason this is a good and flexible way to do things is you can do a report on all items marked for deletion and if necessary turn that flag off with a data import (If showing a customer how to do this), or via a query.
Update "Inventry" set markDeleted = 0 where markDeleted = 1
I used the where markDeleted = 1 part of the query to illustrate the filter that could be used if you wanted to run a SELECT query to see all items marked for deletion first. The above SQL statement doesn't need the where clause in order to work. Here is and example of a Select query to see all items currently marked for deletion:
Select * from "Inventry" where markDeleted = 1
The Virtual Warehouse (Formerly known as the Auxilliary database) is used as a price list made up of price lists from each of your suppliers. It is important to understand what the purpose of the Virtual Warehouse is so you can make sure parts that should be in the Virtual Warehouse are not placed into regular inventory. If a part is not normally stocked, and it is not a special order item and is not seasonal item then it should not be an inventory item. To rephrase: If a part is stocked, or is a special order item or is a seasonal item it should be an inventory item; if one of those is not true then it should be a Virtual Warehouse item/part.
Information: Please note these are access queries and the syntax used will not likely work in Pervasive unless modified. Also there are some additional queries ran in pervasive, but they are noted in the section they are used in.
SELECT CStr([Part_Number]) AS Part_Number_NotPadded, IMInventory.SKU, IMInventory.Description, IMInventory.MATRIX_HEADER_ID, IMInventory.MATRIX_LAYER1_ID, IMInventory.MATRIX_LAYER2_ID, IMInventory.MATRIX_LAYER3_ID FROM IMInventory;
SELECT Inventry.Cat, Inventry.InvUnique AS WWs5InvUnique, [qry RPSC Inventory].* FROM [qry RPSC Inventory] INNER JOIN Inventry ON [qry RPSC Inventory].Part_Number_NotPadded = Inventry.Part;
SELECT IMInventory.Part_Number, IMInventory.SKU, IMInventory.Description, IMInventory.Category_Code, pad([part_number],6,False) AS NewSku FROM IMInventory;
SELECT [qry RPSC Inventory with Category].SKU, [qry RPSC Inventory with Category].Cat, IMMatrixHeader.Matrix_Desc FROM IMMatrixHeader INNER JOIN [qry RPSC Inventory with Category] ON IMMatrixHeader.Matrix_ID = [qry RPSC Inventory with Category].MATRIX_HEADER_ID GROUP BY [qry RPSC Inventory with Category].SKU, [qry RPSC Inventory with Category].Cat, IMMatrixHeader.Matrix_Desc;
SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku],13) & "-" & pad([Part_Number_NotPadded],6,False) AS NewFormatSKU, IMMatrixLayer1.Item_Desc AS Size1, IMMatrixLayer2.Item_Desc AS Size2, [qry RPSC Inventory with Category].Cat FROM ([qry RPSC Inventory with Category] INNER JOIN IMMatrixLayer1 ON [qry RPSC Inventory with Category].MATRIX_LAYER1_ID = IMMatrixLayer1.Layer1_ID) INNER JOIN IMMatrixLayer2 ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID;
SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, [qry RPSC Inventory with Category].SKU, "0" AS KitQty FROM ([qry RPSC Inventory with Category] INNER JOIN IMMatrixLayer1 ON [qry RPSC Inventory with Category].MATRIX_LAYER1_ID = IMMatrixLayer1.Layer1_ID) INNER JOIN IMMatrixLayer2 ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID;
SELECT CStr([Part_Number_NotPadded]) AS RPSCPART, Left([sku],13) & "-" & pad([Part_Number_NotPadded],6,False) AS NewFormatSKU, IMMatrixLayer1.Item_Desc AS Size1, IMMatrixLayer2.Item_Desc AS Size2, [qry RPSC Inventory with Category].Cat FROM ([qry RPSC Inventory with Category] INNER JOIN IMMatrixLayer1 ON [qry RPSC Inventory with Category].MATRIX_LAYER1_ID = IMMatrixLayer1.Layer1_ID) INNER JOIN IMMatrixLayer2 ON [qry RPSC Inventory with Category].MATRIX_LAYER2_ID = IMMatrixLayer2.Layer2_ID WHERE ((([qry RPSC Inventory with Category].Cat) Is Null));
SELECT IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat FROM IMMatrixHeader LEFT JOIN [qry RPSC Inventory with Category] ON IMMatrixHeader.Matrix_ID = [qry RPSC Inventory with Category].MATRIX_HEADER_ID GROUP BY IMMatrixHeader.Matrix_ID, IMMatrixHeader.Matrix_Desc, [qry RPSC Inventory with Category].Cat HAVING ((([qry RPSC Inventory with Category].Cat) Is Null));
SELECT [qry RPSC Inventory].* FROM [qry RPSC Inventory] LEFT JOIN Inventry ON [qry RPSC Inventory].Part_Number_NotPadded = Inventry.Part WHERE (((Inventry.Part) Is Null));
SELECT qtyOnHand.Stocked, Count(qtyOnHand.Stocked) AS CountOfStocked FROM IMInventory INNER JOIN qtyOnHand ON IMInventory.Part_Number = qtyOnHand.Part_Number GROUP BY qtyOnHand.Stocked;
SELECT IMInventory.Part_Number, [qty_on_hand]<>0 AS Stocked FROM IMInventory;