Another Table Occurrence with a "Not Equal" relationship?
When i tried to do that with a second TO of both the products and the product line times tied to the invoice table I can get the new portal to show every line item that is not on that particular invoice.
But I'm looking for a list of products that are not on an invoice, not a list of the line items no on an invoice.
Here is a rephrasing of the question to maybe spark another method:
If I place a portal to the Products table on the layout based on the employees table (obviously there is that line items join table in the middle), I get a list of every product that customer has bought. But can I place a portal on the layout based on the Customer table to show all products that customer has NOT bought?
Weather I show the products no purchased on either the invoice layout or the customer layout doesn't matter to me in particular. Truth be told. I think whatever method I use for one will be transferable to the other.
I am assuming these tables, fields and relationships:
Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
Products::__pkProductID = LineItems::_fkProductID
In Invoices, you can define this calculation field, cProductList with Text as the result type
List ( LineItems::_fkProductID )
Then you can add a new Tutorial: What are Table Occurrences? of Products linked to Invoices like this:
Invoices::cProductList ≠ Products|NotSelected::__pkProductID
A Portal to Products|NotSelected placed on the Invoices layout will then list all products not selected for the current invoice. This relationship can also be used as the basis for a conditional value list called a dwindling value list.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Of course your solution worked perfectly! Thanks so much PhilModJunk!