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.
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?
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'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.
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.
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.
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:
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.