Need help with a portal based on a portal

[laker_42]

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

[laker_42]

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

[RalphL]

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.

[laker_42]

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

[RalphL]

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.