5 Replies Latest reply on Oct 31, 2011 10:26 AM by philmodjunk

    Write script for invoicing starter solution to tie 4 locations together

    LisaRadosta

      Title

      Write script for invoicing starter solution to tie 4 locations together

      Post

      Hello,

      I am using the invoicing starter solution. We have inventory at 4 locations.  Each location has its own field. The values from those fields are summed to make QOH. 

      In invoicing, I would like for the user to be able to put a letter or word into the invoicing field (location) which would tell the field to subtract the amount on that line under quantity from that item's location (in the products database) so that the correct location would automatically be adjusted for that sale. Ideally, it would be one letter, but if not, we can use the word associated with the location.

       

       

       

      I have tried to write the script myself and am having some trouble. Before I mess things up completely, I would like a little advice about how to go about it. 

       

      Thanks

      Lisa

       

       

       

        • 1. Re: Write script for invoicing starter solution to tie 4 locations together
          philmodjunk

          QOH means what exactly?

          Why do you have 4 different fields for location? Wouldn't it be simpler to have one location field with a value list instead?

          I've looked over the "inventory management" features of the invoicing starter solution in the past. It's approach is not the best option. As designed, it's very easy for user errors to mess up your inventory counts.

          • 2. Re: Write script for invoicing starter solution to tie 4 locations together
            LisaRadosta

            Hi,

            Thank you for your quick response. 

            QOH=quantity on hand. 

            We have 5 locations total (I put 4, but that is incorrect). I have to be able to see which products and in which amounts are in each location at any given time. That is why we have 5 fields in the Products database. The problem with a value field (I may be wrong, of course) is that it doesn't allow me to scan an individual product at any given time (usually with the client waiting) and see where we have that particular item. It works really well in Products, but I can't figure out how to make it work for invoicing. 

            We could do a value list in invoicing and then a person would have to go back each day and look at all the invoices and enter manually where the products were located which were sold. This is certainly doable, but I was hoping that it would be automatic so that when someone hit a W on the invoice, it would subtract the quantity sold from the WPB location in the Products database. 

            I could certainly start over with my own database instead of the starter solution, however I am so new to the program that I was really concerned about messing something up on my first try. I am brand new at this and am open to any suggestions that you have. 

            Anyway, I want the user to select a letter or a word on a drop down menu on the invoice and for the quantity being sold to be deducted from the appropriate location on the Products database. Is this possible?

            Thanks again, 

            Lisa

            • 3. Re: Write script for invoicing starter solution to tie 4 locations together
              philmodjunk

              Not quite what I had in mind. In the line items portal of Invoice, you can use one field to designate the location from which the item will be pulled to fulfill this sale. This can be specified at Point of Sale or later when someone actually pulls the product.

              I wouldn't use any field for location in Products, but then I wouldn't use the starter solution for managing inventory due to its limitations.Wink

              I'd use the line items table to manage inventory tables by adding sufficient fields to the line items table so that it functions like a check register to track inventory levels. Sales and other transactions that remove items from inventory are treated like withdrawals. Shipments received, product manufactured etc. type actions that increase inventory are treated like deposits. Calculation and summary fields can then compute total on hand and totals on hand in each location for each item in inventory. Not only can this tell you how much you have on hand, but you can see levels change over time to see how close to zero any item gets and how often which can help you manage re-order levels to better manage your inventory.

              While your invoices layout would still function to remove items from inventory due to sales. Other layouts would be used with this same table to see the full ledger and to update inventory due to non-sales transactions such as receiveing a new shipment of product.

              • 4. Re: Write script for invoicing starter solution to tie 4 locations together
                LisaRadosta

                It has been a while, but I am slow! I did exactly what you said and scrapped the starter solution and started from scratch. I can't believe it, but it is worlds easier to start your own database than to use a starter solution. Anyway, I still have the problem as to how to complete the calculation.

                Again I have 3 databases in which items will appear: inventory and iine items. The inventory database has a field for each location so that we can easily see how much of each item is in each location (we have 5). We have corresponding field for each location in the line items database. 

                In the inventory form view, there is a portal which is where the inventory items are inputted by the user to make an invoice. There is a field which corresponds the location in this portal. 

                So, I want people to be able to put in a B for (BIN location) and have the program subtract the quantity in the QTY field in the invoicing database from the WPB field in the inventory database so that we don't have to manually do it. 

                I created a calculation field in the invoicing form view called Line items:location inventory outcome which is defined as: If (Item Location = Bin ; Inventory::BIN - QTY). 

                Well, that is what i did and it doesn't work. I am sure that I am way off. Any suggestions would be appreciated. 

                Thank you!!

                • 5. Re: Write script for invoicing starter solution to tie 4 locations together
                  philmodjunk

                  Again I have 3 databases in which items will appear: inventory and iine items.

                  I'd call that one database with three tables: Invoices, Inventory and Line Items. You may or may not have these tables in the same file, but you still have three tables and they are linked into one database system. Wink

                  If you search for "Inventory Ledger" here in this forum, you'll find a number of threads where I have already spelled this out for others. You may want to research a few of those.

                  You start with this relationship:

                  Invoices----<LineItems>----Products

                  This is the same basic structure found in the starter solution and also in this excellent, simple demo created by Commment: http://fmforums.com/forum/showpost.php?post/309136/

                  What I am suggesting is that you add additional fields to LineItems so that you have this:

                  TransDate, ProductID, LocationID, TransDescription, QtyIn, QtyOut, cBal, sBalance

                  Transdescription is where you describe the transaction, "Sales", "Recieved", "Damaged", "Return to Vendor", etc.

                  Number of items removed from inventory or logged in QtyOut, Items added to inventory are logged in QtyIn. cBal in a simple calculation field to combine these two with the proper positive or negative sign: QtyIn - QtyOut. sBalance would be a summary field that computes the total of cBal, specified as a running total, restart summary for each sorted group, when sorted by location ID.

                  You can then set up a list or table view of your data with these same fields (except cBal need not be visible) and you can sort your records first by ProductID, then by LocationID to show your current inventory levels for each product, broken down by location.