Making a field in a portal match two criteria across two join tables

[geos]

Anyone have a suggestion for this:

Have three main tables:

Inventory (products)

Invoices

Customers

The Invoices layout contains a portal that pulls from a join table between the invoice and the inventory to pull things like product name and price over.

The Invoice has a direct relation to the customer database to pull the usual name, address, etc over.

The Customer database has a portal for multiple lines for a quoted price for random products (Customer 1 might only have quotes for Product A, B and C, while Customer two might only have quotes for Produts B, C and F), that is also established by a join table.

Now the issue I am having is on the Invoice, on the same line item, I want to pull the name, description and price from the Inventory (no prob here), *and* pull the quote that matches both the Customer and the Inventory_ID from the Quoting join table.

Right now, all it is pulling is the fist occurrence of a quote for the given Customer, not the one that is related to the product.

I have attached a quick mock up of what I think I am doing.

Any suggestions greatly appreciated.

Thanks!

[comment]

First, you need to establish a relationship between the two join tables, based on CustomerID AND ProductID. For this, you will need to add an unstored calculation to the invoice's line items table =

Invoice::CustomerID

Then, define the price to auto-enter a calculated value (with replacing) =

Lookup ( Quoted::Price ; Inventory::Price )

--

Note that for this to work, the invoice must be committed before selecting a product in the line items portal.

[geos]

Just to be clear: I want *both* the price from inventory and the quoted price side by side (this copy of the invoice is just seen by the sales people) to confirm the amounts.

Created a new filed in the invoice line items for the calculation that is the customer ID from the invoice. And then a field on the invoice to lookup from the Quote line item.

But still any pull from the Quote line items join table pulls up the first product quote for a client ID, not the correct quote for the specific product for the specific client.

What I am missing?

[comment]

Have you created the additional relationship? You need to put a new occurrence of the quotes join table on the graph, and link it to line items using BOTH ProductID and CustomerID. Then make sure you are looking up the quoted price from this new occurrence.

[geos]

Thanks!

Wnet back and double-checked the field, and it was mis-set-up from prior messing. Got it!