Inventory

[Ed Bullock]

Hi all

Does anyone have an example of an invoicing and stock control inventory database at the most basic level?

You add a product
Set the initial stock level
Do a new invoice (using line items)
The line items subtract the stock level
If the inventory had its own line items so you could track where stock had gone that would be most helpful

Just cannot get my head around the most efficient way to adjust stock when an item is sold
Many thanks for any basic offerings!

Ed

Sent from my BlackBerry® wireless device
--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Steve Cassidy]

> Does anyone have an example of an invoicing and stock control  
> inventory database at the most basic level?
>
> You add a product
> Set the initial stock level
> Do a new invoice (using line items)
> The line items subtract the stock level
> If the inventory had its own line items so you could track where  
> stock had gone that would be most helpful
>
> Just cannot get my head around the most efficient way to adjust  
> stock when an item is sold
> Many thanks for any basic offerings!

Be glad you find it difficult to get your head around. If it were  
easy, it would most likely not work well...

What I mean is that this is not a trivial question. And I'm not sure  
if you'll find a 'basic' example.

So you create a new invoice record and add a line item, as you  
suggest. Is the product sold at this point? You print out the invoice  
and a packing list. You send the packing list to the packers. Is the  
product sold at this point? The packers pack up the product and take  
it to the post office. Now is the product sold? Exactly when does the  
product leave your inventory? How do you tell your database that the  
critical point has been reached?

The customer doesn't like the product and sends it back. Now is the  
product in inventory or not?

In my experience, the best way to handle this is by scripting stock  
changes based on some kind of stock transaction record. So you raise  
an invoice with some invoice line items. At a certain point (say on  
printing), the stock is assumed to leave inventory and a stock  
transaction record is created (with a stock decrement). Some kind of  
handler script deducts the appropriate amount from the inventory  
quantity and marks the stock transaction record as processed.

Then you raise a purchase order, the ordered items arrive, and as  
each item is checked into the warehouse a stock transaction record is  
created (with a stock increment). The handler script adds the  
appropriate amount to inventory and marks the stock transaction  
record as processed.

An item is returned by the customer. A returns module generates a  
stock transaction record, the handler increments the stock, etc., etc.

This means that you can easily add processes that increment or  
decrement inventory by hooking into the standard handler routine that  
acts on stock transaction records. The stock transaction records form  
a nice little audit trail of where your stock is going.

Sounds easy, perhaps. Gets a bit complex with thousands of products  
in inventory and with sales going on via multiple channels. And a bit  
more complex when you have stock in multiple locations. But it is  
certainly doable and is scalable. Steer clear of any system where the  
inventory amount is calculated as a sum of all purchases minus the  
sum of all sales; this will not scale very well. A hybrid system is  
possible, where your inventory is a calculated sum in this way -- but  
at a certain point you consolidate old records and remove them from  
the calculation.

Are these the kind of ideas you are looking for?

Steve






--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Ed Bullock]

Hi Steve

Many thanks for your reply. I think that's where I was going wrong, I was aiming for a calculated inventory system whereas as you say a scripted one would not only function better but would also not slow down with time and records.

I like the idea of the stock transaction records, marking them as processed as part of the script then looking for non-processed records when the script next runs.

But just off the top of my head how would the handler script deduct the stock?

Let's say on 1 invoice I sell...
Product A x 1
Product B x 2
Product C x 1

I print the invoice, at that point 3 stock transaction records are created
Product A - 1
Product B - 2
Product C - 1

All 3 stock transaction records have a flag on processed=0 (as opposed to processed=1 when it has been done)

Would I then run the handler script off the back of the script used to print and create the transaction records?
Find processed=0 and loop through the found set. Ok I get that but what would the handler script do to each record, how would it take the value and remove it from the current stock count for that particular product in the Products table?

Many thanks for your help. Things are finally starting to become clear!

Ed
 
Sent from my BlackBerry® wireless device

-----Original Message-----
From: Steve Cassidy <[hidden email]>

Date: Sat, 27 Jun 2009 00:08:55
To: FileMaker Talk<[hidden email]>
Subject: Re: Inventory

> Does anyone have an example of an invoicing and stock control  
> inventory database at the most basic level?
>
> You add a product
> Set the initial stock level
> Do a new invoice (using line items)
> The line items subtract the stock level
> If the inventory had its own line items so you could track where  
> stock had gone that would be most helpful
>
> Just cannot get my head around the most efficient way to adjust  
> stock when an item is sold
> Many thanks for any basic offerings!

Be glad you find it difficult to get your head around. If it were  
easy, it would most likely not work well...

What I mean is that this is not a trivial question. And I'm not sure  
if you'll find a 'basic' example.

So you create a new invoice record and add a line item, as you  
suggest. Is the product sold at this point? You print out the invoice  
and a packing list. You send the packing list to the packers. Is the  
product sold at this point? The packers pack up the product and take  
it to the post office. Now is the product sold? Exactly when does the  
product leave your inventory? How do you tell your database that the  
critical point has been reached?

The customer doesn't like the product and sends it back. Now is the  
product in inventory or not?

In my experience, the best way to handle this is by scripting stock  
changes based on some kind of stock transaction record. So you raise  
an invoice with some invoice line items. At a certain point (say on  
printing), the stock is assumed to leave inventory and a stock  
transaction record is created (with a stock decrement). Some kind of  
handler script deducts the appropriate amount from the inventory  
quantity and marks the stock transaction record as processed.

Then you raise a purchase order, the ordered items arrive, and as  
each item is checked into the warehouse a stock transaction record is  
created (with a stock increment). The handler script adds the  
appropriate amount to inventory and marks the stock transaction  
record as processed.

An item is returned by the customer. A returns module generates a  
stock transaction record, the handler increments the stock, etc., etc.

This means that you can easily add processes that increment or  
decrement inventory by hooking into the standard handler routine that  
acts on stock transaction records. The stock transaction records form  
a nice little audit trail of where your stock is going.

Sounds easy, perhaps. Gets a bit complex with thousands of products  
in inventory and with sales going on via multiple channels. And a bit  
more complex when you have stock in multiple locations. But it is  
certainly doable and is scalable. Steer clear of any system where the  
inventory amount is calculated as a sum of all purchases minus the  
sum of all sales; this will not scale very well. A hybrid system is  
possible, where your inventory is a calculated sum in this way -- but  
at a certain point you consolidate old records and remove them from  
the calculation.

Are these the kind of ideas you are looking for?

Steve






--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Steve Cassidy]

Gee, you sent that lot from your Blackberry? Didn't know those things  
came with a screen big enough!!

With a recent version of Filemaker, I'd simply set some variables for  
each line item (item id, quantity, etc.) then call a script that  
creates the stock transaction records (you could do this either with  
an array-style variable or by looping the script through the line  
items). That script could run a generic subscript that processes the  
stock transaction items. (Error checking can be important here; your  
transaction needs to fail if a product record is locked by another  
user.)

You could of course batch-process the stock transaction records  
instead -- once a day or whatever. It all depends on your inventory  
needs.

As to how you go from a transaction record to a product stock  
deduction, I wouldn't really like to comment on a modern way to do  
it. The most sophisticated inventory system I made is still stuck in  
FMP6, where each table was in a separate file and there were no  
variables. I used a utility relationship between the transaction file  
and the product file. That enabled me to use the quick Go To Related  
Record step to isolate the product record and run a script to deduct/
increment the inventory value. It may be that a similar approach is  
still good.

HTH

Steve


On 27 Jun 2009, at 10:34, [hidden email] wrote:

> Many thanks for your reply. I think that's where I was going wrong,  
> I was aiming for a calculated inventory system whereas as you say a  
> scripted one would not only function better but would also not slow  
> down with time and records.
>
> I like the idea of the stock transaction records, marking them as  
> processed as part of the script then looking for non-processed  
> records when the script next runs.
>
> But just off the top of my head how would the handler script deduct  
> the stock?
>
> Let's say on 1 invoice I sell...
> Product A x 1
> Product B x 2
> Product C x 1
>
> I print the invoice, at that point 3 stock transaction records are  
> created
> Product A - 1
> Product B - 2
> Product C - 1
>
> All 3 stock transaction records have a flag on processed=0 (as  
> opposed to processed=1 when it has been done)
>
> Would I then run the handler script off the back of the script used  
> to print and create the transaction records?
> Find processed=0 and loop through the found set. Ok I get that but  
> what would the handler script do to each record, how would it take  
> the value and remove it from the current stock count for that  
> particular product in the Products table?
>
> Many thanks for your help. Things are finally starting to become  
> clear!

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Slober]

Just do add up to Steve's scenario:

  - usually, when client puts an order, your inventory system should allocate qty i.e  decrease available    quantity, so no other order can use the same product if there is no sufficient qty available.

  - what if client orders some products you currently don't have in stock ?
    That kind of request should go under "Back Order" category.

  - Also, what kind of decrementing method you would use - FIFO, LIFO or something else?
 

Cheers,

Slobodan
   

Steve Cassidy wrote:

Gee, you sent that lot from your Blackberry? Didn't know those things  
came with a screen big enough!!

With a recent version of Filemaker, I'd simply set some variables for  
each line item (item id, quantity, etc.) then call a script that  
creates the stock transaction records (you could do this either with  
an array-style variable or by looping the script through the line  
items). That script could run a generic subscript that processes the  
stock transaction items. (Error checking can be important here; your  
transaction needs to fail if a product record is locked by another  
user.)

You could of course batch-process the stock transaction records  
instead -- once a day or whatever. It all depends on your inventory  
needs.

As to how you go from a transaction record to a product stock  
deduction, I wouldn't really like to comment on a modern way to do  
it. The most sophisticated inventory system I made is still stuck in  
FMP6, where each table was in a separate file and there were no  
variables. I used a utility relationship between the transaction file  
and the product file. That enabled me to use the quick Go To Related  
Record step to isolate the product record and run a script to deduct/
increment the inventory value. It may be that a similar approach is  
still good.

HTH

Steve


On 27 Jun 2009, at 10:34, [e-mail] wrote:

> Many thanks for your reply. I think that's where I was going wrong,  
> I was aiming for a calculated inventory system whereas as you say a  
> scripted one would not only function better but would also not slow  
> down with time and records.
>
> I like the idea of the stock transaction records, marking them as  
> processed as part of the script then looking for non-processed  
> records when the script next runs.
>
> But just off the top of my head how would the handler script deduct  
> the stock?
>
> Let's say on 1 invoice I sell...
> Product A x 1
> Product B x 2
> Product C x 1
>
> I print the invoice, at that point 3 stock transaction records are  
> created
> Product A - 1
> Product B - 2
> Product C - 1
>
> All 3 stock transaction records have a flag on processed=0 (as  
> opposed to processed=1 when it has been done)
>
> Would I then run the handler script off the back of the script used  
> to print and create the transaction records?
> Find processed=0 and loop through the found set. Ok I get that but  
> what would the handler script do to each record, how would it take  
> the value and remove it from the current stock count for that  
> particular product in the Products table?
>
> Many thanks for your help. Things are finally starting to become  
> clear!


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Ed Bullock]

Just a quick note Steve to say thank you for all your help
Have got it working perfectly, checking stock, removing it from the
inventory etc. All scripted rather than with calculations. Have even got it
ignoring line items that are set as services rather than products.

And yes, 10 years working with a Blackberry gives you super fingers!

Thanks again, your help is very much appreciated.

Ed

-----Original Message-----
From: FileMaker Talk [mailto:[hidden email]] On Behalf Of Steve
Cassidy
Sent: 27 June 2009 11:02
To: FileMaker Talk
Subject: Re: Inventory

Gee, you sent that lot from your Blackberry? Didn't know those things  
came with a screen big enough!!

With a recent version of Filemaker, I'd simply set some variables for  
each line item (item id, quantity, etc.) then call a script that  
creates the stock transaction records (you could do this either with  
an array-style variable or by looping the script through the line  
items). That script could run a generic subscript that processes the  
stock transaction items. (Error checking can be important here; your  
transaction needs to fail if a product record is locked by another  
user.)

You could of course batch-process the stock transaction records  
instead -- once a day or whatever. It all depends on your inventory  
needs.

As to how you go from a transaction record to a product stock  
deduction, I wouldn't really like to comment on a modern way to do  
it. The most sophisticated inventory system I made is still stuck in  
FMP6, where each table was in a separate file and there were no  
variables. I used a utility relationship between the transaction file  
and the product file. That enabled me to use the quick Go To Related  
Record step to isolate the product record and run a script to deduct/
increment the inventory value. It may be that a similar approach is  
still good.

HTH

Steve


On 27 Jun 2009, at 10:34, [hidden email] wrote:

> Many thanks for your reply. I think that's where I was going wrong,  
> I was aiming for a calculated inventory system whereas as you say a  
> scripted one would not only function better but would also not slow  
> down with time and records.
>
> I like the idea of the stock transaction records, marking them as  
> processed as part of the script then looking for non-processed  
> records when the script next runs.
>
> But just off the top of my head how would the handler script deduct  
> the stock?
>
> Let's say on 1 invoice I sell...
> Product A x 1
> Product B x 2
> Product C x 1
>
> I print the invoice, at that point 3 stock transaction records are  
> created
> Product A - 1
> Product B - 2
> Product C - 1
>
> All 3 stock transaction records have a flag on processed=0 (as  
> opposed to processed=1 when it has been done)
>
> Would I then run the handler script off the back of the script used  
> to print and create the transaction records?
> Find processed=0 and loop through the found set. Ok I get that but  
> what would the handler script do to each record, how would it take  
> the value and remove it from the current stock count for that  
> particular product in the Products table?
>
> Many thanks for your help. Things are finally starting to become  
> clear!

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Ed Bullock]

Hi all,

I had this working but have found a small issue with my setup...

If a customer orders 5 of product A, this creates a stock transaction record
which is flagged to be processed.
When the invoice is printed, it processes the stock transaction record and
takes 5 off the stock quantity in the inventory table, and marks it as
processed.
That is all working fine.

Where I have the problem is the user can just change the quantity of the
line item on the order. If the invoice is then reprinted that line has
already been processed so the extra items never get taken from stock.

Obviously just changing a quantity is not the way the users should be doing
this, they should either credit it if they want less items or add another
line if they want more of the item.

How can I stop the user from changing the quantity once the invoice has been
printed and the stock taken from the inventory?

Can I "lock" the quantity field once the invoice has been printed?

Many thanks
Ed

-----Original Message-----
From: FileMaker Talk [mailto:[hidden email]] On Behalf Of Ed
Bullock
Sent: 29 June 2009 00:52
To: FileMaker Talk
Subject: Re: Inventory

Just a quick note Steve to say thank you for all your help
Have got it working perfectly, checking stock, removing it from the
inventory etc. All scripted rather than with calculations. Have even got it
ignoring line items that are set as services rather than products.

And yes, 10 years working with a Blackberry gives you super fingers!

Thanks again, your help is very much appreciated.

Ed

-----Original Message-----
From: FileMaker Talk [mailto:[hidden email]] On Behalf Of Steve
Cassidy
Sent: 27 June 2009 11:02
To: FileMaker Talk
Subject: Re: Inventory

Gee, you sent that lot from your Blackberry? Didn't know those things  
came with a screen big enough!!

With a recent version of Filemaker, I'd simply set some variables for  
each line item (item id, quantity, etc.) then call a script that  
creates the stock transaction records (you could do this either with  
an array-style variable or by looping the script through the line  
items). That script could run a generic subscript that processes the  
stock transaction items. (Error checking can be important here; your  
transaction needs to fail if a product record is locked by another  
user.)

You could of course batch-process the stock transaction records  
instead -- once a day or whatever. It all depends on your inventory  
needs.

As to how you go from a transaction record to a product stock  
deduction, I wouldn't really like to comment on a modern way to do  
it. The most sophisticated inventory system I made is still stuck in  
FMP6, where each table was in a separate file and there were no  
variables. I used a utility relationship between the transaction file  
and the product file. That enabled me to use the quick Go To Related  
Record step to isolate the product record and run a script to deduct/
increment the inventory value. It may be that a similar approach is  
still good.

HTH

Steve


On 27 Jun 2009, at 10:34, [hidden email] wrote:

> Many thanks for your reply. I think that's where I was going wrong,  
> I was aiming for a calculated inventory system whereas as you say a  
> scripted one would not only function better but would also not slow  
> down with time and records.
>
> I like the idea of the stock transaction records, marking them as  
> processed as part of the script then looking for non-processed  
> records when the script next runs.
>
> But just off the top of my head how would the handler script deduct  
> the stock?
>
> Let's say on 1 invoice I sell...
> Product A x 1
> Product B x 2
> Product C x 1
>
> I print the invoice, at that point 3 stock transaction records are  
> created
> Product A - 1
> Product B - 2
> Product C - 1
>
> All 3 stock transaction records have a flag on processed=0 (as  
> opposed to processed=1 when it has been done)
>
> Would I then run the handler script off the back of the script used  
> to print and create the transaction records?
> Find processed=0 and loop through the found set. Ok I get that but  
> what would the handler script do to each record, how would it take  
> the value and remove it from the current stock count for that  
> particular product in the Products table?
>
> Many thanks for your help. Things are finally starting to become  
> clear!

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/



--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Jonathan Fletcher]

On Jul 4, 2009, at 8:54 PM, Ed Bullock wrote:

> Can I "lock" the quantity field once the invoice has been printed?


You answered your own question. Printing the invoice posts the data  
and then locks everything. You can lock fields under certain  
circumstances or you can just create a layout that displays printed  
invoices with all the fields locked. Several pros and cons on each  
side. You decide which works best for you.

Another thing you can do is to ask the user if the invoice printed  
properly. If the user answers yes then it will post the data and lock  
the invoice, otherwise it will take the user back and allow them to  
edit and/or retry.

If the user later wants to edit an invoice, you should back out all  
posted transactions and allow the user to retry. Keep in mind, though,  
that there are certain things that cannot be undone easily. For  
instance:
   . retrieve invoices that are already in customers' possession
   . back out batched credit card transactions
   . undo a check deposit

In those cases, you should enforce the creation of a new invoice that  
adjusts the quantity and invoiced amount and creates a new charge or  
credit on the customer's account.

Also, if you keep running accounts for a particular customer, know the  
difference between an invoice and a statement, and why.

I hate accounting just as much as the next guy, but I grudgingly  
acknowledge that it's what makes the world go round.

Or at least keeps track of it...

j.



--
Jonathan Fletcher
FileMaker 9 Certified Developer
FileMaker 10 Certified Developer

Project Foreman
NewMedia Construction Co.
[hidden email]

Instigator
The BB&J Network
The "Go-To Guys" for
FileMaker Development in Louisville
www.thebbandj.net

FileMaker Louisville Blog and Podcast:
www.filemakerlouisville.posterous.com


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/