Perhaps a limited data file to see would help someone assist.
The Database of companies has Many Locations; with many assets.
In the current portal (each location) ; you would lie to see a list displaying assets with a COUNT() correct!
for Company - need a TOTAL COUNT for all locations.
Instead of showing the assets in a portal, use a 'Go to Related Records' script step and then display the records in a list view, sorted by asset type. Swap the Body part for a Sub-Summary on change of Asset type, add a summary field for the quantity and you're done!
Best wishes - Alan Stirling, London, UK
A - Company ( PK: company_id)
B - Location ( PK: location_id )
AB - CompanyLocations ( PK: company_location_id, FK: company_id, FK: location_id)
C - Asset ( PK: asset_id, FK: company_location_id) - could add company_id and location_id and denormalise for easier dev checking if required
REL A-AB = compnay locations, portal uses rel
TBL A, field c_company_locations_count = Count( AB::company_location_id) is the company location count
TBL A, field c_company_asset_count = Count( C::asset_id) is the company asset count
REL AB-C = company location assets
TBL AB field c_company_location_asset_count = Count( Asset::asset_id) is the company loaction asset count
similar approach to count all assets at a location
Now it might be the case that a given location is related to only one company, so the join may be unecessary. But then again, if the location was a warehousing one, and stored assets for multiple companies... But that could be approched a slightly different way probably. Or if a location was sold to a different company... The point is that not using a join is potentially limiting.
As for the 'drill down'
REL A to AB, CompanyLocations ( or B Locations if a location is only ever one, and only one company)
parse the company_location_id to a global to establish a REL using g_company_location_id = C::company_location_id
this will establish REL A::C to show all company assets at a company location
script button on the portal row to parse the company_location_asset_id to a global
REL A to C , and portal, for selected company asset, g_asset_id = C::asset_id
OR as an alternate to the A::C use a single row iteration of AB to C ( company location assets) and filter it by C::asset_id = g_asset_id
Or somthing like that.