I'm not sure if your data model matches what I am about to post or not:
Invoices----<LineItems (InvoiceData in starter solutions)>------Products-----<ComponentList>------Products|Component
Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
Products::__pkProductID = LineItems::_fkInvoiceID
Products::__pkProductID = ComponentList::_fkKitID
Products|Component::__pkProductID = ComponentList::_fkComponentID
Important detail: Products and Products|Component are two table occurrences with the same data source table.
Now "packing list" can be the LineItems table here or a different table also linked by InvoiceID just like LineItems. (You might use line items to document the sales transaction and Packing slip to document the packing requirements.)
But the basic process is the same. In the portal to LineItems located on your Invoices ID, a script trigger on the portal row or a field in the portal row performs a script that:
a) checks for the existence of related records in ComponentList for the selected product. If [ Not IsEmpty ( ComponentList::_fkKitID ) ]. If there are related records there, this is a kit and the next part of the process takes place. If not, this ends the script:
b) A variable is set to the List of ComponentIDs from the records related to this product: Set Variable [$CompList ; value: List ( ComponentList::_fkComponentID ) ].
c) The current __pkInvoiceID is set to a variable.
d) Another variable is set to the list of Quantities specified for the kit in Component list.
e) A fourth variable is set to the quantity specified in LineItems for this kit.
f) the script changes to a layout based on Packing List and creates one new record for each value in $CompList, multiplying the specified kit quantity from the list of componentList quantities by the lineitem quantity so specify a total quantity for that component.
g) after exiting the loop, return to the original layout so that a user can add another item. (Or you can set up a looping script that loops through all the line item records to do the above after the invoice has been completed.
Now the result isn't exactly what you specified. If two kits specify the same component, you have two entries in your packing list table, one from each kit. If you use a list view layout with sub summary layout parts and some summary fields to subtotal the quantities, you can produce exactly the merged list that you specify.
Alternatively, you could modify the above approach to first find and search all existing packing list records for a record with the same componentID so as to merge in the next kit's quantities into one record, creating a new record if one is not found, but that's about the same amount of work and will slow down the execution of your script as it will have to do a lot more with each listed line item.
Thank you PhilModjunk. That make sense. Not sure I can use List function that way. I will try that. My data model is exactly like what you mentioned. Thank you very much. I also would prefer to have multiple component records from different kit so when it prints. So no need to summarize. Really appreciate your help.
Hi Phil. How do I refere to the individual value in the list $CompList?
Hi Phil. Please don't worry. I figured it out.