3 Replies Latest reply on May 19, 2010 10:59 AM by philmodjunk

    Setting up a basic inventory

    info@fernpub.ca

      Title

      Setting up a basic inventory

      Post

      Hi. I'm fairly new to FMP and just got FMP 11. I'm trying to coordinate an inventory db I've created with a pre-existing invoice db. Basically, I can't figure out how to link the two files, and more importantly how to make the items sold in the invoices be taken out of the inventory file. I'm sure it's super basic, but I haven't been able to find instructions on how to do this anywhere, so any advice will be greatly appreciated.

        • 1. Re: Setting up a basic inventory
          philmodjunk

          What structure have you set up in your inventory file? One record for each product?

           

          Instead, you can set up your inventory file like an accounting register with a field for adding items to inventory and a field for removing them. Each record in this table represents a change in inventory (items received, sold, lost, destroyed, etc.). Summary fields can then compute the amount on hand for you.

           

          WIth this structure you can use the inventory table as your line items table that your invoice links to and uses a portal to edit. Then completing an invoice automatically deducts the items sold from your inventory.

           

          Search this forum for threads with "inventory log" and you'll find several that discus this approach.

          • 2. Re: Setting up a basic inventory
            info@fernpub.ca

            The inventory file is one record = one product. I just used the inventory template that comes with FMP and edited the layout accordingly.

             

            I'm a bit confused about what the inventory log table will look like, what fields to include etc... 

             

            Can I still use the inventory db I have created, or do I need to make a new one from scratch?

             

            Thanks for your help! Sorry for being pretty clueless, I'm learning as I go.

            • 3. Re: Setting up a basic inventory
              philmodjunk

              I find the inventory template that comes with filemaker way too limiting. WIth an inventory log table, you can not only see what you have on hand, but also how inventory levels are changing over time, when was the last time you recieved a new supply, etc.

               

              An inventory Log table works like a check register where new product recieved represents a "deposit" and items removed from inventory (Sold, lost, shrinkage, etc.) are "checks". A summary field that computes a running balance is used to show items on hand.

               

              Typical fields in such a table:

              TransactionDate

              ItemID

              Description

              Qty Recvd

              Qty Removed

              cBalance (Qty Recvd - Qty Removed )

              sBalance (Summary, running total of cBalance, restart totals when grouped by ITemID)

               

              If you use this with an invoice that uses a portal to link directly to this table, you'd add at least these fields to this table:

              InvoiceID

              Unit Price

              Cost (Unit Price * Qty Removed )

               

              Other fields may be needed depending on what you need to see in your invoice.