7 Replies Latest reply on Mar 5, 2010 8:45 AM by philmodjunk

    HELP! Link barcode with amount in stock..

    draychrys23

      Title

      HELP! Link barcode with amount in stock..

      Post

      Hi I am creating an inventory database on the latest FileMaker...

       

      I would like to link the barcode with the amount in stock so I can have duplicate records with different details...

       

      So I would like say 5 records of bar code 12345 with details but the amount of stock to be the same so if one is deleted it just takes one off the amount of stock...

       

      And can I set it to delete in a certain order when I scan an item out?? 

       

      Thanks

        • 1. Re: HELP! Link barcode with amount in stock..
          philmodjunk

          Rather than delete records, I'd log them in and out in an inventory ledger where each change in stock levels is logged with a new record and a summary field computes the curent stock on hand. I'd point you to a link for  this but the new forum software upgrade has currently made the advanced search link I'd use to find the thread unavailable.

           

          Your inventory Log file would have fields something like this:

           

          TransDate

          ItemID

          AmtIn

          AmtOut

          cBal : AmtIn - AmtOut

          STotalOnHand : Summary, total of cBal, running total, restart total on ItemID

           

          To scan items into inventory, create a new record and put the number of items added in AmtIn.

          To scan items out of inventory, do the same, but the the number of items removed in AmtOut.

          • 2. Re: HELP! Link barcode with amount in stock..
            draychrys23

            Thanks for reply...

             

            so i create a new table inventory log?? and that would calculate the stock levels?? and record everything??

             

            how do i link this table with the records so when i scan one out it removes it from the records list to the inventory log...

             

            am i understanding this right sorry if i am not...

            • 3. Re: HELP! Link barcode with amount in stock..
              philmodjunk

              You may need to create a new table or you may be able to adapt an existing table for this purpose.

               

              If you have a typical set of tables for recording sales: Invoices and Line Items, you can adapt the Line Items table to serve this function. (If you have a portal to Line Items on your invoices layout, the line items entries could be populating the "out" field for each item sold, for example.)

               

              "how do i link this table with the records so when i scan one out it removes it from the records list to the inventory log..."

              What "records list" are you talking about? I'd need to know more about what you need to do and how you've set up your tables, relationships, and layouts.

              • 4. Re: HELP! Link barcode with amount in stock..
                draychrys23

                well at the moment i have set up a home page that has a button for scanning in and out stock so i type the barcode and click barcode in and it adds one to the stock level but after doing this i relised for what i need the stock database for it wont work....

                 

                Because each item has a different use by date so i made the button duplicate the record when adding an item... So this solves my use by date problem but it creates another problem it doesn't show the amount in stock anywhere so i need to link the barcode to somewhere to track how many i have in stock. and also record all the goings on (ins and outs).

                 

                I hope that makes sense...  

                • 5. Re: HELP! Link barcode with amount in stock..
                  philmodjunk

                  Set your script to create records in a table with the inventory log structure I described. Think of it as a check register where items received into inventory are "deposits" and items removed are "withdrawals".

                   

                  Logging items in would generate records with the scanned item's ID and a count of items received in the "in" field.

                   

                  Logging items out would generate records with the scanned item's ID a count of the items removed in the "out" field.

                   

                  Your scanning script can either generate one record in this log for each item scanned or combine multiple items of the same ID into a single log entry for that Sale, Shipment received, or other event that updates your inventory.

                  • 6. Re: HELP! Link barcode with amount in stock..
                    draychrys23

                    i cant work out how to make my script create a new record in a different table... i am so confused...

                     

                    sorry

                    • 7. Re: HELP! Link barcode with amount in stock..
                      philmodjunk

                      Here's a simple example that takes two items from the current record and puts them in a new record in another table:

                       

                      Set Variable [$ItemID; YourTable::BarCode ]

                      Set Variable [$Qty ; YourTable::Qty ]

                      Freeze Window

                      Go To Layout [ InventoryLog (InventoryLog)]

                      New Record/Request

                      Set Field [InventoryLog::ItemID ; $ItemID ]

                      Set Field [InventroyLog::ItemsOut ; $Qty ]

                      Go To Layout [original layout]