1 2 Previous Next 27 Replies Latest reply on Sep 27, 2014 3:47 AM by GuyStevens

    Creating a Physical Inventory Database

    ZoeCrafton

      Title

      Creating a Physical Inventory Database

      Post

           I am somewhat new to Filemaker and have got some basics down but I have reached a point where I am stuck. i am only vaguely familiar with scripts and I think thats where my break down is.

           I have an Invenotry database of product for my store that I have built and I have created two portals to keep track of my invoices and my sales reports. Essentially one is what I receive and one is what goes out. 

           The problem I have is taking physical counts which i do weekly. What I have been doing is copying my filemaker file every week taking the counts and then saving the file with the week as the name. I am trying to figure out how to keep each week in one database. I thought making another portal as a ledger might work, but I cant wrap my head around how to make it add a week for every item in my inventory.

           I have basically reached the extent of my knowledge and cant figure out how to fine tune and simplify my database.

      Screen_Shot_2013-04-19_at_3.11.05_PM.png

        • 1. Re: Creating a Physical Inventory Database
          schamblee

               Here is a sample that might get you started.

          https://www.sugarsync.com/pf/D9559058_790_062480517

               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.

                

          • 2. Re: Creating a Physical Inventory Database
            GuyStevens

                 Hi Zoe,

                 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.

            • 3. Re: Creating a Physical Inventory Database
              ZoeCrafton

                   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. 

              • 4. Re: Creating a Physical Inventory Database
                GuyStevens

                     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.

                http://www.youtube.com/watch?v=9vKzIGXWv7s

                     Greetings

                     DaSaint

                • 5. Re: Creating a Physical Inventory Database
                  ZoeCrafton

                       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?

                  • 6. Re: Creating a Physical Inventory Database
                    GuyStevens

                         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?

                    • 7. Re: Creating a Physical Inventory Database
                      ZoeCrafton

                           Finally figured out what was wrong, I had the two fields as different types. ..... :( lol

                      • 8. Re: Creating a Physical Inventory Database
                        GuyStevens

                             Whoopsie laugh

                             Happens to the best of us :)

                        • 9. Re: Creating a Physical Inventory Database
                          ZoeCrafton

                               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!!!

                          • 10. Re: Creating a Physical Inventory Database
                            GuyStevens

                                 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?

                            • 11. Re: Creating a Physical Inventory Database
                              ZoeCrafton

                                   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.

                              • 13. Re: Creating a Physical Inventory Database
                                GuyStevens

                                     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.

                                • 14. Re: Creating a Physical Inventory Database
                                  ZoeCrafton

                                       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.

                                  https://www.dropbox.com/s/z298ifab61us4ja/Inventory%20Count%20EDIT.fmp12

                                       Thanks again!!!!

                                  1 2 Previous Next