I have a table with data called InvoiceShipments. It continues a row for each product shipped on an invoice. Each product belongs to a product category, which I can query and filter by. Some of the products are finished good products with a Bill of Material, where the Bill of Materials (BOM) is a list of the parts that combine to make the finished good.

In the InvoiceShipments table, the finished good is listed with a price but no cost. It is then followed by the components (BOM) of that finished good, which in turn have a cost but no price. I have a separate table that lists all of the component items and which finished goods it goes to. Note that component goods can belong to more than one BOM.

InvoiceShipments Sample Data:

 

I can currently filter the InvoiceShipments by the products that I want based on the product category (from a join to a different table). What I want to do is grab that finished good number, and get a list of all the part #s that make up that BOM, then go back to the InvoiceShipments and sum the costs for all of the rows that match those component #s and invoice#. But I haven't been using Crystal long enough to know what to do at the query level, what to do with a command table, what to do with a formula, etc.

 

My desired outcome would look like:

 

Any help you can offer would be greatly appreciated.