3 Replies Latest reply on Jun 2, 2011 10:26 AM by philmodjunk

    Invoice and Inventory Auto update



      Invoice and Inventory Auto update


      I'm using the inventory and and Invoicing solution in file maker pro 11. My problem is that when i make a new invoice the inventory is not updating automatically so that even if i am allowing someone only to have read only rights i still can't keep track of how many procduct i have instock because it is not updating. Is there a way to have the products update automatically? thanks

        • 1. Re: Invoice and Inventory Auto update

          The invoicing starter solution has a number of significant flaws when it comes to managing inventory levels. There's code there that's supposed to update the inventory but it's very vulnerable to user errors messing up the inventory counts.

          Another approach is to use your LineItems table as an "inventory log" where sales reduce your inventory totals and shipments recieved increase them. This approach can also be used to handle inventory shipments and customer returns that return product to inventory.

          What you do, is add some fields to LineItems that turns it into a "ledger" where one field is used to remove items from inventory and different field is used to add them. A running total of the difference of these two fields tells you your current inventory for each item in stock.

          Your table would need at least these fields to function as an inventory log (Keep the current fields so that it still works for logging item sales):

          TransDate : Date (Can look up a date from Invoices for sales entries)
          InvoiceID : Number (blank for non sales entries)
          ProductID : Number
          TransType : text ( sales, shipment, return, shrinkage, etc.)
          QtyIn        : Number
          QtyOut       : Number (this is the item quantity field already used in your portal on the invoices layout)
          cBal          : QtyIn - QtyOut
          sTotal      : Summary, Total of cBal, Restart totals when grouped by ProductID

          With this structure, you can create a list layout based on LineItems. If your sort your records by ProductID, then TransDate, you'll not only see the inventory levels for each Product, you'll be able to see how they've changed over time. You'd log non sales inventory changes directly on this layout such as logging in the receipt of a new shipment of products.

          • 2. Re: Invoice and Inventory Auto update

            Ok i am very very new to file maker pro. You solution sounds great but unfortunately i fully understand how to go about doing this. Do you have a screenshot by chance. Sry for bein such a noob

            • 3. Re: Invoice and Inventory Auto update

              Sorry, but I don't have a screen shot, nor a demo file of this particular method...