February 15, 200520 yr I have 3 tables: ProductSales;ProductLineItems;ProductInventory. ProductSales is related to ProductLineItems via Invoice#. ProductLineItems is related to ProductInventory via ProductID. I have an order entry layout based on the Product Sales table. It has a portal based on the productLIneItems table which allows users to enter in purchased product. I have another portal on the layout that is based on the ProductInventory table. In this portal I wanted to show OnHandQuantity for the product that a user is entering. It works for the first product entered into the LineItems portal but it doesn't change when you go to a different product on another line. What am I missing that will allow me to accomplish this? Thanks for the help! John
February 15, 200520 yr Author I found one of my problems, I was only showing 1 row in the portal. I changed it to 4 and it is showing up now. But if I change or add a product, its quantity on hand will not show up until I either go to another record or go into define fields and come back out. Is the problem that a commit only happens when I do the things I mentioned and the portal will not be updated until a commit is issued? If so, I will have to write a script that just does a commit and users will have to fire the script before the onhand quantity will be updated. That's not very friendly, is there another way to force the commit without a script? Thanks! John
February 15, 200520 yr What you have is a many to many relationship. You could put the quantity on hand field into the same portal that you use for line item. Just make sure that the TO's for the 3 tables are connected in one TOG.
February 15, 200520 yr Author Ralph, Ahhh...you are correct. I was overlooking the simple & obvious answer. If I were to do the way I was headed, I would have to create a unique key in the lineitems table and relate that back to inventory, correct? (i.e. Concantenate the invoiceId & productID together). Not pretty! Thanks! John
February 15, 200520 yr In 7 you don't need to make a concantenated key, you just use the add in the relationship. But this would not help you get the quantity on hand from the inventory table.
Create an account or sign in to comment