Here's thread that expands on the concepts used in this starter solution to include invoicing that automatically updates inventory each time you add/change/remove a line item from an invoice: Managing Inventory using a Transactions Ledger
I made the changes according to that link and my products still are not being adjusted nor are any transactions being listed on the Transactions Ledger. I have a sample file with 5 products loaded if anyone wishes (or has the time) to look at at.
If you made the changes specified, the stock levels will update automatically. You can upload your file to a sharing site such as Drop Box, but you can use the controls found below Post a new Answer to upload images--such as what you have set up in Manage | Database | Relationships or your layout while it is in layout mode and that may also be enough info to spot why this is not working
Attached is a copy of my relationships. I have the file uploaded in dropbox and can share with you if you send me an email address for the invite. Thanks.
That does reveal the issue.
Invoice Data and Inventory Ledger should be one and the same table. You may want to read the thread on this again and note how it starts with a "lineitems" table--named Invoice Data in this starter solution--and modifies it to be used as both an inventory "transactions" table and also as the line items table for invoices. The QtyOut field in Invoice Ledger and the Qty field in Invoice Data, for example, should be one and the same field.
Any inventory transactions--such as receiving a shipment of more product, that are not part of a sale are logged in this combined table, but the inventory ID match field is left empty as such a transaction is not linked to an invoice as it is not part of a "sale" transaction.
And you don't need both a __pkInventoryID and an Invoice ID in your Invoice table. These are two differently named fields with the same purpose, to uniquely identify each record in the invoice table. And thus you can also remove one of the corresponding match fields in Invoice Data for the same reason.
The pairs of Item and ProductID fields linking products to Invoice Data are also redundant. You only need one of these two match field pairs there as well.
I have tried to do this over and over and still cannot get this to work. I believe I am doing everything according to the post but I obviously am missing something.
Can you upload a new screen shot of your relationships graph showing the changes that you have made?
"Can't get this to work" is pretty vague. Exactly HOW is it not working? (That can be a useful clue.)
My apologies for the late response. I actually created a whole new table and started over from the beginning and got it to work. Now I have a different issue. Is it possible to create a new record in a table from information entered in another table? I created a purchase order invoice (table) for new products that I receive from my distributor so that I can track my outbound expenses. I want this table to automatically create a new record in my products table for each product listed on the purchase order. What would be the method for doing this?
Scripts can easily loop through your line items records and create matching records in products. But unless you need to track each item you sell as a completely different item, this shouldn't be necessary.
(Example: if you are selling cars, every car is a specific record in inventory. If you are selling screw drivers, you probably have just one record for each type of screw driver...)
A sample looping script that creates one new record in Products for every item on the current PO's LineItems:
IF [ Not IsEmpty ( LineItems::_fkPO_ID ) // make sure that line items exist for this PO]
Go To Related Record [Show only related records; From table: LineItems; Using layout: "LineItems" (LineItems) ]
Go to Record/Request/Page [First]
Set Variable [$Var ; LineItems::SomeField ]
#put as many set variable steps to set different variables as you need to copy data from Line Items to products here
Go to Layout ["Products" (Products)]
Set Field [Products::someField ; $Var ]
#one such set field step for every set variable step
Go to Layout [layout: "LineItems" (LineItems) ]
Go to Record/Request/Page [Next ; exit after last]
I attempted to follow your example but I am not receiving the desired result. I wasn't sure where I was supposed to use the script so I created a button on the Purchase Order layout to run the script but of course that did not work. I'm uploading screenshots of my tables, layouts, relationship, and script so that maybe you will spot the mistakes I've made. I don't know how to load multiple images with this post so I will do them as separate posts.