One way is to treat your inventory table like you would a bank account. Each time you add or remove items from inventory, you log the addition or removal just like you would deposits and withdrawals from your account.
You have a field to record all items added to inventory (debit). A field for all items removed from inventory (credit). A calculation field (debit field - credit field) and a running total type summary field to compute the ongoing "balance" (inventory on hand).
You add an extra field, such as an item number of SKU to uniquely identify each item and sort your records to group your inventory changes first by item number and then by date.
With that structure you can build an invoice where the items sold are recorded in a portal that refers to the above table and the "quantity sold field" will refer to the items removed field. Thus, each time you fill out an inventory, the items listed on the invoice are automatically removed from inventory.
Thanks Phil that helps a lot. In terms of creating a new layout for a new invoice. I'm a little confused on how to create the layout itself. For example I created a portal that displayed records from the inventory table, but it displays all the records in a list when really what I need is the ability to insert line items that are found from the inventory table, but only appear on request.
How should I create this layout and its connection, do I need a join table?
Appreciate the help.
I don't know how you've got this set up, but you should have at least three tables, but none of them are "join" tables.
Table 1: PriceList, 1 record = 1 type of item to sell. Fields would include an ItemID, ItemDescription and ItemPrice. You may have other fields, such as calculation fields that compute a total on hand count.
Table 2: InventoryLog, 1 record = 1 item change in inventory. You'll need at least a date, and InvoiceNumber, ItemID, ItemsIn, ItemsOut, cItemBal (ItemsIn -ItemsOut) and ItemsOnhand (Running Total Summary of cItemBal) and a TransactionType field.
Table 3: Invoices 1 Record = 1 sale transaction to a customer. You'll need a minimum of InvoiceNumber, and Invoice Date.
You place a portal to Inventory log on your invoice layout based on InvoiceNumber and "Allow creation of records via this relationship" enabled for this relationship. You use a relationship based on ItemNumber to link PriceList and Inventory log so that you can look up item prices.
Great help so far, it's coming together. I've created the three tables and the relevant records. The only thing I'm struggling with is getting the totals to work correctly so that the Items_On_Hand is correct, and each line item on an invoice has a subtotal, and each invoice has a total.
Items_On_Hand is a total of cItem_Balance, it's a running total and I've restarted summary for each sorted group in PriceList::Item_ID. I've tried various tweaks but basically it seems to be totaling the number of items in the whole database, rather than the total number of each item in the database.
On the Invoice layout, inside the portal to InventoryLog I've two calculation fields that should give subtotal for each item in the invoice, and the total for the invoice. The calculations are:
Extended_Price (subtotal) = (InventoryLog::Item_out * PriceList::Item_Price)
Invoice_Total = Sum(Extended_Price)
The Extended_Price definitely doesn't calculate correctly, but Invoice_Total seems to work fine.
Could you advise on how to restructure the Items_On_Hand and Extended_Price calculations?
"Items_On_Hand is a total of cItem_Balance, it's a running total and I've restarted summary for each sorted group in PriceList::Item_ID. I've tried various tweaks but basically it seems to be totaling the number of items in the whole database, rather than the total number of each item in the database."
This running total should restart for each item, if you have found all records and then sorted them by item. If the records are not correctly sorted, you'll get a running total of the entire group.
"Extended_Price (subtotal) = (InventoryLog::Item_out * PriceList::Item_Price)
Invoice_Total = Sum(Extended_Price)
The Extended_Price definitely doesn't calculate correctly, but Invoice_Total seems to work fine."
Given that Invoice_Total calculates correctly, it could be an issue with your layout not refreshing as smoothly as you need. In any case, you've got another problem. Let's say you sold 5 left handed widgets last week. This week, your supplier for this item raises their price 10% and you decide to raise your price by the same amount to maintain your profit margin. If you increase the Item_Price value in the PriceList table, all of last week's invoices that sold left handed widgets will also go up in price which is not what you want.
Instead, Define a number field, UnitPrice in InventoryLog and set its auto-enter field options to look up the Item_Price from PriceList. Since this value is copied from PriceList, changes to Item_Price will not affect existing invoices. Coincidentally, you should also then see the correct result for Extended_Price appear in your portal when you update that calculation to use UnitPrice instead of Item_Price.
Hi Phil, thanks for your patience, i'm nearly done.
For some reason the portal appears to be behaving incorrectly. I created Item_Price which in turn allows Extended_Price to display correctly for the first line item in the portal. However when I tab down to the second row to add another item the extended price is the same as the first line, and it's also displayed on the third line (and every subsequent line if I add more records).
The Invoice Total only seems to display the result of the sum of the first line item also. Am I facing a limitation in my table structure where I need a join table to make each line of the portal unique, or have I just made a mistake? I've checked the calculation fields and they appear to be correctly created.
I'm wondering if you have your calculation fields in the correct tables.
Extended_Price should be a calculation set to return a number type. It should be defined in InventoryLog and its calculation should read:
Item_out * UnitPrice
Invoice_Total should be a calculation set to return a number type. It should be defined in Invoices and its calculation should read:
Sum ( InventoryLog :: Extended_Price )
See any discrepancies here?
That worked perfectly Phil, thank you.
I seem to have a similar issue with Items_On_Hand. It's a summary total of cItem_Balance (running with restart), when sorted by InventoryLog::ItemID. It's basically counting the number of items on hand for the whole database, rather than the number of each Item_ID.
This field will report different results depending on whether or not your records are sorted by ItemID. Are they sorted by ItemID?
Another method for computing items on hand:
Define a sum function Sum ( InventoryLog :: cItem_Balance ) and place it in the PriceList table. Place this field on a list or table view based on Price List and you can see the total on hand for each item listed there. (You can even place this field in your invoice's portal so that you have current inventory counts appearing as you select items for sale.)
Perfect. Excellent support Phil.
One final question (sorry, I thought we were finished too).
I've rebuilt the DB as per your instructions and am preparing to import my records from the previous version. I'm trying to import the values in PriceList::Amount_In_Stock which was the old DB version of cItems_On_Hand except it was a number that I entered manually, not a calculation. I am not permitted to match this to cItems_On_Hand, because it's a calculation, and so imagine that the best method is to link it to InventoryLog::Item_In thus creating a new Record in InventoryLog for each item with the correct total of Items_On_Hand.
FMP doesn't let me import the records into two tables, only giving me the option to import to PriceList in the new DB. Do I need to write a script for this, or is their a smarter way to achieve it?
To import to a given table, first select a layout that refers to the table you want to receive the imported records. You should be able to import the data if you first switch to the inventory log and layout and then do the import.
Matching to the "in" field is exactly what I would do. I'd then use replace field contents to label all these as "Starting Inventory" or some such.