5 Replies Latest reply on Mar 11, 2014 8:26 AM by philmodjunk

    Using the Invoice Starter Solution to maintain inventory

    ClaireCollin

      Title

      Using the Invoice Starter Solution to maintain inventory

      Post

           I am having difficulty figuring out how to get the invoicing function to update the product amount in stock.  The Update Inventory button is not working and apparently I am not the only one with this issue.

           I read this post, http://forums.filemaker.com/posts/a6ef951917, and found it moderately helpful.  I am still unsure as to how to make the invoice line items populate the transaction ledger so that it updates the running balance. Can you provide more details on this part of the process?

        • 1. Re: Using the Invoice Starter Solution to maintain inventory
          philmodjunk
               

                     I am still unsure as to how to make the invoice line items populate the transaction ledger

               Well the transactions ledger IS also the LineItems table. It's one and the same table used for different purposes on different layouts. The QtyOut Field in the transactions ledger becomes exactly the same field as the Qty field in the starter solution's LineItems table where you specify the number of items sold.

          • 2. Re: Using the Invoice Starter Solution to maintain inventory
            ClaireCollin

                 I was thinking about it differently and got this to work.

                 However, now the conditional formatting for the Qty field on the Inventory layout does not work.  It is always on even if there is plenty of inventory.  Is there a way to conditionally format based on the current inventory (sBalanceRunning) being less than or equal to 0?  I tried the simple equation Line Items::sBalanceRunning≤ 1 but of course that doesn't work either.  Do you have any tips?

            • 3. Re: Using the Invoice Starter Solution to maintain inventory
              philmodjunk

                   The way that the invoices starter solution manages inventory and the method used with the inventory ledger method in my thread are quite different. I would need to know quite a bit about your current set up before I could answer that question.

                   A running total summary field may not return the value that you need for this. You may need to make a copy of that summary field and disable the running total option so that you can use this non-running total summary field to show the current amount on hand for a given product.

              • 4. Re: Using the Invoice Starter Solution to maintain inventory
                ClaireCollin

                     I have a Transactions table with the following fields and relationships.

                     Invoice ID   Product ID   QtyIN   QtyOUT   cBalance   sBalanceRunning

                     Invoices::Invoice ID----<Transactions::Invoice ID

                     These fields are populated by the Line Items on the Invoice layout. So on the Invoice layout the QTY is the QtyOUT field from the Transactions table.  I want to Conditionally Format this field on the Invoice layout to change color when the sBalanceRunning is less than or equal to 0.  I have tried a number of things and either they all change color or none change color.

                     I can't create a duplicate of sBalanceRunning without the running totals because then it totals all of the records. I need to know when a specific product has a low balance and needs to be replenished before the invoice can be fulfilled.  I would like to see this on the invoice layout instead of having to look at a separate report.

                • 5. Re: Using the Invoice Starter Solution to maintain inventory
                  philmodjunk

                       If you put sBalanceRunning in the portal row, what value does it show? Does it show the correct value? I don't think that it will and that's why your conditional format is not working but please check to be sure.

                       A non running total summary field can show the total for just one product, but it will take a new table occurrence in your relationship graph:

                       LineItems::_fkProductID = LineItems 2::_fkProductID

                       If you put the nonrunning total summary field from LineITems 2 in your LIneItems portal row, it will show the total for just the product selected in that portal row.