Here is a sample that might get you started.
This basic sample is to give you an idea on how to track the information. I didn't get into making a layout with portal or report to view a given week.
The idea would be that you have a list of products in one table.
And another table that acts like a sort of "transactions" table where you enter items bought (things you put into your stock) and items sold.
You enter a certain date and note the ProductId. Then you have one field for + (bought) and one for - (sold).
That way you can keep track of every "transaction" and know at any point in time how many items you have in stock without needing to count them.
If you need any help with this, make sure to let us know.
Well I guess to ask a differnet question. I have a a way to track what is bought and sold already with a ledger as shown in the screenshot on my origianl post, so I guess what I want is to have another ledger where if I hit a button it will add a blank line for all of my records (products) that I can go through and add the current weeks physical count and easily export just that week to excel. I think I essentially need a script but I dont know what.
DaSaint: I have already created a system to keep track of that, I still have to do a physical count every week to check against what the system says I have to confirm nothing was stolen or damaged, etc.
I found this an interesting problem and I'm guessing a lot of people might be struggling with Inventory databases.
So I decided to make a little (well not so little) screencast.
It's on making an inventory Database and in the end I also provide a system to correct your stock when you do an actual count.
Your video has been extremely helpful and I am changing my filmaker setup as I watch it.
However I have run into a roadblock that i cant figure out. I have attached a screenshot. I am trying to link my balance sheet to my product list, where the item id is the field being related. However when I try to create the cInstock I get a blank. If i try to insert the amtsold field for example it also comes up blank as if it doesnt see the items on the balance sheet. What am i missing?
What's the calculation you used to calculate the amount in stock?
You should have two summary fields in your Balalce sheet table that give you the total of the amount sold and the total of the amount bought.
And then create a calculation in your Product_Inventory table that does the total bought minus the total sold.
First of all check to see if your ID's are entered correctly in both tables. If those two related fields don't have the correct ID's in them there will not be any related records.
I can't tell you what might be going wrong because I don't have a lot of information to go on right now.
Do you have any extra table occurrences of any of these tables?
Finally figured out what was wrong, I had the two fields as different types. ..... :( lol
OK, I have gone through the video that you posted and really appreciate it, it was extremely helpful. I have two questions tho.
1. It sill does not address the one issue of how to log a weekly Physical Stock take. I guess I would just create an additional table but is there a script I could use to auto add a record for the week to all of my products that I can easily export to excel for that one week so i can see what products for example i may have missed in counting that week?
My company is still very set in Excel and I have to export a weekly stock take to email them every week.
2. Is there an easy way to import an excel spreadsheet to ur system of invoices (purchases) or sales reports? I have a pos system i would like to just import details from without retyping in all of the numbers for each of my 450+ items.
Thank you again!!!
1. What is it that you would want to do exactly? Do you want to count every product and then:
- enter the counted amount,
- store the calculated stock value and the counted amount somewhere (in a related table) together with the date so you can see how much you counted, when and what difference there was.
- Make a stock correction if nececcary
Something like that?
Because that's basically a small addition to the "correct stock" script.
If you would do that you would have a record of previous counts, would be able to create reports etc.
2. You can easily import an excell spreadsheet providing that all fields for a product are on a horizontal row.
What is it exactly you would like to import?
Is it product information?
Or sales and purchase data?
1. Yup that lol
2. Sales and purchase that I will import regularly. I really would like to just have a folder I can dump the spreadsheet into and have fm auto import it.
I didn't include the importing of the files in that screencast. It's focused on logging the corrections.
But you can make a script that imports from a file. That shouldn't be to hard.
That was very close to what I wanted and I made a few mods for my specific setup. Thank you! Im gonna bug you one more time for two scripts. As soon as I look at scripts I get lost - i know what i want it to do just not how to use filemakers lingo to make it happen.
1. Is there a way to make a script that when I push a button it will add a blank record (that I already have auto dating) in the StockLog table for every existing record in the Products(Animals) table. And if so to take it a step further, have it auto enter "0" into the counted stock field until I go in and actually put my count in. So essentially for my database it would auto create 450 new blank records in the StockLog table, one for eac product.
2. I can import the file easy enough but it doesnt add it to the portal correctly. It just adds new records for each item into my Purchases list. I know im doing this wrong. Also how do i tell FM to grab a new file when I add it to the folder once I get the import to work.
I am attaching a copy of my file for reference.