Summarize groupings of related records at line item level

[M Bario]

Hello -

I have a report called "System Cost and Pricing" in which I set out to detail components making up various computer systems. Each system contains specifically detailed components such as graphics cards, internal hard drives, controller cards, etc...

I have the following tables:

Equipment - Lists general details for each type of equipment we keep in inventory. An example would be Hitachi 2TB Internal SAS Hard Drive, replacement cost for this type of drive, and what that drive would rent to customers for on a daily or weekly basis (we rent to customers)

Inventory - Specific details for each piece of equipment we have in inventory. Serial number, internally assigned barcode, purchase date, purchase price, etc... Each inventory record contains a key field related to a type of equipment. ie - barcode #1234 relates to the equipment record for Hitachi 2TB Internal SAS Hard Drive

Systems - We have systems built in common configurations and ready for rent. Each system is comprised of specific items from inventory, such as a computer (ie HP Z800), graphics cards, multiple internal hard drives, power supply, etc. Each record in the Inventory table contains a key field where I place the appropriate System ID when that item is made part of a system.

The above structure allows me to:

• Create an Equipment layout with an Inventory portal listing all the items of that type we have in inventory.

• Create a Systems layout with an Inventory portal listing specific inventory items comprising each system

So far so good, but I need one more layout - which is a System layout with a portal showing one line item and an associated quantity for each type of equipment we have in a System. Example - Line item #3 in the portal would note: Hitachi 2TB Internal SAS Hard Drive, Qty of 4, weekly rate per drive of $10, total weekly rate of $40 for that line item.

I created a calculation field in my Systems table with the expression "Count ( Equipment::Equipment_ID )". Unfortunately this only tells me the total number of related equipment records for that system. I need to know how many of each type of equipment are in the system so I can extend pricing, replacement costs, etc out by quantity.

Any advice or pointing toward related posts would be appreciated.

Thank You!

[comment]

If this is for a report, you could find the inventory items that are components of a system, and summarize them by system and by equipment. Summarizing in a portal is more difficult.

[M Bario]

If this is for a report, you could find the inventory items that are components of a system, and summarize them by system and by equipment. Summarizing in a portal is more difficult.

I have been thinking along these lines, but I'm not exactly sure how to implement. So in high level terms I would need to:

Perform a find for each type of equipment in a system (usually 8 - 12 of them)

Store the found record count in each instance somewhere - as a parameter I assume

Multiply each "FoundRecords" quantity by the weekly or daily price for that equipment item and store each result somewhere

Add the totals for all extended weekly costs and store that result as a total weekly or daily cost for the system

Lay all of it out in a report

As I type it out about I begin to develop ideas on how to implement, although given a fair amount of trial and error. Should I consider a new "system report" table where I can store all this data collected by the script commands? Or am I off in left field regarding the execution?

Thanks for your response. Really appreciate it.

[comment]

I think it can be much simpler. Assuming an inventory item is assigned to a system by entering the system's ID into a SystemID field in the Inventory table (if this is assumption is wrong, then it should be right ):

1. Find all inventory items that have anything in the SystemID field;

2. Sort them by SystemID and by EquipmentID;

3. Go to the report layout. This layout should have two sub-summary parts (by SystemID and by EquipmentID) and no body part.