In that "history" portal, do you want to see a list of all invoices--one line to an invoice or a list of all products purchased--each item on it's own line? Or do you want to see each product listed only once in that portal.
The first option does not require any new table occurrences. You can just put a portal to invoices on a layout based on customers. I doubt that's what you want.
If you want an "item list", you need Customers---<Invoices---<Invoice Data>-----Products and you'd put a portal to invoice data on your layout. Again, no new table occurrences need.
If you want to list each product 1, make it a portal to Products.
If you then want some kind of total quantity listed with each product, We'll need to do some creative relationship work or use ExecuteSQL to get the needed totals for each product for the current customer.
Thanks for the advice.
Ok, great. The second option of a list of all products purchased (specific to that customer) is what I am trying to achieve. However the same record needs to be accessed in the Products Layout as well as the portal in the "purchase histories" tab. When I create a portal to invoice data would I use the fields from my Products Table?
Both the second and third options list all products purchased specific to customer.
Product | Qty
Widget 1 5
Widget 2 3
Widget 1 2
If the same item is purchased more than once, option 2 lists each as a different row. Option 3 lists each product only once. Including a Qty of 7 for widget 1 and 3 for Widget 2 could be done, but it would take more to do than I've described thus far.
And if you do this from products, this would list all purchases of that item for all customers. Is that what you would want? In this case, you would again use a portal to Invoice Data. But check your relationship graph as you might need to add an occurrence to invoice data depending one which occurrence of Products is the basis for your layout. Many developers use an approach called "anchor buoy" where each layout's table occurrence is the "anchor" to a set of table occurrences linked to it for just the use of that layout's context.