Partkeepr data and csv

Some of Partkeepr is not obvious To create a Part requires a Part Category and a Storage Location to have been created first. for CSV import it needs to know which kind of quantity is being added this will be pcs in most cases. If the Quantity type is null you will not find any of the parts uploaded by CSV.

I intend to put together a fully working CSV import configuration. Currently I am only part way through doing so. Adding parts is not so hard as they can be entered as a CSV in the main part window. Unfortunately that does not have access to stockLevel, The column StockLevel in the Part table might better be called stockTotal.

For adding CSV files with part quantities the stock history pane is more useful. Each record in the stock history table adds a number of a particular part say 50 widgets are entered one time and later 30 widgets removed the quantity in these 2 rows would be 50 and -30 the stockLevel in the part table is the sum of these 2 entries 50 + -30 = 20 widgets available.

unfortunately the stockLevel field in Part needs to be triggered in order to update the table value e.g add 0+ to widget in the main part window and the stockLevel value goes to 0 however a refresh will then cause the stockLevel to be calculated for that part and this field becomes the total of widgets added + Widgets removed. There is a correction column usually set to 0 false but can be true. I think this likely will allow you to enter a stock level such as 50 and it will be treated as the actual mumber of a particular part rather than adding it to the existing parts recorded.

I will rewrite this later but with partkeepr and something like mysql workbench its possible to explore the table structure.

To create a CSV import you need to create a preset and upload a csv file. As you select fields and entities the preview panel will show you the result of executing your import so far against your database.

If you switch between CSV view and Preview the preview will update and you can see which fields are going to be updated or not when you run the query.

It might be me but sometimes it seems fields and entities can appear and disappear. THe Stock history csv import ideally will have a User Name associated with the parts inputted.

PartKeepr uses around 50 Tables in total some are standalone most are linked. == Part == +--------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | used everywhere always unique | category_id | int(11) | YES | MUL | NULL | | Essential Partkeepr will not let you enter a part without an assigned category (root is 1) | footprint_id | int(11) | YES | MUL | NULL | | not enforced or essential | name | varchar(255) | NO | | NULL | | The Partname / Partnumber Essential | description | varchar(255) | YES | | NULL | | Optional | comment | longtext | NO | | NULL | | optional but used in search | stockLevel | int(11) | NO | | NU LL | | Calculated Field combination of stock history records for a given part. | minStockLevel | int(11) | NO | | NULL | | not essential but pretty useful to filter on | averagePrice | decimal(13,4) | NO | | NULL | | Again built from stock history records. | status | varchar(255) | YES | | NULL | | i've gone for r-equested / o-rdered / d-elivered Date to keep track of parts | needsReview | tinyint(1) | NO | | NULL | | true/false handy flag when you need to review a part. | partCondition | varchar(255) | YES | | NULL | | I repurposed this to hold supplier prefered quantities eg 100,50 x | productionRemarks | varchar(255) | YES | | NULL | | optional | createDate | datetime | YES | | NULL | | set at system time when part was created automatic | internalPartNumber | varchar(255) | YES | | NULL | | optional but maybe easier to pick than a metapart | removals | tinyint(1) | NO | | NULL | | not sure on this true/false flag meaning ??? | lowStock | tinyint(1) | NO | | NULL | | True/false (don't know what its used for maybe automatically flag???) | metaPart | tinyint(1) | NO | | 0 | | True/False real part or meta part | partUnit_id | int(11) | YES | MUL | NULL | | Essential without a partunit_id parts do not show up in searches 1 is the pieces id | storageLocation_id | int(11) | YES | MUL | NULL | | Storage location is essential even if not physical +--------------------+---------------+------+-----+---------+----------------+ 20 rows in set (0.01 sec) The essentials for this table are id category_id name/partno storageLocation and partUnit_id (partUnit_id can take you by surprise but you will not fund a part that has not got this set!) CSV import to this table is possible from the parts view but you might not wish to do so this is because it doesnt allow you to set a part quantity. on the plus side with the octopart access you can find distributors for parts before you buy them. If you want to bulk import stock the stock history import is better suited even if it does look read only in the interface, == StockEntry == +------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | part_id | int(11) | YES | MUL | NULL | | Foreign key (id from part) | user_id | int(11) | YES | MUL | NULL | | Foreign Key Id from users for CSV use might want to set a csv user called csv | stockLevel | int(11) | NO | | NULL | | positive or minus values (the total of these for a part = current stock level | price | decimal(13,4) | YES | | NULL | | optional but you may prefer to keep this for records | dateTime | datetime | NO | | NULL | | should be auto-entered as the current system time the record was created | correction | tinyint(1) | NO | | NULL | | True/False mostly false I think this is intended as relative or absolute value e.g 500 absolute or 500 added to existing stock | comment | varchar(255) | YES | | NULL | | Free use it maybe a good place to store an order number if you can't get to distributor ordernumber. +------------+---------------+------+-----+---------+----------------+ This unassuming table is very important for CSV entry it lets you get values from most tables for default entities and lets you add stock values, i don't know if it will allow the creation of new parts or not. The CSV import doesn't appear to trigger an update for Part.stockLevel (stockLevel might be better called stockTotal in Part, stockLevel in StockEntry is more stockTransfer) in the part table say a value of 100 is showing when you add or subtract a value it seems it sums the Stocktransfer values but still may not give the proper level until the grid is refreshed. CSV import into Stock History. You need a saved configuration for CDV to work. Technically your CSV file ony needs name and quantity included the rest can be set as default values you will probably want at least two import types absolute or relative. When you have uploaded a CSV file you can configure the fields and get a result in preview. e.g if you match name with existing stock using match entity Part.name then if the name is unique then the corresponding stock value can be updated. if two parts share enough of the same name to match then you have a problem PartKeepr doesn't know which record to update. you can pick another field e.g category or storage location. PartKeepr can be very flexible a part has an id and that is the only unique quality really. e.g you can have 2 parts called widgit and it's the id which makes them unique. it's kind of too easy to have parts which have identical names but maybe different category or storage location They are not really the same part. you might make a meta part to allow you to choose a widgit from storage location 1 or storage location 2 it kind of enables the problem to be continued. One major problem is the inability to delete a part if its used in a project or even a project report. Even if you didnt save a project report it still kind of exists with references to part ids in the database. Describe Report; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | createDateTime | datetime | NO | | NULL | | +----------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) Describe ReportPart; +----------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | report_id | int(11) | YES | MUL | NULL | | | part_id | int(11) | YES | MUL | NULL | | | distributor_id | int(11) | YES | MUL | NULL | | | quantity | int(11) | NO | | NULL | | +----------------+---------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) If you create a report without saving a reportname then you probably will have part_ids in ReportPart which will make it impossible to Delete within the partkeepr application It may be possible to delete a report with a name and have the rows in reportpart also be removed. which then may make it possible to delete a part_id. Alternatively if you have a new part you could reuse the Part_Id although it may play havoc with pricing and distributors and manufacturers. Maybe best to move it to a category / location such as obsolete.