Searching \ for '[EE]: Organising stocks of components' in subject line. ()
Make payments with PayPal - it's fast, free and secure! Help us get a faster server
FAQ page: www.piclist.com/techref/index.htm?key=organising+stocks
Search entire site for: 'Organising stocks of components'.

Exact match. Not showing close matches.
PICList Thread
'[EE]: Organising stocks of components'
2007\10\07@135909 by Philip Pemberton

face
flavicon
face
[mental note: when sending messages to the PICLIST, send them from the email
address that's actually /subscribed/ to the list...]

Hi,
  OK then, this is probably a bit of a weird subject for a [EE] post, but
hear me out...

  I, probably like most people on this list, have a fairly large stock of
electronic components. Most of these are stuffed into Raaco storage boxes of
various sizes, in a very haphazard fashion. Sure, all the resistors are in one
box, capacitors in another, but it's a total toss-up which box, say, the
LM324s are in. How many of a given part I actually have at a specified time is
a question best left to the omnipotent.

  So I reckon there has to be a better way to do this. Spreadsheets just
don't seem to be the right tool for me, and I've heard of (expensive) software
that can handle this. But I'm wondering - for your normal every-day lab stock,
what do you guys use?

  I'm about half-way through developing a little PHP-based database to answer
the questions "What do I have?", "Where did I put component XYZ123?", "How
many XYZ123s do I have?", and "Where did I buy the XYZ123s from?", but I'm
curious as to how everyone else is managing this (I assume) common problem.

  At the moment I've got something that I can add manufacturer, supplier and
part data to, edit said data, delete it, and add stock, and I'm working on
adding 'data grabbing' support (e.g. "I just bought 100x 123-1234s from
Farnell and put them in box QAZPLM/A/1, add that to the database"; all the
data is grabbed from the distributor websites in realtime) and BOM support
("project XYZ needs these parts, allocate them and tell me what I need to
order, if anything, to complete the project"). Datasheet storage is also on
the drawing board, as is some form of "how many XYZs can I build from what I
have in stock, and what is the limiting factor" script for the BOM side.

  Am I over-engineering this thing to the hilt? Anything else I should think
about?

  I can throw the code I have now into a tarball if anyone *really* wants to
take a look, but at the moment it isn't pretty, and it's based on the code
that runs the admin side of my website (read: relies on HTTP authentication
and is as ugly as sin). Everything's plugin based though, so if you want to
add something you just create a new plugin for it. Kinda nifty, but also kinda
OTT...

  Answers on a postcard to the usual address...

Thanks.
--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
spam_OUTpiclistTakeThisOuTspamphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\07@143114 by dpharris

picon face
Perfect timing.  I had just asked a friend what he is using because I have
decided it is time to get myself organized.  I think your description of need is
just right.  The web data grabbing is icing, but very tasty!

David



Quoting Philip Pemberton <.....piclistKILLspamspam@spam@philpem.me.uk>:

{Quote hidden}

> --

2007\10\07@143855 by Timothy J. Weber

face picon face
Philip Pemberton wrote:
>    So I reckon there has to be a better way to do this. Spreadsheets just
> don't seem to be the right tool for me, and I've heard of (expensive) software
> that can handle this. But I'm wondering - for your normal every-day lab stock,
> what do you guys use?

I've used spreadsheets, and have trouble working around their
non-relational nature.  And I've thought about a web app like you're
describing.  But I also got stalled thinking "There's gotta be some
better way to do this that others are using."

If there's not, maybe it would make a good shared project.

Additional things I had considered:

- Storing the price when components are purchased.  It would be nice to
be able to say things like "How much did this project cost in parts?"
and "How much would it cost to make a new one?" (maybe even with the
automatic update from the supplier's web site).

- A relational table for projects, that reference parts, perhaps with
alternates.

- Keeping track of equivalents.  A deep issue, perhaps.  But you might
want to say "What do I have that's close to part X?" or "What's
currently the cheapest source of part Y among my favorite suppliers?"
Probably harder to implement than it would be worth, but...
--
Timothy J. Weber
http://timothyweber.org

2007\10\07@144021 by wouter van ooijen

face picon face
> So I reckon there has to be a better way to do this.

I might not be typical, because I run my webshop. This is what I do:
components that don't take up too much space are stocked in one place.
Each place has a code, which is printed on the invoice (I print 3 copies
of each invoice, one for me, one for the customer, and one for picking
the items and use as address label).

I have a Python file that contains a long list of entries like this:

Component( Code = 'EE-24LC512-DIP',
  Location  = 'LA09-03',
  Inventory = [
     Inventory( Date = '31-12-2006', N =   0 ),
     Inventory( Date = '31-12-2005', N =   6 ),
     Inventory( Date = '31-12-2004', N =  40 ),
  ],
  Sources = [
     Source( Supplier = Microchip, Code = '24LC512-I/P', EX = 2.43,
Class = Class_New, N = 30, Date = '19-02-2006' ),
     Source( Supplier = Memec, Code = '24LC512IP', EX = 1.99, Class =
Class_New, N = 60, Date = '29-09-2004' ),
  ])

This allows me to locate a component, check where I bought them, when,
and for what price. Every newyear I go through the routine of counting
everything, so I can calculate what my inventory is worth.

Wouter van Ooijen

-- -------------------------------------------
Van Ooijen Technische Informatica: http://www.voti.nl
consultancy, development, PICmicro products
docent Hogeschool van Utrecht: http://www.voti.nl/hvu



2007\10\07@144633 by Ariel Rocholl

flavicon
face
I use ListPro for this and other matters. Best thing is you can use in your
PC and in your PocketPC/smartphone and keep them in sync automatically. I
would say it is cheap for all what it offers, you can easily
increase/decrease stock, add/remove items, categories, etc. You can also
update the schema (i.e. add/remove fields and set default values for them)
without impacting data already in place.

It can import/export several formats as well.


2007/10/7, Philip Pemberton <.....piclistKILLspamspam.....philpem.me.uk>:
{Quote hidden}

> -

2007\10\07@145036 by Dave Tweed

face
flavicon
face
Philip Pemberton <piclistspamspam_OUTphilpem.me.uk> wrote:
> I'm about half-way through developing a little PHP-based database to answer
> the questions "What do I have?", "Where did I put component XYZ123?", "How
> many XYZ123s do I have?", and "Where did I buy the XYZ123s from?", but I'm
> curious as to how everyone else is managing this (I assume) common problem.

You're not the only one thinkkng this way; see Keith Brown's article in the
August Circuit Cellar:

  http://www.dtweed.com/circuitcellar/caj00205.htm#3501

-- Dave Tweed

2007\10\07@145317 by Dr Skip

picon face
I'm very interested in the answer as well. Most detailed database solutions I
estimated would take any time I had to make anything or work and turn it into
data entry time - both tracking usage and acquisition. So, no database here yet...

The solution I use may not be of interest, but for caps, resistors and the
like, I use qt size ziplock bags with the rating written on the bag and placed
in a filing sort of order in a box(es). Boxes (some larger sized too) are
labeled with component type, numbered, and I do keep a broad spreadsheet of
expensive or high qty items linked to box number. Semis tend to be pushed into
anti-static foam in similar groups and bagged. Others are compartment boxed.
It's a compromise and it minimally works, but I can't afford to sit and
inventory every little part into a pc. I also think the overhead of having to
check out each component used in quick test or prototype would severely limit
spontaneity (if you don't, it's either re-inventory time every so often, or
bye-bye useful db). If it were my job to just manage a lab, that would be
different. The effort has to recover more loss or generate more income than the
time it takes would normally produce.

Now, if you had an idle student sitting around..... ;)


Philip Pemberton wrote:
{Quote hidden}

2007\10\07@163305 by wouter van ooijen

face picon face
> I've used spreadsheets, and have trouble working around their
> non-relational nature.  And I've thought about a web app like you're
> describing.  But I also got stalled thinking "There's gotta be some
> better way to do this that others are using."
>
> If there's not, maybe it would make a good shared project.

ROFL!

(memories of the perfect PIC board discussion bubble up in my mind)

Wouter van Ooijen

-- -------------------------------------------
Van Ooijen Technische Informatica: http://www.voti.nl
consultancy, development, PICmicro products
docent Hogeschool van Utrecht: http://www.voti.nl/hvu



2007\10\07@165609 by Timothy J. Weber

face picon face
wouter van ooijen wrote:
>> I've used spreadsheets, and have trouble working around their
>> non-relational nature.  And I've thought about a web app like you're
>> describing.  But I also got stalled thinking "There's gotta be some
>> better way to do this that others are using."
>>
>> If there's not, maybe it would make a good shared project.
>
> ROFL!
>
> (memories of the perfect PIC board discussion bubble up in my mind)

Yeah, I was thinking of that when I typed 'maybe'...

Software is sometimes more flexible than hardware, though!
--
Timothy J. Weber
http://timothyweber.org

2007\10\07@172002 by Philip Pemberton

face
flavicon
face
dpharris@telus.net wrote:
> Perfect timing.  I had just asked a friend what he is using because I have
> decided it is time to get myself organized.  I think your description of need is
> just right.  The web data grabbing is icing, but very tasty!

Oh, it's more than icing. My local test copy has a (rather shaky and unstable)
DigiKey invoice importer. As in, you feed it the invoice number and it runs
off and grabs the invoice, then tries to add the stuff on the invoice to the
database (but with UNALLOCATED in the Binlocation field). DK (obviously) don't
leave anything like name, address, customer ID number in there, just the stuff
that was ordered, SO# and invoice number. Strange that you only need the
invoice number to get to it though, but given that there isn't a way to go
from invoice # => customer, I guess it isn't too bad.

So basically, the flow is:
  - Order arrives
  - Check everything off against the list, make some noise if anything's
missing...
  - Enter the invoice number, wait for ISIS to grab the component data and
import it
  - Hit "List Parts with Unallocated Stock"
  - Change the relevant Bin Locations to match the boxes you've put the parts in
  - ...
  - Profit!

Shame Farnell don't offer web-accessible invoices though - natch. Their 'new
and improved' website is bloody awful though, and not just because it's bogged
down with gobs of Javascript...

But I like Wouter's idea of tracking purchase date and price too - I'm already
tracking supplier and location, but date/price/invoice number would be handy
too. Maybe with the option of linking to the invoice itself (if I could be
bothered to scan it in, or even just link to DK's site if it's a DK invoice).

So more database mods then, and not just to get rid of the half-baked "alias"
system I bodged in to reduce typing when using Mass Import without a data
source...

I also need to figure out a labelling scheme for my boxes. At the moment I'm
using the form:
  <three-letter prefix>/<four digit ID number>

So my first box of SMDs is SMD/0001, fifth box of resistors is RES/0005,
miscellaneous #32 would be MSC/0032. I think I'm going to need to buy some
more labelling tapes for my little P-touch 1000 label printer...

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
@spam@piclistKILLspamspamphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\07@172458 by Chris Smolinski

flavicon
face
Curiously enough, I recently put together my own little inventory/BOM
program. Nothing fancy, and I need to enter in purchases manually,
although it does have an option to let me build N assemblies, and
auto-deductions components from stock.  Works great as long as I
remember to use it ;-)

--

---
Chris Smolinski
Black Cat Systems
http://www.blackcatsystems.com

2007\10\07@180716 by Philip Pemberton

face
flavicon
face
wouter van ooijen wrote:
> (memories of the perfect PIC board discussion bubble up in my mind)

Oh yes, the Too Many Cooks problem...

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
KILLspampiclistKILLspamspamphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\07@180923 by Philip Pemberton

face
flavicon
face
Chris Smolinski wrote:
> Curiously enough, I recently put together my own little inventory/BOM
> program. Nothing fancy, and I need to enter in purchases manually,
> although it does have an option to let me build N assemblies, and
> auto-deductions components from stock.  Works great as long as I
> remember to use it ;-)

Yeah, that's always the biggest problem - using stuff without tagging it as
used...

"Damn it, where did all those 4k7 resistors go? I need twelve, but I can only
find three!"

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
RemoveMEpiclistTakeThisOuTspamphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\07@181534 by Chris Smolinski

flavicon
face
>Chris Smolinski wrote:
>>  Curiously enough, I recently put together my own little inventory/BOM
>>  program. Nothing fancy, and I need to enter in purchases manually,
>>  although it does have an option to let me build N assemblies, and
>>  auto-deductions components from stock.  Works great as long as I
>>  remember to use it ;-)
>
>Yeah, that's always the biggest problem - using stuff without tagging it as
>used...
>
>"Damn it, where did all those 4k7 resistors go? I need twelve, but I can only
>find three!"

Now I know why the stockroom girls always got mad when I'd go and help myself.

--

---
Chris Smolinski
Black Cat Systems
http://www.blackcatsystems.com

2007\10\07@185126 by Philip Pemberton

face
flavicon
face
dpharris@telus.net wrote:
> This all sounds wonderful, just the ticket for us Digikey junkies :-)  
>
> Did you say you were releasing this?  

Yep.
I'm working on getting a demo up and running for you guys to play with, but
the software on my webserver is being fussy.

> PS I just came back from teh local hardware store with some (more) storage boxes...

I've been buying mine from Farnell -- nearly all of them are Raaco A45 storage
boxes. About six quid each, plastic, and built pretty decently.

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
spamBeGonepiclistspamBeGonespamphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\07@195219 by Timothy J. Weber

face picon face
Philip Pemberton wrote:
> I'm working on getting a demo up and running for you guys to play with, but
> the software on my webserver is being fussy.

Very cool - I look forward to seeing it.

I had thought that one big advantage of having a publicly-accessible
database would be that you could share the table of part relations, like
"same as," "see also," "deprecated in favor of," etc.  Obviously making
it suit different working styles is hard, but the benefit of
accumulating some of that shared knowledge for free over time might be
worth it.
--
Timothy J. Weber
http://timothyweber.org

2007\10\07@231419 by Russell McMahon

face
flavicon
face
> Yeah, that's always the biggest problem - using stuff without
> tagging it as
> used...

> "Damn it, where did all those 4k7 resistors go? I need twelve, but I
> can only
> find three!"

Just bought a Lux meter.
End line.
Had to call 6 stores with 1 shown on the computer before I found one
with 1 on the shelf.
A store 200 miles away has 4 but they won't transfer end line stock
:-)


       Russell

2007\10\08@011316 by Forrest W Christian

flavicon
face
I have two types of components.... Engineering and Production.

Engineering components are what I use on protoboards or to build the
very early prototypes.  From an accounting perspective, these are
treated as expenses since they will (hopefully) never be used to build a
production unit.  They are also generally what I use to repair that
random device which makes it into the shop.

The engineering components are wide and varied, and not a lot of any
given type.   I store these in those multidrawer cabinents which you can
buy at Home Depot, Lowes, Walmart, or any other store with any hardware
or tool selection to speak of.   I have about 10 of these cabinents
screwed to the wall of the shop near the prototype workbench - There are
approximately 200 small drawers and 100 large ones.  Just a random
selection of the labels on these drawers include "Elec Caps < 10uf",
"Red Resistors" (aka x.xK), "slide switches", "Amps", "Misc PICs",
"NPN", "N Enh Mosfet", "Audio Output", "XTAL & Osc", "Light Emitters",
"LCD w/controllers", etc.   The idea being if I am looking for a NPN
transistor I can just dig through the appropriate drawer.  The devices
in these drawers are believed good, but may have been on a protoboard
somewhere--- although typically I have one drawer which has an
assortment of "protoboard leftovers" which get used first when I'm doing
a circuit.   Oh one final piece - I group similar types of components
together ..  I.E. Passives, Switches, Sockets, Connectors, Semis, Optos,
etc.

For the production components, everything gets accounted for in
inventory in Quickbooks Premier : Manufacturing Edition.   Every item in
any product I manufacture gets an item in Quickbooks.  The Manufacturing
Edition allows you to set up assemblies as well, with a list of items
needed to build that assembly.   When you build an assembly (or a
quantity of the assemblies), you just tell quickbooks to build X items
and it automatically deducts the appropriate quantities.   As I'm
pushing ~100K of product through every year, the cost of the Quickbooks
Premier didn't put me off since I was already using quickbooks as my
accounting package.

As far as storage of the production components, generally I'm dealing
with larger quantities of a smaller number of items (under 100 items),
so I'm dealing with tubes of components, or thousands of one variety,
etc.   Tubes I just keep in a couple of boxes although I'm about ready
to build/buy a divider of some sort to keep the tubes in so I can find
the right one easier (if anyone has any ideas for this, I'm all ears).
Resistors and other axial components get run through the trim/bend
shortly after arrival and then put in a drawer in a separate cabinet
than the proto components.  You can store a *lot* of 1/8W resistors in a
single small drawer - probably on the order of 10,000 or so.   The rule
is one drawer one component.  (although sometimes when inventory is
high, it's two drawers one component).   For the larger components,
either they stay in their box or they go into an open bin - depending on
how they come from the factory.

-forrest

2007\10\08@131753 by alan smith

picon face
As an aside.....

I have plastic cabinets that I keep the parts in...sure its a mess sometimes to find what I want but I know what drawer has what.  But, for projects I have a box for each one.  I order parts for that project build, typically ordering what I need for it but often at least 2 or 3 extras, and other times....10 or 20 extra if they are common used parts, that restocks my other bins.  But the box has the project on the side, and I keep all the parts and bags, etc in that box so if and when I need to go back to it...everything to build the project is there.  Most components are pretty cheap so I just keep a variety on hand for when I need to modify a design, or build something up.  I just don't have the room to keep a huge variety of things.  The other nice thing to have is the sample folders that resistor and cap vendors give out...I just keep them restocked when I run low.

     
---------------------------------
Pinpoint customers who are looking for what you sell.

2007\10\08@141218 by Vitaliy

flavicon
face
Dave Tweed wrote:
> You're not the only one thinkkng this way; see Keith Brown's article in
> the
> August Circuit Cellar:
>
>   http://www.dtweed.com/circuitcellar/caj00205.htm#3501

I remembered that article too, when I read the subject. :)

=====================
Build a PHP Components Database
by Keith Brown
Keith introduces you to the LAMP software suite and walks you through your
first PHP project: a program for logging and tracking your parts. Now you
can use your favorite browser to access your programs without having to
generate a GUI for new applications. p. 48
Keywords: PHP, database, LAMP, MySQL
=====================

We've looked at different solutions to improve the way we track parts.

On the physical end, we have several 60-drawer parts cabinets, kind of like
this one:

<http://images.netshops.com/mgen/master:RRG010.jpg>

Each one is labeled with the type and name/value of the part (Transistor,
3904 TO092), and the plan is to put row/column labels on each drawer.

I think the key to the component database, is random access. Any kind of
categorized system would require a lot of effort to maintain, and/or result
in drawers being underutilized.

With a random access system, you just put the new parts in the next
available drawer, and record their location in the database in the
"drawer:column:row" format.

Vitaliy

2007\10\08@145608 by James Newton

face picon face
Phil, I'm VERY interested in that. I've been wanting to write something like
it for years, and have actually posted about it on the PICList some time
ago. I want to host it as an application that many people can use and use to
share information (e.g. part name from number, replacement part when one is
obsoleted, etc...) and possibly to buy and sell between one another to
reduce shipping / time when possible.

I have a few local friends who I know stock inventory of parts all the time.
It makes no sense for me to order 50 capacitors (to get a good price) from
digikey when Kelly, just 4 blocks over, probably has them and would sell
them to me for the same price especially if he new that he could pick up
some part that I happen to have when he is out and it is holding up a board
he needs to get out. The best way to implement that sort of an "inventory
sharing" system is to have everyone publish their inventory and providing a
good inventory tracking service for free on a web server is the best way I
can see to get that going. As it is, I call Kelly and waste his time, or he
calls me and wastes my time asking if we have some little part.

I have developed a database that does a good job of setting up and tracking
categories of parts and what attributes they need. E.g. All electronic
components (passive or active) have a footprint. Passives like capacitors
have a type, rating(volts) and value(farads). All digital components belong
to a logic family (TTL, LS, CMOS, ...). You can't have space in a flat file
database for every possible attribute for every possible component; a hex
inverter doesn't need a farad value. So there is a table with a hierarchy of
types, which I call "COA" or "TagTree"

- Name: May describe an item (capacitor, resistor, etc...) a category of
items (active, passive, linear, etc...) or an attribute (footprint, logic
family, value in farads, value in ohms, etc...)
- isA: Points to other records in the same table which are higher in the
higherarchy. E.g. #35 "digital" points to #33 "active" which then points to
#26 "component"
- Type: Attribute, Category, Option or Item. Attributes and Options are
related but Attributes MUST be recorded for the Item or Category of item to
which they apply and Options MAY be recorded. If an Attribute points to a
Category, all the Items in that Category must have records that record the
value of the attribute for the items.

There is a main table with just the base information for each item:

- Store: who owns the part (me, you, radioshack, digikey)
- Unit: Each, roll, box
- Name: Part number (if applicable) and name. E.g. ""2N222 PNP Switching
Transistor"", "2K7 5% 0.25W Axial Resistor" (when it's in my store),
"660-CFS1/4CT52R272J Resistor" (when it is in Mousers store),
"CFS1/4CT52R272J Resistor" (when it is in the KOA Speer store),
- Price: not necessarily published. This is the one off price: Quantity
discounts are handled with Tags (see below)
- Quantity: number actually on hand.
- UPC: included for those items that have one.
Weight: for shipping purposes.

And then there is a separate table ("tags") that records attributes and
options for the items. It just records:
- TagTreeID: points to the entry in the COA for which this value is being
recorded. E.g. tag #2759 points to TagTreeID #150 which is "Use With": An
Option whose value points to another part which most users will want to use
with this part.
- string: a text value
- Value: a numeric value (Double)
- targetID: The item this tag is for.

In SQL, I have a stored procedure that pulls all the tags associated with an
item, or all the tags that apply to a type of item or category.

It is trivial to add an Option tag for "Location" under all parts that would
story the text description of a part. There is an existing web interface to
do that. There is also a web interface to edit the item, and add any
required or optional tags. It isn't pretty, but it works.

What I do not have at this point, is a system for limiting access to the
items that are applicable to your store.

I would love to work with you to integrate what you are doing and what
wouter is doing with what I am doing. It seems like we have taken very
different approaches.

Interested? Or do you want to keep doing it on your own?

--
James.

{Original Message removed}

2007\10\08@150547 by PicDude

flavicon
face
I've conjured various ways/apps to do this in the past and found that the real
problem is not how the data is stored/retrieved, but that maintaining it is
the real problem. When I'm working on a project and I go pull parts from the
bins, boxes, etc, I find it difficult to interrupt the process with managing
an inventory.

Nowadays, I have everything categorized in drawers such as Resistors-1206,
Resistors-0805, PIC's, Capacitors, etc.  When it comes time to re-order
parts, I can quickly look through the bins to find what I have.  Also, I have
a couple text files (actually their software post-it-notes on my win2k
desktop), that I have it an To-be-ordered list and a Regular-components list.  
As I'm working on a project, either designing or running low, I can add parts
to the first list as that's always on the desktop, and later when I place
orders, I check through both lists to make sure I don't miss anything.

Cheers,
-Neil.



On Sunday 07 October 2007 12:58, Philip Pemberton wrote:
{Quote hidden}

2007\10\08@161658 by Vitaliy

flavicon
face
Forrest W Christian wrote:
> For the production components, everything gets accounted for in
> inventory in Quickbooks Premier : Manufacturing Edition.   Every item in
> any product I manufacture gets an item in Quickbooks.  The Manufacturing
> Edition allows you to set up assemblies as well, with a list of items
> needed to build that assembly.   When you build an assembly (or a
> quantity of the assemblies), you just tell quickbooks to build X items
> and it automatically deducts the appropriate quantities.   As I'm
> pushing ~100K of product through every year, the cost of the Quickbooks
> Premier didn't put me off since I was already using quickbooks as my
> accounting package.

We also use QuickBooks ME, although I really dislike the interface.

I'm curious, what is your business? http://www.imach.com said "connection timed
out"....

Vitaliy

2007\10\08@162141 by Vitaliy

flavicon
face
alan smith wrote:
> The other nice thing to have is the sample folders that resistor and cap
> vendors give out...I just keep them restocked when I run low.

Where can I get one?

2007\10\08@204115 by Forrest W. Christian

flavicon
face
Vitaliy wrote:
> We also use QuickBooks ME, although I really dislike the interface.

> I'm curious, what is your business? http://www.imach.com said "connection
timed out"....

One of these days I'll get something back up on imach.com....which is
the domain for the "holding company" for lack of a better term..

For most of the electronic products we sell see
http://www.packetflux.com/ .  

-forrestc

2007\10\09@021110 by Philip Pemberton

face
flavicon
face
James Newton wrote:
> Phil, I'm VERY interested in that. I've been wanting to write something like
> it for years, and have actually posted about it on the PICList some time
> ago. I want to host it as an application that many people can use and use to
> share information (e.g. part name from number, replacement part when one is
> obsoleted, etc...) and possibly to buy and sell between one another to
> reduce shipping / time when possible.

Hmm, not my original plan, but certainly doable.
But I'd do that as a separate project instead of integrating it into ISIS.

> I have developed a database that does a good job of setting up and tracking
> categories of parts and what attributes they need. E.g. All electronic
> components (passive or active) have a footprint. Passives like capacitors
> have a type, rating(volts) and value(farads). All digital components belong
> to a logic family (TTL, LS, CMOS, ...). You can't have space in a flat file
> database for every possible attribute for every possible component; a hex
> inverter doesn't need a farad value. So there is a table with a hierarchy of
> types, which I call "COA" or "TagTree"
(snip)
> In SQL, I have a stored procedure that pulls all the tags associated with an
> item, or all the tags that apply to a type of item or category.

Nifty.

I've been using MySQL for the database, and designing the database with
DBDesigner 4 (which takes an entity relationship diagram and turns it into SQL
code). Here's my DB design:
<http://www.philpem.me.uk/temp/isis_erd.png>

Basically you have a number of core tables:
  - Manufacturer
  - Supplier
These store details about manufacturers and suppliers and have basically the
same format. Name, address, etc. ManufacturerAlias and SupplierAlias store
aliases for the manufacturer/supplier records, so you can (e.g.) have a
manufacturer record "OPTEK" that is aliased to "TT Electronics/OPTEK", so that
when you do a Digikey import, all the stuff for TT/Optek gets filed under
Optek. That way you can save typing when doing a mass import too - for e.g.
Fairchild, add an alias 'Fch' and when you enter the manufacturer 'Fch' in the
Mass Import function's manufacturer field, the new part will be marked as
being manufactured by Fairchild.

Next up is the Parts table - the core of the database. This stores details on
a given part - local stock code, description, manufacturer's part number,
manufacturer, number of pins, package, and any associated notes.

Then you have the Locations table. This stores details on the parts in a given
location. Pretty simple - the part ID matches (relates to) a part in the Parts
table, then you have a bin location and quantity. This means you can have 123
XYZ123s in box A, and another 41 in box B. You can also have more than one
different item in the same box - e.g. 12 XYZs in box A, five ABCs in box B,
and six QAZ123s in box A again.

StockEvents store data on previous stock transactions -- items that have been
added to or removed from stock. This is basically an audit trail - if
something is used, you can find out (quite easily) what it was used for.

Datasheets stores all the datasheets for all the parts. A part can have as
many datasheets as you like, and all of them are tied into the Part record.

BOM contains the per-BOM details - the project name and description. BOMLine
contains the actual lines of the BOM - the BOM the line is associated with,
the part it references, and the quantity required.

All pretty simple and pretty standard.

> I would love to work with you to integrate what you are doing and what
> wouter is doing with what I am doing. It seems like we have taken very
> different approaches.

You're welcome to do so - know any PHP? :)
I'm going to see about getting it onto Sourceforge soon (maybe), and put a
demo online at some point today, once I've got the new server set up.

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
RemoveMEpiclistspamTakeThisOuTphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\09@021111 by Philip Pemberton

face
flavicon
face
Timothy J. Weber wrote:
> Philip Pemberton wrote:
>> I'm working on getting a demo up and running for you guys to play with, but
>> the software on my webserver is being fussy.
>
> Very cool - I look forward to seeing it.

Not very soon you won't...
My dedicated server has a broken set of MySQL client libraries. The install
RPMs and such are no longer available, and it's a 'known bug'. There's no way
that PDO is going to work on that thing, so I've opted to replace it instead .
This is after a long bitter row with the hosting provider which came to a head
when they demanded more money (for less service even!) and I told them I was
giving them my 30 days notice...

I'm working on getting it installed on my file server (a Linksys NSLU2!) but I
fear the effect a bunch of PICLISTers might have on it... I really don't want
my home ADSL line DDOSing!

> I had thought that one big advantage of having a publicly-accessible
> database would be that you could share the table of part relations, like
> "same as," "see also," "deprecated in favor of," etc.  Obviously making
> it suit different working styles is hard, but the benefit of
> accumulating some of that shared knowledge for free over time might be
> worth it.

That's a neat idea. But one for Version 2 :)

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
piclistEraseMEspam.....philpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\09@100741 by Timothy J. Weber

face picon face
Philip Pemberton wrote:
> Timothy J. Weber wrote:
>> Philip Pemberton wrote:
>>> I'm working on getting a demo up and running for you guys to play with, but
>>> the software on my webserver is being fussy.
>> Very cool - I look forward to seeing it.
> Not very soon you won't...

Sadness!

I dread the day when I'm going to have to fire my current provider -
moving server stuff is a pain.

>> I had thought that one big advantage of having a publicly-accessible
>> database would be that you could share the table of part relations, like
>> "same as," "see also," "deprecated in favor of," etc.  Obviously making
>> it suit different working styles is hard, but the benefit of
>> accumulating some of that shared knowledge for free over time might be
>> worth it.
>
> That's a neat idea. But one for Version 2 :)

Definitely not a must-have!
--
Timothy J. Weber
http://timothyweber.org

2007\10\09@160355 by Thomas C. Sefranek

face picon face
I'm trying to figure out how I could merge my inventory into your system.
http://hamradio.cmcorp.com/inventory/Inventory.html

 *
 |  __O    Thomas C. Sefranek  EraseMEWA1RHPspamARRL.NET
 |_-\<,_   Amateur Radio Operator: WA1RHP
 (*)/ (*)  Bicycle mobile on 145.41MHz PL74.4

ARRL Instructor, Technical Specialist, VE Contact.
hamradio.cmcorp.com/inventory/Inventory.html
http://www.harvardrepeater.org

{Original Message removed}

2007\10\09@184007 by Philip Pemberton

face
flavicon
face
Thomas C. Sefranek wrote:
> I'm trying to figure out how I could merge my inventory into your system.
> http://hamradio.cmcorp.com/inventory/Inventory.html

"With some difficulty"

You'd need to convert your existing inventory into something Mass Import can
read, which is basically a CSV that uses pipe symbols (|) to separate the
fields, instead of commas.

I've got Mass Import working; the Digikey Import feature is proceeding
steadily along...

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
RemoveMEpiclistEraseMEspamEraseMEphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

2007\10\09@200518 by Howard Winter

face
flavicon
picon face
Phil,

On Sun, 07 Oct 2007 23:50:53 +0100, Philip Pemberton wrote:

> RemoveMEdpharrisspam_OUTspamKILLspamtelus.net wrote:
>...
> > PS I just came back from teh local hardware store with some (more) storage boxes...
>
> I've been buying mine from Farnell -- nearly all of them are Raaco A45 storage
> boxes. About six quid each, plastic, and built pretty decently.

I use Raaco storage trays, but I like the "Service Case 13" type which rather than the moveable dividers of the A45 they have compartments that can be swapped
in, out and around.  These come in various sizes and shapes from those that are 1/4 the size of the tray to those which are 1/32nd.  This gives lots of options for
various sized components and mixtures thereof.  They will also fit into a 4-high plastic rack which can be portable or fixed to a wall, which is handy for mobile jobs.  
This is Maplin's page for the rack with 4 trays included:  
http://www.maplin.co.uk/Module.aspx?ModuleNo=29041

They used to be about £30 for the rack plus 4 trays (£6 for a tray alone) but they've been going up lately.  Maplin recently had a clearance of a subtly different
version (blue handles and clear trays, rather than orange) for half this price, so I grabbed some while the going was good, but sadly they're now "discontinued".  
For some unaccountable reason, Rapid want £60 for them!

I label each tray with the headline title of the contents ("Switches", "IC Sockets", "Voltage Regulators", "Molex KK" etc) and then I label the individual containers
with the component values, part numbers, number of pins, whatever, and I don't keep track of the stock of these.  For PICs, however, I have a spreadsheet which
has one page with a line for the spec. items for each type I have (memory, pins, A/Ds, UARTs, etc.) and a matching second page which shows how many of each
type are in each container, with the total stock summarised back onto the spec. page.  I'm not interested in things like where they came from or the cost - if I was
doing it as a business I may think differently - so I don't need a database.

For active projects I use Rapid's "Education Storage Trays", such as:
www.rapidonline.com/searchresults.aspx?style=0&kw=14-0430
which allows me to have jobs in progress with all their parts and part-built boards etc. together in one place, and able to be put away when they aren't being
worked on.

This is what I do *now* - in years gone by I was less organised, and I have a lot of components in places where they are hard to find when I need them, so I need
to start re-organising these into my current filing system!

Cheers,


Howard Winter
St.Albans, England


2007\10\09@215136 by James Newton

face picon face
Phil, I do know a little PHP, although I don't normally code in it. I would
be very interested to see the code you have written, on sourceforge or
otherwise.

My database diagram is... a bit different. It's up on a page at:
http://www.piclist.com/techref/idea/website/pricebook.htm scroll down a bit.
As per that page:

Notice there are no fields for items that

 A) may not exist. For example, there is no field for "Mobile Phone" in the
Contacts table because the person may not have a cell... if we need one, it
gets added in to Tags, with an entry in COA via the COA/TagTree page.

 B) will probably have more than one. Price for example, there is always
more   than one price. MSRP, wholesale, street, purchase, etc...

I like your idea of having aliases for mfgrs and suppliers, although I would
have put mfgrs and suppliers in the same table and used a flag to
differentiate. I've added a Option tag called "alias" to my "Stores" table
(and in doing so found a serious bug and fixed it) so that now any store can
have any number of aliases. No changes to the data base schema required.

Location and datasheets are also tags. Datasheets stores a path and file
name to the actual datasheet file. How are you storing the datasheet? In the
SQL database itself? That might be better... Have to think about it.

BOM is related to kits of parts. I've been trying to figure out how I want
to handle that. Kits are also inventory items themselves. The quantity of
kits on hand is important to some people (me) for example. I was thinking
that I wouldl just add an "IsA" field to the ItemType table so that each
item could point to the kit item that it is a part of, but that doesn't work
because each item could easily be used in many different kits. So I think it
has to be another table. Maybe I could use the tags table, but more likely
I'll make another table "Kits" that just has "ItemTypeID" and "IsUsedBy"
both pointing into ItemType. "...WHERE Kits.IsUsedBy = ItemType.ID" gets you
a list of all the parts that are used to make a kit and "... WHERE
Kits.ItemTypeID = ItemType.ID" gets you a list of all the kits a specific
part is used in. Err... is that right?

Anyway, enough for today.

--
James

{Original Message removed}

2007\10\10@163950 by Philip Pemberton

face
flavicon
face
James Newton wrote:
> Phil, I do know a little PHP, although I don't normally code in it. I would
> be very interested to see the code you have written, on sourceforge or
> otherwise.
>
> My database diagram is... a bit different. It's up on a page at:
> http://www.piclist.com/techref/idea/website/pricebook.htm scroll down a bit.
> As per that page:

Ho. Lee. Fook.

Well it looks like we're both trying to solve different problems here.

I'm trying to solve the 'what do I have, how much did it cost me, where is it'
problem, and you seem to be going after 'what do my friends have that I need,
and what do they need that I have' problem...

Frankly I'm not that concerned at saving details about purchase price; I can
grab the invoices from the box file if I need to know that.

I like the 'tags' idea, and I might add that one to a later version of my DB.
Storage of parametric data would be very handy, as would a cross-reference of
some description. In other words, an answer to the question "I need a 2N3904,
what do I have that's similar?"

> I like your idea of having aliases for mfgrs and suppliers, although I would
> have put mfgrs and suppliers in the same table and used a flag to
> differentiate.

Or in some cases the Manufacturer may also be the Supplier... which causes two
records, and some data redundancy...

> Location and datasheets are also tags. Datasheets stores a path and file
> name to the actual datasheet file. How are you storing the datasheet? In the
> SQL database itself? That might be better... Have to think about it.

I'm storing the datasheet as a file on the server's hard disc drive, then
storing the location of that file in the database (as a path relative to
/webshare/datasheets, or wherever I decide to put them).

> Anyway, enough for today.

Too right. I finally got Mass Import working on the train to work this
morning, and Digikey import is just a variation of that (though it'll handle a
bit more than Digikey -- in theory you should be able to add another data
source by writing a data grabber plugin and adding a few lines to the
configuration file). A simplified version in fact -- Digikey P/N, and optional
quantity and location.

--
Phil.                         |  (\_/)  This is Bunny. Copy and paste Bunny
RemoveMEpiclistTakeThisOuTspamspamphilpem.me.uk         | (='.'=) into your signature to help him gain
http://www.philpem.me.uk/     | (")_(") world domination.

More... (looser matching)
- Last day of these posts
- In 2007 , 2008 only
- Today
- New search...