Portal showing sub-summaries

[trvr]

I wish to include on a form view layout a portal showing sub-summary totals of related records from a child table that is a few relations away. I have found solutions in this forum and elsewhere for similar setups but they have yet to generate expected results. 

Relationships:

System  –<  Assembly  –<  AssemblyItem  >—  Part  >—  Material

A System has multiple Assemblies and each Assembly has multiple AssemblyItems. Each AssemblyItem consists of a single Part made from one of several Materials.

Objective:

Display a portal on System-based layout that shows total number of Parts in the System by Material type.

Example:

As a simplified example, say I have a system containing 10 aluminum parts, 12 bass parts, and 4 plastic parts. The number of assemblies containing these parts is arbitrary.

On the System layout there will be an always visible portal showing:

Parts in System

Aluminum	10
Brass	12
Plastic	4

 

I know there are other ways to achieve something like this, such as a stack of filtered one-row portals or using the calc engine on button bars. However, my database has several more materials that just three, the composition of parts in a system can be significantly different from that of another, and the types of available materials can change. Therefore, it seems like a portal would be the best solution.

Can anyone offer some suggestions?

[comment]

One possible way: have a script triggered upon loading a System record populate a global variable with the value of:

List ( Parts::PartID )

and add a calculation field to the Materials table =

ValueCount ( Filter Values ( List ( Parts::PartID ) ; $$currSysPartIDs ) )

This will count the number of parts that each Material has in common with the currently viewed System. Put this field in a portal to Materials placed on the layout of Systems.

Note that this counts distinct Parts, not AssemblyItems. If you have two items of the same part, they will be counted as one. 

 

I believe it should be also possible to adapt the method shown here to your situation, but I have no time to try it now (the above too is untested).

 

Edited March 11, 2020 by comment