Re: New boards are ready, but there is an issue....

[portscan]

Hi all.

Im a new one here.

and waiting my stuff from smash (  8) )

Idea : i spend my work time building access databases used for stock/order management.

So Smash : would you be interested by such a tool, if it might help you do your stuff faster/more easily ?

Bye

[th0mas]

access database.. isn't that an oxymoron? :)

[portscan]

lol

actually access + vba is very good to build quite simple stuff very quickly and quite user friendly (even if its some crosoft sh*t). 2003 is a nice one. far from the 97 ugly one. perfect choice imho for a few users with no big web stuff.

much faster than php&mysql (which i do too, when the customer says he wants "pro stuff" for 2 users with no website behind, and we apply a "pro price"  ;D ).

[smashtv]

Idea : i spend my work time building access databases used for stock/order management.

So Smash : would you be interested by such a tool, if it might help you do your stuff faster/more easily ?

My problem right now is that my inventory is not automated, and there does not seem to be an easy way to do this without parsing PayPal IPN.  I realize I can go with a full blown shopping cart like osCommerce, but I really would rather keep my shop a simple one-page order form style.  All of the store packages are really bloated and seem to -almost- do everything, when what I really need is something that will simply change inventory totals in a db by reading and responding to the PayPal IPN.  I won't put the customer through entering their address twice (amazing that -most- of the shopping cart packages can't even pass the user info to paypal correctly) or make them click through 5 screens between done and checkout....

I really would like to stay away from the asp stuff for this, It's a real pain to try to implement frontpage extensions on my host (would cost me lots of work on my other scripts/cgis due to the permissions reset alone, and slow the delivery of my pages a bit).  As far as I can tell, the ideal setup would be a php or perl IPN handler that subtracts stock as needed from an SQL db.....And does ONLY that.  I don't want customer address/email/info hanging out on my server.  If anyone else has been through this before and knows a better way please let me know! 

I'm not anti-MS for this, but I don't need one asp/access combo sitting in the middle of all of my other php/sql pairings.... ;)

So the short answer is yes, I am very interested in this, and I would be happy to pay or trade to have it done.

Best

Smash

[portscan]

i didnt know you already had a sql db ! nice...

somethin like :

a  file created from the transaction and that's used by a simple "INSERT into" sql query at the end of you ipn script ?

doable............

a query that inserts item number and -quantity to the inventory table.

any way to have a description of the inventory table ? (table and field names) and the partlist table if you use indexes.

should take a big dozen lines max into the ipn.

or maybe a new ipn, don't know where you found yours

then to get you inventory you'll have to sum the quantities per item (another ultra basic query). the usual start stock + in - out = actual stock.

you could event show this stock in your order form so the user knows there's no proto boards  ;D .

bye

[smashtv]

i didnt know you already had a sql db ! nice...

I had been using phppointofsale (phpPOS) as a front end, it's able to warn me about stock levels, allows for easy manipulation of counts for re-stock, etc.  It's a full-blown cash register with inventory, so I made one customer record in it for cash/paypal sales and ran inventory on the fly with a barcode gun for a while.  Even with the bins barcoded etc. it proved to be way too much of a time waster when packing orders to scan everything.....and the day I realized I could pack 10 orders or 8 orders in the same amount of time depending on whether I scanned them or not the barcode method ended.  ;)

somethin like :

a  file created from the transaction and that's used by a simple "INSERT into" sql query at the end of you ipn script ?

doable............

a query that inserts item number and -quantity to the inventory table.

I think most IPN handler scripts have the db manipulation built into the handler script.......Basically when a transaction is complete paypal can be set to hit an address to your IPN handler script, and feeds it all the data about the transaction.  The handler must give a "this transaction is ok" type response back to PayPal before the transaction shows up in your account.

The good thing is there is -almost- an easy button for this, there are a few code examples out there for ASP to do exactly this, and I think one for PHP.....I'll dig up all of this stuff if you are interested in writing this handler.  I'm not coder, but not afraid to tweek php or perl stuff to get to goal, so adding db calls to any script is a bit over my head. ;)

any way to have a description of the inventory table ? (table and field names) and the partlist table if you use indexes.

Absolutely!  I removed the phpPOS front end script a few months ago, but the db is still there.  I'll dump it to a tab-delimited or csv so you can have a look.  First I'll install the latest phpPOS and point it at the db, since I want to use it when it's time to add inventory I should probably make sure they did not add any fields to the db.....to be sure the structure you are looking at is exactly what I'll be using forever.  :)

you could event show this stock in your order form so the user knows there's no proto boards  ;D .

hehe that's what actually sent me on the quest for automatic inventory a long time ago..... :)  I did not realize then that I would need it more for me than for my customers! 

I'll get all of the pieces to the puzzle mentioned above together and present them to you, so you can get a reasonable estimate of time/effort/price before you commit to this, and we'll go from there.

As I work against this backlog of orders, I really miss the old days when I was able to ship everything within 48 hours.....Auto inventory would be a huge step in this direction, or at the very least keep my eyes from popping open in the middle of the night, wondering if I remembered to order a reel of ribbon cable.  :)

Best

Smash

[portscan]

1) supply chain

sorry if im gonna look like a teacher but...(as a supply chain and warehouse manager  ;D i can't resist)

I see too big chickens :

-the time you waste on inventories, but its on the good way

-the time you need to process an order (won't this be the next thing?)

if you want to improve your "customer order delivery service rate" at no cost.

the first 2 things to think of are :

-do the lead times from my suppliers correspond to my supply chain system ?

-What do i need to improve my lead times ?

and this needs :

-stock and order management working closely together, stock alerts when an id's stock is decreasing too fast (once it's at 0 it's simply too late) (you already have this alert system but you waste your time on inventories),

-Calculating your suppliers' service rate (and serialize yours after that, 85% is the strict minimum, above 95% is too expensive) and find what to do to be in these targets.

Actually sales/production/supply chain are 3 definitely different jobs that must be in one same brain.

your orders seem to have increased a lot since a few weeks, and 6 weeks just to process an order is simply way too much and very dangerous. (add delivery and your get 9 weeks from customer order to customer delivery, even OEM's producing on order only can do better).

As soon as you have more orders incoming than orders going out, then its getting verrrrry dangerous (looking at your order status page makes me  :o  each time i go and see if mine has been shipped).

Supply chain is the key !

BUT you got it before i write it, that was obvious, i just wanted to confirm this.

2)warehouse

I agree with you about barcodes, in your case, with small electronic parts, it's simply a waste of time.

Decreasing stocks from order validations is really the key in your case.

All you have to do is making sure you pack the right quantities and  do FIFO on each part.

3)SQL, PHP, IPN

Ok i'd be really happy to see your db's structure and show you an IPN php script that automatically decreases the stock once the order is validated.

So now ok i'll wait for your datas and check what i can do on it.

Bye

Cyrille

[smashtv]

1) supply chain

sorry if im gonna look like a teacher but...(as a supply chain and warehouse manager  ;D i can't resist)

I see too big chickens :

-the time you waste on inventories, but its on the good way

-the time you need to process an order (won't this be the next thing?)

if you want to improve your "customer order delivery service rate" at no cost.

the first 2 things to think of are :

-do the lead times from my suppliers correspond to my supply chain system ?

-What do i need to improve my lead times ?

and this needs :

-stock and order management working closely together, stock alerts when an id's stock is decreasing too fast (once it's at 0 it's simply too late) (you already have this alert system but you waste your time on inventories),

-Calculating your suppliers' service rate (and serialize yours after that, 85% is the strict minimum, above 95% is too expensive) and find what to do to be in these targets.

Actually sales/production/supply chain are 3 definitely different jobs that must be in one same brain.

your orders seem to have increased a lot since a few weeks, and 6 weeks just to process an order is simply way too much and very dangerous. (add delivery and your get 9 weeks from customer order to customer delivery, even OEM's producing on order only can do better).

As soon as you have more orders incoming than orders going out, then its getting verrrrry dangerous (looking at your order status page makes me  :o  each time i go and see if mine has been shipped).

Supply chain is the key !

BUT you got it before i write it, that was obvious, i just wanted to confirm this.

First off thanks Cyrille for the help on this.  :)

On lead times:  some of my stuff comes from the east via slow boat needing a one month lead time, and some from a few hundred miles away needing only one business day.  I can usually cope with demand spikes with the slower vendors by ordering double what current demand calls for, but if a long lead time vendor slips up and sends the wrong thing, loses a package, etc. that concept breaks quickly. 

Totally agreed on 6 weeks being way too long.....My time to convert bulk parts into kits has been limited by other things lately, but that is temporary.  I have been blessed upside my head with a full schedule with double bookings for my production company -and- the most demand ever from the store.  Factor in the time required to be a good father, and it makes even a bad 9 to 5 job look like a vacation.  :)

2)warehouse

I agree with you about barcodes, in your case, with small electronic parts, it's simply a waste of time.

Decreasing stocks from order validations is really the key in your case.

All you have to do is making sure you pack the right quantities and  do FIFO on each part.

The barcode guns were not a total waste, they make quick verification of incoming parts much easier, and save me huge time and trouble keeping up with parcel and customs numbers.  Already there on the FIFO thing, -everything- here was freshened up in recent months for RoHS compliance.....Anything that will bin over a few days gets heat sealed in an airtight bag with fresh silica gel packets.  ;)

3)SQL, PHP, IPN

Ok i'd be really happy to see your db's structure and show you an IPN php script that automatically decreases the stock once the order is validated.

So now ok i'll wait for your datas and check what i can do on it.

Bye

Cyrille

Very cool, thank you!

Caught up is the word of the day for me right now, this sitting was for shipping labels (and they are done) so I'm off to knock a few more off the list then grab the db stuff to send to you.  :)

Best

Smash