5 Replies Latest reply on Feb 1, 2013 7:02 AM by philmodjunk

    FileMaker Pro 12- how to relate inventory total against outgoing invoice items?

    OmCrosland

      Title

      FileMaker Pro 12- how to relate inventory total against outgoing invoice items?

      Post

           Hi,

           I am new to FileMaker Pro and I wondered how do i get the inventory unit total to be related to the invoicing items?

           1) I know you have to add relationships into there ut I'm not sure how. If someone could give me step-by-step instructions that would be much appreciated.

           2) Also how do I add a button to use a barcode add-on script? I have added it to the external data source already.

           Many thanks.

        • 1. Re: FileMaker Pro 12- how to relate inventory total against outgoing invoice items?
          philmodjunk

               1) This depends on the design of your tables and how you need to manage inventory. Inventory systems generally work of 1 or both of two approaches:

               1) You log each inventory change in a "transactions" table. If you sell X copies of Item A, a record logging the removal of X copies of item A is created in the transactions table. Other transactions might log inventory changes for product manufactured, shipped out, consumed, lost, damage or.... Your inventory total then uses a relationship matching by Item ID to sum the quantities added and subtracts the quantities removed. The FileMaker 12 starter solution uses this method and you may want to consult this thread on the subject:

          http://forums.filemaker.com/posts/445739f103?start=1&stop=10#211358

               2) You count the items on hand and the updated count now becomes your inventory totals for each item. Modified versions of this do a "cycle count" where a different portion of the total inventory is counted and updated on a regular basis. Essentially, you start with a count can compute the amount of change to your inventory.

               Barcode scanning depends on what you will use for your scanner. If you will be using an iPhone or iPad to scan codes, please ask about this in the FM Go Forum. If you are connecting your scanner to a computer with a USB conection, see this thread: Need help writing script to integrate barcode scanning

          • 2. Re: FileMaker Pro 12- how to relate inventory total against outgoing invoice items?
            OmCrosland

                 Many thanks for your quick response.

                 I am using both starter solutions for the Invoices and Inventory. The relationships seem to be linked in already using these soultions, however the Invoices don't seem to be relational to the Inventory.

                 1) how would I create a 'transactions' table? Also would the 'item id' be matched from the Inventory or Invoice table? I had a read of the thread that you posted, but it seemed to describe how to not have negative data in the quantites section, rather than how to create and manage inventory to invoicing.

                 2) I have got a dedicated desktop hand-held scanner that I would like to be able to incorporate the barcodes to relate to certain product items so once it goes into an invoice it is searchable for tracking useage.

            • 3. Re: FileMaker Pro 12- how to relate inventory total against outgoing invoice items?
              philmodjunk

                   Apologies on that thread. It is not the one I thought it was. I'm still searching for the thread I used to recommend for this as how to set this up is a frequent question in the forum. But the Inventory starter solution's Transactions table is a pretty decent example of how you would set this up.

                   Every starter solution is independent of one another. I suggest taking concepts from the Inventory starter solution and modifying the Invoices solution (or your own design of an Invoices solution) to use those same concepts. The Invoice Data table in Invoices can have fields added such that it functions as your transactions table. The field where you record the quantity sold for a given item, then becomes the "items out" field in the transactions table.

                   You'd then need to add at least one more layout for using Invoice Data to log other inventory changes--such as receiving more product from a vendor.

                   2) Assuming that your hand held scanner works, like nearly all do, in keyboard emulation mode, the thread I shared for working with barcode scanners should give you some good ideas on how to script this. There will be technical details on how to configure your scanner to add characters to the scanned data in order to "trip" script triggers that will need to come from the scanner manufacturer.

              • 4. Re: FileMaker Pro 12- how to relate inventory total against outgoing invoice items?
                OmCrosland

                     Would you be able to tell me step-by-step how to do the instructions for question 1 as I've never used FileMaker before or have prior knowledge of scripting etc.

                     I am using the Starter Solutions for both Inventory and Invoices and tried to link a relationship to the Inventory Transaction to Invoice date, item, item description but it is still not showing it in the Transactions table. You said the starter templates were independant, so would it be complicated to link the two up so that when I raise an invoice, the inventory stock goes down accordingly?

                     The technical telephone number, is that any good for this kind of issue or is using a forum better as I need guidance on using FileMaker 12.

                     Screen Shot : shows relationship link that I have tried to set up

                • 5. Re: FileMaker Pro 12- how to relate inventory total against outgoing invoice items?
                  philmodjunk

                       I recomend that you NOT try to link the two starter solutions. Such can be done, but not by someone as new to FileMaker as you.

                       I have suggested that you modify the Invoice Data table in Invoices so that it also functions as your transactions table.

                       The Products table in Invoices and the Inventory table in Inventory are two nearly identical tables. One such table, Products, can serve for both. Note how the relationship between Invoice Data and Products matches by just one pair of fields. That's all you need. The other fields should not be so linked as match fields.

                       Here are the fields you need to add to Inventory Data:

                       QtyReceived (number)

                       cBal (Calculation: QtyReceived - Qty )

                       sRunningBalance ( Summary : Total of cBal, Running Restart totals when grouped by Item)

                       sBalance (Summary: Total of cBal)

                       TranType (Text)

                       TranDate ( Date: Auto-enter the creation date )

                       Adding these fields will not alter the function of your existing layouts, but you will then be able to add a layout that functions like a bookkeeping ledger for tracking all inventory changes for all items. Changes due to produce sold will automatically appear on such a layout, but you would use this layout or a similar one to log all other types of inventory changes.

                       See if you can get this far with modifying a copy of the Invoices starter solution. (Always make lots of back up copies, it avoids having to start all over fro square one if something doesn't go right.)