Would someone be able to help me with the following please. It would be greatly appreciated.
I have created the following tables for a many to many relationship with a join table.
Client - Client ID
Products - Product ID
Line Items - CID, PID
Background story: A company is selling about 50 different product types. Some clients are buying 5 types and some are buying 20 types. So in the Clients table, I am assigning which product types each client has committed to buying (as I will also need this later for an Invoices table). I managed to assign Products to each Client via
- a portal of Constant relationship between Products and Clients, so I could see all products in Clients table for selection
- and then populating a second portal in Clients table, which in turn creates new records in the Line Items table.
Step two - In the Products table, I need to show a portal of Client Names that have committed to buying each product. The information resides in the Line Items, but I don't know how to format / script the portal into Products to show it. I tried Product ID to PID, but the portal fields available show from the Products table, not the Line Items table. Obviously my understanding of the portal relationships is not that advanced. I realized that a portal with that relationship probably doesn't make sense either because it gives me no way of connecting to CID. I tried with a Constant relationship between Products and Line Items, just so I could see the line item records, but again the fields available are only from the Products table, not Line Items.
Even though the records are broken down in the Line Items table, the Products table needs to show is a portal with a condensed list of clients, i.e. so it doesn’t show every broken down record that’s in the Line Items, but rather roll them up by client ID, so all I see is a client’s name only once.
I thought about a direct relationship between Products and Clients, but there is nothing that connects the two directly.
I tried playing with filtering and sorting as well, which is no good when the portal fields are only available from the Products table.
If it is a reasonable request, would anyone know how to make this happen?