At the moment I use calc as an inventory keeper for my online record store. Every distributor invoice I get is converted to a calc sheet, and I work out the sales price and then add a column for items sold. When I sell an item I cmd+h the catalogue number/title and add 1 to the items sold column. Once the items sold column matches the items received column I stop selling the item. I now have 300 sheets in my calc file and probably 15,000 items in my garage and it's getting a little hard to manage.
Problems:
Each sheet of my calc file has a different layout. Some pages are itemID/artist/title/quantity/cost/price some are quantity/artist & title/item ID/cost/ price etc.
-Do I need to reformat the calc file so everything is in the same order with the same fields on every sheet?
Should the inventory database table I build containing this information be 300 separate tables or 1 table with the data from 300 sheets inserted into it?
-the only way I found to import this info is via the copy and paste feature. I would prefer to import the entire file rather than copy and paste 300 times if possible.
The most common error from doing this manually is that I had 10 copies in stock and have sold 9 but failed to list the 10th for sale. Or I had 10 copies in stock have sold 10 but still have the item listed for sale.
-My sales data and current for sale items data is available as CSVs from the 3rd party websites I commonly sell on. So I need to cross reference these sales/items for sale with my inventory spreadsheet. Is Base suitable for this?
Some facebook friends have warned me base may crack under large loads of items.
I have approx 300 sheets in the calc file with approx 100 lines each, so 30,000 lines of items (many are duplicates when an item is reordered). My main sales/inventory CSV contains approx 25,000 "sales" and 5000 "listed for sale" items. This is approx 2 years worth of business.
Solutions I'm looking for:
primarily stock keeping. as mentioned above making sure I list items for sale that I have in inventory. Dont list items for sale that I don't.
Ideally base will spit out a CSV of these errors using item IDs that will allow me to upload to the 3rd party website using their "delete all" or "add all" feature.
stop adding sold items manually but upload a CSV for the days sales with that info in it. or a total sales sheet if that makes more sense.
accounting queries such as speed of turnover on stock from different distributors or artists so that I make more informed purchasing decisions.
Thanks for any help!
