3 Replies Latest reply on Apr 5, 2013 12:49 PM by philmodjunk

    Inventory database



      Inventory database


           Hi everyone,

           I need to create a inventory database that will allow me to scan an item and if the item is not in the database to create a record and if the record exist to enter into another layout to and ask how many to add to quantity.

           Here are the layouts I made


      Quantity to add

           Here are the fields I created.





      Extended Price

      Qty to add

           I know that there might be a similar question on this forum but to be honest I really didn't get anything they were talking about.:(


        • 1. Re: Inventory database

               You might have several layouts that each refer to the same table or they may refer to different tables.

               If you open Manage | Database | Tables, what Tables do you have?

               If you then click Relationships, what relationships between your tables do you have?

               What precisely do you need help with?

               How to automate the scanning process?

               How to process the data once it has been scanned into the field?

               If you haven't seen it already, please look at this thread if you want to automate the scanning process and will use a scanner that connects via USB to your computer: Need help writing script to integrate barcode scanning

          • 2. Re: Inventory database

                 I have 1 Table Called (INVEN), 2 Layouts (one is called Inventory and the other is called Quantity to add)

                 There is no relationship built yet.

                 I need help building a database that will store my items, items's barcode, price, quantity and the extended price.

                 I like to write a script that when we scan a barcode Filemaker will automatically look for the item and if it does't find one to create a new record and wait for the user input. If the barcoded item already exist to go to the other layout called (Quantity to add) and wait for user input. When the user writes in 3 for instance the script to add 3 to the item's original quantity

                 I hope this make sense. I thought it would be incredibly easy but I was proven wrong :)


                 Enclosed is a script I got from someone at this forum but I got lost when it didn't work that well. I think I must of screwed it up.

            • 3. Re: Inventory database

                   Inventory management itself is not a simple concept so it shouldn't come as a complete shock that a database system to facilitate it would not necessarily be a simple thing to design.

                   Before you try scripting the bar code scan. You need to set up tables, fields, relationships so that you can do all of this manually first. When that is working for you, then you can look at automating the process with a barcode scanner. Your efforts to set this up for manually entered barcodes won't be wasted as it is a common situation that you still have to enter the codes by hand once in a while. (The label may be damaged, the scanner may go down...)

                   There are two basic approaches to managing inventory:

                   1) Log each change in inventory. As items are recieved, sold, shipped, manufactured, lost, stolen.... each change in inventory is logged much like logging depsits and withdrawels from a bank account and the "balance" then calculated tells you how many items you have on hand. It's kind like you have a spearate "account" named after each item in your ineventory.

                   2) Log the physical inventory and enter it in your database. Later, log it again. Then use the difference between the current inventory count and the previious count to compute the amount of change in your inventory.

                   Method 1 is typical for warehousing, manufacturing, Sales.

                   Method 2 is typical for situations where the business operations are too chaotic and time crtitical to log each event that consumes inventory items. This might be a restaurant kitchen or Emergency Room supply closet or....

                   For a discussion of method 1, see: Managing Inventory using a Transactions Ledger