Creating an inventory program...

[webmonk]

Hi folks,

I'm new to turning (less than a year with a few hundred pens turned and a handful of bowls, etc) but what I'm running into already is a problem keeping track of the kits, blanks, etc that I have here in the shop. I'm a web developer/programmer by trade, so my solution to most problems I come across is to let the computer deal with it and I don't see why this should be any different.

As I'm sitting here in the planning stage I started thinking that others are probably having the same problem I am, but since you guys have WAY more experience than I, you'd know what kind of tools would actually make things better versus me just guessing at it and changing the system later. So if you're interested, here's the chance! Here are some of my initial thoughts just to get the idea rolling. Please add your own and/or ask questions!

Track number kits, blanks, bushings, drill bits.
Include attributes such as finish type, blank measurements, bushing diameters.

These two things alone would allow for some interesting tools. For instance, I'm a big fan of platinum finishes with very dark woods. I could simply ask the system what's avaiable and get a list of all possible combinations that fit that criteria. Or if you've got turner's block why not let the system randomly generate selections of kits and materials that it's sure you have the materials for and (if you want to go through with measuring blanks) that the blanks are actually the right size for the kits.

The system could also generate warnings for when you're getting low on certain supplies/kits/etc. And if it's doing that, why not have it check the various online shops and find the best deal on the combination of stuff you need to order?

Anyway, that's enough to get started with I think. All ideas/suggestions welcomed!

 

[comben001]

Do it and they will come...

 

[melogic]

Webmonk, first of all welcome to the IAP.
I have been trying out different ideas and playing around with excel and other programs for a while trying to come up with an inventory program. I was looking at something that I could put all of my wood inventory in with all of my different pen kit inventories in and then when I want to create a pen, take a pen kit and a piece of wood and have the program delete 1 pen kit from inventory and 1 wood blank from inventory and create an inventory number for the new completed pen. Each wood and pen kit specified by the type of wood and pen kit. For example: let's say I have 10 Europen TN pens in inventory and 100 pieces of Cocobolo wood. I select 1 Euro Tn and 1 piece of Cocobolo and it gives me a new inventory number of ETN 101 and deletes 1 Euro from inventory along with 1 piece of cocobolo.

 

[OKLAHOMAN]

If I ever got that organized and my wife found out how much inventory (dollars) I had invested (she knows how to work my computer) well I think you get the picture! That being said most of us either want a Web-Site or have one and we might have questions for you on that front. I do agree that when I get out to the shop and dig around for a certian kit or blank it would be nice to know if I still have it but the time to list these in a program would take the fun of the hunt away and turing time as well. Just IMHO. By the way welcome to the addiction, hope you know this was mostley tongue in cheek

 

[alamocdc]

I simply use Excel w/multiple worksheets for the things I need to track. For example, I have a tab for sales, one for current inventory, one for pen pricing, etc. It's still a work in progress, but it has served me quite well. I know there are inventory programs that can be purchased, but I'm cheap.[]

 

[rpasto92]

I'll add to melogic's wishlist and say why not also have the software spit out a printed information sheet for pen ETN 101 that tells about the wood, what kind of plating is used, how to care for it and what refills it takes. Also, if this is an ordered pen rather than one you will be adding to your inventory, a place to enter who this pen is for, payment status, shipping info, ect. (pretty much a work order)...by doing this you can save customer information in another table for future orders and order history in yet another table so when they contact a year from now and forget all their specifics you can pull it up and say "Sure I remember the titanium Europen I made for you last year from cocobolo". Since it's a wishlist, if it's not a pre ordered pen but one you are adding to inventory, how about printing a barcode on the info sheet so you can take this software on the road with you to craft shows and scan in your sales? Assign values to everything and now you can add a small accounting package to track your cost of goods sold, revenue, sales tax collected, ect.

So Webmonk, are you done yet? Let me know if you need a beta tester...I love finding flaws in software[]

Ryan P^2
http://www.writeturnpens.com

P.S. typing my URL made me remember another thing...can this software help keep my webpage up to date with what is currently available in my inventory. Ok...that's all for now.

 

[rpasto92]

Billy B., sounds like we have been sharing the same spreadsheet all this time and didn't know it.

Originally posted by alamocdc
<br />I simply use Excel w/multiple worksheets for the things I need to track. For example, I have a tab for sales, one for current inventory, one for pen pricing, etc. It's still a work in progress, but it has served me quite well. I know there are inventory programs that can be purchased, but I'm cheap.[]

 

[JimQ]

I run 3 Excel spreadsheets. One with pen kit inventory with a seperate page for each style of pen, one with bought pen blanks with a page for wood and a seperate page for each type of plastic or specialty like Dymondwood and one with multiple pages for pens.

Page 1 is ordered & started pens, page 2 is completed pens that are available to sell, page 3 is completed ordered pens ready to deliver, and page 4 is sold pens. I just cut and paste to transfer from one page to the next. Each pen is assigned a number on the first page. I also use barbell ring tags to label each pen with it's number and price.

JimQ

 

[huntersilver]

Welcome!

I keep all of my work in progress in Excel.

For inventory and cost of goods sold I use
a product called PeachTree, works very nice.

Sounds like you want a Materials Requirement System,
for me I am not there yet[]

 

[Jim15]

Welcome Webmonk.I think a inventory program would be great.

 

[JimGo]

Welcome! I use QuickBooks - certain versions can do a lot of whatyou want.

 

[RonSchmitt]

Welcome Webmonk,
Add me to your list of people knockin' at your door for the finished program!

 

[webmonk]

Let me say Wow! I wasn't expecting nearly this much input so fast! Thanks! Now to respond:

I had been thinking of ways to turn it into a completed inventory tracking system as well. Here we'd have pictures, descriptions, ad copy, care info, etc. All the stuff you'd need to dynamically generate a sales catalog, create an update for your website, or print out the info sheet for the customer as suggested. It's not so different from tracking materials so I can certainly see that happening soon after the initial phase.

I love the idea of keeping sales records and customer info too. I'm just now starting to sell my pens so feedback on exactly what we should track here is greatly appreciated. BTW, the barcode idea is wonderful!

Great suggestions folks! I'm going to start charting this stuff out and hopefully begin coding this week. I'll let you know as soon as I need some Alpha testers.

@OKLAHOMAN - I'm happy to help with website questions. Feel free to email them (jon@centralridge.com) or put them somewhere on the forum here and point me to em. If you're looking for an easy way to show off pictures I use Coppermine (my site: www.jonbeard.com/pens ) though I see you folks already have an online gallery here, but this would be good for someone who wants a more exclusive showing I guess. If you're looking for an easy solution for selling online, I'd suggest OSCommerce (or one of the spin-offs. www.oscommerce.com is the site, and while it's more for developers, there's an area with live examples of people using it in their own store). Both are free to install and use. The only cost would be in having someone configure them for you and then to host it.

Thanks again folks!
Jon

 

[Dario]

Here's wishing you luck!!!

I too am keeping mine organized at helps me keep track sufficiently...for now.

All I did and on an excel workshet is my pricing...again for now.

I wish I reach that point that I am producing enough pen that I will need such a program to keep track (not because of mental failure I hope).

 

[wpenm]

Let me add my welcome to IAP Jon. I would be very interested in your finished product. Please remember that some of us are, shall we say a little challenged when it comes to the use of programs like you are talking about so please make it simple to use.[] Oh, and some us are very cheap too![:0]

 

[epson]

I use Quickbooks Premier edition. It can track the kits, and can track assembled pens after they are made. You can assign a cost for the parts and a cost for the finished pen. It automatically removes the pen kit from inventory and add the assembled pen to inventory.

 

[webmonk]

@wpenm - Cheap is okay! I've been known to barter from time to time. My bandsaw is a wimp so a pre-cut bowl blank would go a long way with me.

Jon

 

[swiftden]

Hello and welcome. sounds like a great idea. i am only just starting in pen turning and buy stuff in so would be great to get something like that before i amass too many supplies to be bothered entering them all.

 

[woodmarc]

Greeting and welcome fellow Geek.(wood and digital) []
Quickbooks Premiere is a good piece of software for accounting and inventory, But it does nothing for the web site.

Now, here is some food for thought WebMonk; Quickbooks has programming API's that will allow you to access certain stuff for integration. there is a site called Commercestarterkit.org that has a fully finctional web commercer site, based on asp.net 2.0 and contains all of the programming and API's for linking to paypal and is fully endoresed by them. Creating a link between the two would save a ton of time in the inventory management, and the links to payment acceptance.
I just downloaded the demo to quickbooks to see what kind of API's I could use. Care to start a collaboration. I appears that we may have a few guinnie pigs who may help with debugging and testing.

BTW, I am a former systems integrator who went to network administration. Thought the pressure was less. [}] The grass is always greener......

 

[webmonk]

Heya Marc! There are geeks hiding everywhere!

I'd certainly be interested in exploring the QB avenue since a few people here have mentioned it, but my primary goal is to make this program free to use and as widely available as possible so I think I'm headed down the road of a LAMP-based system with everything accessible online. I really like the idea of a module that would sync your data between this system and QB so if you're interested in working on that segment let's talk!

Network admin is tough! I'm lucky enough to be an e-commerce programmer for an ISP so for the most part I get to schedule the problems for everyone else.

Anyway, thanks for the interest and input!
(and the geek shall inherit the earth...)

 

[kf4knf]

Program it! I will beta test for ya! HEHE I am in IT with a bit of programing under my belt. [][]

 

[webmonk]

Hehe! Well it'll be in Perl and MySQL, so if you have any experience with either of those you might get drafted.

 

[melogic]

Thanks for taking this on webmonk. I would dearly love to be on your list of testers and I have a couple of bowl blanks and pen blanks I can and will trade. I love the barter system as well.[][][]

 

[alamocdc]

Add me to the beta testing list, Jon. Best of luck to you on this.

 

[webmonk]

Okay gents. I'm looking for a handful of ALPHA testers. For those who don't speak geek ALPHA testing is when you're testing a system that's in the extremely early stages of being built. Many of the features don't yet work and there are bugs galore (that could cause data loss), but the nice part is you get the opportunity to really have a say in how things work so when it gets to a stable form it's like it was designed for you. BETA testing is different because you're pounding on a product that we're pretty sure is good to go in the hopes of finding a few of the straggler bugs before things go to the general public.

I'm thinking about 3 or 4 people using it at this point would be about right. Preferred users should be able to use a browser with ease and be full of good ideas about how the system should work. Most of all, the attitude needed is one of building a system, not using one for production.

If you're interested, email me at jon@centralridge.com with your name and just a little background on your experience with anything pertinent to the project. If you're not part of the first wave of testers, please don't be discouraged. I hope to have everyone a place in here very soon.

Thanks!
Jon

 

[thewishman]

Wow, when a few skilled people get together, things start to happen quickly! I'll volunteer to be a gamma or delta tester.

 

[Draken]

Make sure your program can handle photos. It would be nice to be able to keep inventory of completed pens as well, and once they sell, they can be moved to the "portfolio" to help new customers get a feel for your craftsmanship as well as other materials and platings.

Cheers,
Draken

 

[webmonk]

Definitely! Great suggestion!

Jon

 

[Dario]

email sent

 

[Dario]

How about adding a column for manufacturer/supplier (i.e. Cigar, Euro, slimline, etc. can come from different manufacturers)

 

[webmonk]

Can do. These would be people like PSI? Who else should be on the main list?

Jon

 

[Dario]

CSUSA, Berea, and PSI are are the only ones I can think of

 

[Dario]

Finish can be more.

TN should be broken into 2: Titanium Gold and Black Titanium

Chrome also have regular and black

How about the solid silver?

Rhodium

Satin finish (there are also several colors on these)

etc.

 

[Dario]

How about adding size column on the blanks? knowing it will help a lot what kit you can use.

3 tier group should be sufficient like slimline (less than 3/4"), regular (3/4" - 13/16"), Jumbo (7/8" and bigger)

 

[webmonk]

I'm adding a tool that will let you create your own material to add to the list. Once added, it's available in everyone else's pulldown. Kind of making it a group effort.

Jon

 

[Dario]

Originally posted by webmonk
<br />I'm adding a tool that will let you create your own material to add to the list. Once added, it's available in everyone else's pulldown. Kind of making it a group effort.

Jon

GREAT!!!

BTW, got this

Software error:
Unable to insert new pen with: INSERT INTO pens
SET
kitname='Jr Gent II',
finish='Titanium',
tip=NULL,
material='wood',
materialtype='Amboyna',
userid='4'
at inv.pl line 433.
For help, please send mail to the webmaster (webmaster@centralridge.net), giving this error message and the time and date of the error.

 

[webmonk]

So as not to scare off anyone who just wants to talk generally about this idea I'd like to move the TECHNICAL discussions to a new thread I've started: http://www.penturners.org/forum/topic.asp?TOPIC_ID=16224

The new thread is where actual implementaion and debugging talk should go. Here, we'll stick to ideas, questions, etc.

Thanks!
Jon

 

[JimGo]

Originally posted by webmonk
<br />Who else should be on the main list?

Craft Supplies UK
Home-built

 

[baldysm]

Please don't take this the wrong way. I'm not opposed to this program in the least.

My question is, how much are the features everyone is talking about really going to be used? Remember, in order for the information coming out of the program to be of value, someone has to consistently enter all the information into it.

My record keeping is not perfect, but it does fit my needs fairly well. I have a little piece of paper with every kit. I write down the cost ot the kit, the blank, and the finish of the kit. I have a spreadsheet that I have all the pens I have made - kit type, where I got the kit, the blank, where I got it, the cost of the pen and my sale price.

I have little piles of kits on shelves, spread out all over hell, little piles of blanks (spread out all over the place, in drawers, in cabinets, on top of boxes, etc). Under each pile of blanks/kits, there is a piece of paper with what it is, what it costs, and in some cases where I got it.

You can make the program very complex to do anything you want, but it becomes daunting to put all that information into it. For something as simple as the cost of the pen kit, for example. I get my kits from different sources at times, on sale at times, etc. How to track cost of the pen kits when the prices vary? FIFO? LIFO? Average cost? Weighted average cost?

I spent the time awhile ago to take inventory of all my kits. Printed out all the results and have it on the counter in my shop, totally out of date and useless. Aside from the fact I have too many kits, it's a hassle to try to keep everything up to date, more so than the relatively minor hassle of occasionally ordering something I really dont need or running out of something. If I forget to update the inventory a couple times, then it becomes a liability when I can't rely on it.

So good idea, been talked about in the past I seem to recall. I would vote for something more simple than something that can handle everything in every way you want, making it a complex beast with many features seldom used. (Like Microsoft's Office products).

 

[webmonk]

Hi Scott,

Those are all valid points. The main reason I'm doing it is because there's atleast one person who is going to use it - me. But more to your point, it's easier to make one big monster than it is to make 10 little ones because it's a building block approach. If you've got a system that tracks your kits, it's easy to add functionality to make it track blanks too. If you're tracking those it's easy enough to do something else with that data and so on and so on.

That said, the other thing I want to do is make it so that you can more or less just use the parts you find useful. If you only want to use the pen tracking/order history functionality and could care less about your inventory you need only enter the pens you've made and you're there. If you need help organizing the supplies but already have a solution for tracking the finished product, you can do that too. Or, if you're like me and need both, then you're still good to go. I'm not a fan of busywork.

And no, I'm not taking it the wrong way. I appreciate any and all feedback. Believe me, there have been plenty of times in my life where someone should have said, "Snap out of it boy! You're on a fool's errand!" Though, I probably would have gone ahead and done it anyway...

Jon