Making Database for inventory Control?

[gullfounder]

I want to say hi to this community. I like to introduce my self to you guys before i ask any question. ;-)

So, My name is Waleed. I am from Pakistan and very new user to File Maker.

I just got my first Project in File Maker Pro. So, as i have told you guys that i am new to File Maker. That is why i am facing lot of Problems. Searching through the internet for Solution i came across this community. I really liked it because it is dedicated to File Maker.

Back to question!

I am trying to make a database for Little business. The objective is that

Supplier-(Product)->Stock-(Product)->Customer.

We buy product form certain Suppliers. After we bought certain Product it should be added into Stock. That stock should decrees as we sale that Product. So, If any time we want to check our Stock We can generate Report to see what we have left in stock so, we can order more product accordingly. One more thing is that we use Barcode reader for data entry for both Our purchase and Our Sale. Don't worry about the Barcode Print. As the Product Will already have there own barcodes. Similar Product type will use same barcode for each item. So, it mean Different Product will have there own Barcode that identifies them.

If it happens that some product are defective the customer can return it. So, its get re added to stock after new replacment arrives.

It's not that i have not done any work on it. I have done database making on Paper. But i need you guys help me complete it. Here is what i have Completed. I have attached the PDF file of the Database i made. Kindly help me improve it. Specily how to maintin the Stock?

Thanks in advance.

inventory.pdf

[Fitch]

Your Stock table might have for example, product ID, date, and quantity (and perhaps "source" or other info). When stock goes in or out, you create a record in this table with a positive or negative quantity. The current stock level would be the sum of the quantity field for a given product. You might want to script a process that stores this number in the products table, for better performance.

Hope that helps, and welcome to the forums, sir!

[gullfounder]

Thanks for your welcome and ans.You mean!

Stock table

Stock ID (Auto genrate, PK)

Product ID(FK)

Quantity

Date

But in long term doen't it will take lot of time to calculate the stock? Any better solution. Where i can quickly calculate the Stock? or any system that would allow me to just calculate from last month!

[Lee Smith]

Todd Geist shows how this can be done, here Inventory Transactions

HTH

Lee

[gullfounder]

Todd Geist shows how this can be done, here Inventory Transactions

HTH

Lee

Thanks Lee But i alreay read it. But as i am novice user i could not understand it.

[Fitch]

But in long term doen't it will take lot of time to calculate the stock?

You might want to script a process that stores this number in the products table, for better performance.

[gullfounder]

Oh! Right Sorry my mistake. :)

[Fitch]

Just to clarify, even if you do store the qty in Products -- which I think you will want to do -- you'll still want to have the detail records in Stock so you know the history of the inventory.

[gullfounder]

Just to clarify, even if you do store the qty in Products -- which I think you will want to do -- you'll still want to have the detail records in Stock so you know the history of the inventory.

Sorry Fitch! I did not understand it. I don't have any Qty Field in Product Table.

[Fitch]

Here's a discussion that may be of interest:

http://stackoverflow.com/questions/287097/inventory-database-design

[gullfounder]

Fitch, I have read your link. The answer number 16 was my preferred method. I like to apply that. Because i also have have 1000 of Products but sale are bit comparatively low. Still it is written that this method performs better then the rest.

I have a question though. Can this method easily applied in File maker Pro?

Would you provide me some guides on it?

[gullfounder]

I have worked on it. Kindly see it. If it is right.

Stock

Stock ID (PK)

Product barcode

Qty in

Qty out

Date

The above table will keep record of the item transaction. When we buy product it will put it in the Qty In field. Hence it will add the qty in Product Tables field "Item in Stock ". When we sell the product it will simple minus it from same field of the Product Table. IF the item is return for some reason this too added in the stock transaction and added to the Product table Item in Stock field. But if it is defective it will not be added to the stock. Instead returned to the supplier. But on arrival of new item it will be added to the Stock!

Is the above system is right? Can this be implemented in File maker Pro?

[Fitch]

I am not an expert in inventory management, and every company has their own business rules. That said, it doesn't seem necessary to have a separate field for in vs. out. A single field, with positive numbers for "in" vs. negative numbers for "out," makes it simple to total up.

When you buy product: create a positive record in Stock.

When you sell product: create a negative record in Stock.

When product is returned: create a positive record in Stock. I'd probably do this even for a defective product, and then when you send it back to the supplier, create a negative record. When supplier sends a replacement, create a record to reflect that. You may want an additional field in Stock to track returns, it could be a checkbox.

After every one of those steps, i.e. each time you create a new record in Stock, you'd update the "Item in Stock" field in Product.

All of this is fairly easily implemented in FileMaker, but there's no one "right" way, it will depend on your workflow and UI.

[gullfounder]

It is ok. This is i have worked out on the Database. I guess it is final! Kindly check it out.

inventory.pdf

[Fitch]

Nothing inherently wrong with your design. Just keep in mind that FileMaker's relationship graph is not an ERD. We typically employ multiple table occurrences of a given table in order to "see" the data from a particular context.

[gullfounder]

Thanks, I know that.