Negative Stock Quantity

[Neil879]

Hi all,

 

Hopefully I 've posted this in the right place

 

I'm new to filemaker and have been developing a maintenance inventory database.

 

I have a 'stock transactions' portal where I can add and remove stock. I am having trouble figuring out how to put a condition on the amount in stock where there can't be a negative amount. So if there is a quantity of 2 items in stock, only 2 can be removed. (if someone trys to take 3 out of stock at the moment, the stock amount would go to -1 for example).

 

Thanks in advance for any help/advice on this issue, I'm still learning so any feedback is greatly appreciated.

 

 

Thanks

 

 

 

Neil

[Lee Smith]

Hi Neil, and welcome to the FM Forums,

 

I moved your topic from "FileMaker Pro 12" to "Calculation Engine (Define Fields)” because, the General Topics are reserved for the discussion of the new tools, functions and features that were introduced in that version of FileMaker and not for asking how-to questions.

 

Why not attach a copy of your file, or a mockup of it, so we see what you have now? To attach a file, just follow the steps you will find here ATTACH A FILE.

[eos]

See if the attached file helps you:

 

 

InventoryAndStock_eos.fmp12.zip

[Neil879]

Hi Lee,

 

First of all thanks for replying I have been working on this project for a while and have started to 'hit brick walls' due to my lack of experience.

To give you an insight to my situation, re not allowing negative stock, I 've attached a copy of the file.

I'm developing a maintanence inventory database.

 

Thanks in advance for any advice you may have.

 

 

Neil

 

Hi Eos,

 

 

Thanks for replying  and attaching the file, had a look but not sure if it's what's wrong with my stock problem

MID Invent 200814.zip

[eos]

Neil -

 

when you post a sample database, please help people helping you by making clear what exactly the problem is and in which context it can be seen.

 

To give you an insight to my situation, re not allowing negative stock, I 've attached a copy of the file.

 

In your first post you mention a “Stock Transaction” portal – where is it? I'm afraid that your solution, as it stands, does a rather poor job of “giving insight” …    

 

Hi Eos,

Thanks for replying  and attaching the file, had a look but not sure if it's what's wrong with my stock problem

 

If I understood correctly, you want to prevent specifying a quantity to a transaction where the current stock level of the involved item would be exceeded. The attached file shows a way to do just that. This is of course a simplified setup, but you'll find the same components in your database, so if you study the techniques used there, you should be able to adapt them to your system.

 

btw, I see no reason to have separate tables “Order LineItems” and “StockTransactions” (and OrderDetails then is redundant anyway). They both describe the same type of transaction: a product in a given quantity is purchased or sold – which is what you need to distinguish.

 

That can can happen automatically, since you would create the different type of line items from different contexts (Purchase Order, Invoice etc.); also, you only would need to use one quantity field, and have an auto-enter/calculation field that calculates the correctly signed quantity based on the type of transaction.

 

Storing same-typed data in one table will make it much easier to create cross-transaction type reports (and of course, maintain and monitor your stock levels …).

[Neil879]

Eos-

 

First of all apoligies for not giving enough information on the attached file, although you seemed to have a grasp on the problem. Just incase there is any confusion, I have attached some images of the 'Stock Transactions' portal in the 'LayProduct' layout for your viewing.

 

What I'm trying to accomplish here is, when there is 2 items in stock then only 2 items can go out. If more than 3 items were to go out this would create a negative stock value, and also when stock was then to be added.

 

Re your comments about the file you attached and redundant tables, I am new to filemaker and still learning ,so I'm still discovering the right and wrong way to do things.

 

I appreciate any futher advice you may have

Edited August 21, 2014 by Lee Smith
removed the quote of dos

[eos]

First of all apoligies […] I am new to filemaker and still learning ,so I'm still discovering the right and wrong way to do things.

 

Hi Neil,

 

no apologies necessary. We all made our first posts and built our first DBs, though not necessarily in that order.

 

Some comments re your database:

 

1. At the moment, you create Stock transactions directly from a Product into a StockTransaction; it may be better to call a “StockTransaction” the process by which you move multiple items, and store these sub-transactions in a StockTransactions_lineItems table:

 

StockTransaction --< LineItems >-- Products

 

and do that from the context of a Transaction (Sales or Purchase), where first you create a Transaction, then select Items to add as line items (you'd normally move several products at once, anyway. Itf it's just one, no harm done either.

 

Now I've jumped the gun: as you can see, I also suggest that you regard Purchases and Sales as the same thing – it's just that the numbers have different signs - so you don't need an additional tables and a set of scripts, but just a few additional fields and are able to abstract lots of code.

 

Another thing: check your existing Products to StockTransactions relationship, and you'll see that you have set both tables to cascading deletes. While i may be a good idea to delete all related transactions once you delete a product, vice versa it isn't.

 

And then again, you should consider never deleting a product (and its child records) that has been already been used in an official capacity, i.e. appears on bills, quotes etc., or you wouldn't be able to make sense of these documents later on. Set a product inactive so it doesn't appear in selection lists for new processes, but leave it in the database.

 

OK, check out the attached modified database of yours in which I've shown how to manage your stock.

 

When you're sure you understand the principles, tear everything down, implement the new data structure I suggested and code it all fresh … 

MID_Invent_eos_MOD.fmp12.zip

[Neil879]

Hi Eos,

 

Thanks for reply . The data structure along with the advice has helped a lot with opening my eyes and implementing a solutution to my problem.

 

A fresh pair of eyes always helps !

 

 

Thanks

 

 

Neil

[comment]

What I'm trying to accomplish here is, when there is 2 items in stock then only 2 items can go out. If more than 3 items were to go out this would create a negative stock value

 

A small footnote: this is a good example of what makes good software. Suppose there are in fact 3 physical items in the warehouse, but - due to an earlier error - the recorded quantity in stock is 2 only.  Have you ever heard anyone say "I am trying, but the computer won't let me"?

[Neil879]

Hi,

 

While implementing the solution from the attached file below, I have realised that when stock reaches the reorder threshold, no futher transactions may take place (incoming/outgoing).

While it is logical that if there is no stock available then there can't be any outgoing, what is stopping more stock being added when stock is low? (below the threshold).

Basically what I'm trying to work out is a condition where when stock is below the threshold I am still able to add more stock (to top-up) and take stock out(to take out the last item if need be).

Hope I've explained this clear enough, any help is much appreciated.

 

Neil

 

Attached Files

• MID_Invent_eos_MOD.fmp12.zip 414.78KB 12 downloads

 

 

[eos]

´While it is logical that if there is no stock available then there can't be any outgoing, what is stopping more stock being added when stock is low? (below the threshold).

Basically what I'm trying to work out is a condition where when stock is below the threshold I am still able to add more stock (to top-up) and take stock out(to take out the last item if need be).

 

I suggest you study the script in the sample database more closely. It lets you add as many units (“top up”) as you like by way of an incoming transaction; a check is only performed for an outgoing transaction. There, an alert only appears if a negative quantity would be reached (which, I think, is the topic of this thread …?), so taking out the entire inventory is possible.

 

You could change the one-button alert that pops up for a negative quantity to a two-button dialog that offers the user the choice to proceed anyway, then query the user's choice, and either stop the script, or proceed with the requested quantity.

[Neil879]

I suggest you study the script in the sample database more closely. It lets you add as many units (“top up”) as you like by way of an incoming transaction; a check is only performed for an outgoing transaction. There, an alert only appears if a negative quantity would be reached (which, I think, is the topic of this thread …?), so taking out the entire inventory is possible.

 

I think I may have made a mistake implementing the script, as I am unable to make any further transaction when the stock reaches the reorder level.

I'll start a fresh and try again

[Neil879]

Eos,

 

My mistake, had missed part of a script.

 

Thanks for you're help.