I did somthing similar with a solution for my sons scout troop (after a lot of help here). I wanted to be able to select scouts from a portal listing all of them and have them move over to a "participated" portal.
First you need a join table, called EventPeopleJoin in the script below. The script below copies the scout(asset) from the first portal to the second portal. The first portal is an X type relationship to the scout table and the second an = type based on the eventID. In my solution I wanted the scout to be filtered out of the first portal once selected, so you may not need the refresh window [flush cached join results] step
Set Variable [ $scoutID; Value:scouts list::scoutID ]
Set Variable [ $eventID; Value:Event::EventID ]
Go to Layout [ “EventPeopleJoin” (EventPeopleJoin) ]
Set Field [ EventPeopleJoin::AttendeeID; $scoutID ]
Set Field [ EventPeopleJoin::EventID; $eventID ]
Go to Layout [ original layout ] Refresh Window
[ Flush cached join results ]
First you need relationships for your portals. You haven't provided a complete picture here so what I am about to post is based on some educated guessing about the structure of your database. Please substitute your names for mine:
Where all products is an occurrence of products
Invoices::InvoiceID = LineItems::InvoiceID
Products::ProductID = LineItems::ProductID
Invoices::anyfield x AllProducts::anyfield
To add AllProducts, you'd select Products in Manage | Database | Relationships, then click the button with two plus signs to duplicate it. Then double click it to rename it. Then you can drag from any field in Invoices to any field in AllProducts to link them in a relationship. Doubleclick the relationship line and change the operator from = to x so that all records in Products will be linked to any given record in Invoices.
Now you can add portals to LineItems and AllProducts to your layout, which should be based on Invoices.
With this structure, your button in the portal to AllProducts can then look like this:
Set variable [$InvoiceID ; Value: Invoices::invoiceID]
Set variable [$ProductID ; Value: AllProducts::ProductID]
go to layout ["LineItems" (LineItems)]
Set field [LineItems::ProductID ; $ProductID]
Set field [LineItems::InvoiceID ; $InvoiceID]
Go To layout [original layout]
Will this "occurance" of Products reflect any changes to the original? ie will AllProducts include a new record I create in Products?
Even with the cartesian relationship, the AllProducts portal does not display anything until a line item exists. What am I doing wrong?
The relationship to all products does not involve the line items table either directly or indirectly, thus I can't see any way that the presence or absence of a line item will affect it. You do need an invoice record first, but that shouldn't be a problem here as creating a new invoice should be the first step in the process anyway...
Occurrences are simply convenient labels for a given table so that you can define multiple relationships bewteen the same tables. For more on the subject: Tutorial: What are Table Occurrences?