Did you set up an auto-enter field option on the _fkProductID field in LineItems to look up the product ID from the look up table occurrence shown below the LIne Items table occurrence?
Did you mean the image shown below? If so, I did.
If you are going to use the Item name to look up the product ID as suggested by the relationship linking Invoice Line Items to Products | Looked up, the above dialog would refer to a field from Products | Looked Up, not Products.
Once you are getting the ID correctly looked up, we can see if you have the correct auto enter settings to look up the unit price and then we get to see if there might be a "timing issue" as each line item record has to look up the product ID before it can use product ID to look up a unit price.
After changing Product to Product | Looked Up, it did look up the unit price from the Product table. Thank you very much~~
I also have a question regarding to the unit price in the Invoice Line Item. Currently, the unit price is lookup from the unit price in Products (see image below); but if I change the related table from Products to Product | Looked Up, it will still look up the unit price. So I'm wondering if there is any downside when looked up the unit price from Product | Looked Up instead of Products? The "invoice" template from Starter Solution is using Products to look up the unit price instead of Product | Looked Up.
Moreover, what should I do to make the QTY in Invoice Line Item to affect the Stock Availability in Products? So when I type a number in the QTY in Invoice Line Item, the Stock Availability in Products will reduce; and when I delete the related records from the portal in the Invoice, the Stock Availability will be added back.
I spend some time looking into the "Invoice" template from Starter Solution and made some adjustments to see if I could come out a solution for my stock availability to be varied based on the QTY from both orders & invoices that I issued.
However, I failed to do it. Below image is the new tables and their relationship that I created for your reference.
And for the calculation formula for the stock availability from the Products table, here is what I typed:
If ( Sum (OrderLineItem::QTY) - Sum (InvoiceLineItem::QTY) = 0 ; 0 ;
Sum (OrderLineItem::QTY) - Sum (InvoiceLineItem::QTY) )
Can anyone please help me to find the error?
Let's tackle one thing at a time. I don't know if you've seen it, but I have a demo file of what I call "enhanced value selection" techniques. It includes a working example of using an auto-complete enabled value list of names to link records by ID. I'll include a download link for it at the bottom of this comment.
The main problems to look out for when using a product's name rather than an ID in a relationship is that a product's name may not be unique and might change at some point in the future. The best design approach is to use such "name" based matching cautiously. The trick is to base all of your relationships on the ID except for those specifically added to facilitate a more user friendly interface. You can use scripts to handle situations where the entered name does not match to any product or matches to more than one product.But always make sure to keep your records correctly linked by ID.
So using the name to look up a price is probably OK, but not, say to manage inventory. Please note that each version of FileMaker has it's own and different set of starter solutions. All of them are horribly lacking in documentation to tell you how they were put together, how to use them or how they work.
Here's a thread on how I'd manage inventory: Managing Inventory using a Transactions Ledger
And the demo file: "Adventures in FileMaking #2 - Enhanced Value Selection"