Create a view layer to join one row of an inspection to many of sub-inspections

Question

We have a hosted feature layer created from Survey123 for playground inspections for our Parks Department. The inspections consist of one overall main inspection for the playground and multiple related sub-inspections for each piece of equipment in the playground. I want to create a view layer in Portal to join these two but I want the view layer to only contain the number of rows as there are main inspections (right now 23 rows).

Currently when I create the view, it results in the view layer having 101 rows, which is the total number of sub-inspections. So it looks like there are over 100 inspections completed for the playgrounds when really it's all the sub-inspections so we have duplicate values.

Is there a way to perform this join in the view layer that will just append the column names of the sub-inspection table to the main inspection table? Obviously with the same column names, that is why it's appending rows, but I would be fine if it just appended to the columns with a suffix "_1", "_2", etc. Can I do this in arcade somehow right in the pop-up for the feature layer? The feature layer itself contains the main feature and the related sub-table.

CameronLacelle · Answer

Yeah that's a good idea, I feel like I'm continually adding to the ArcGIS Ideas whenever I hit a wall using Enterprise apps. Which is unfortunately more often than I'd like it to be. Appreciate the reply.

Bud · Answer

Would it make sense for you to submit an ArcGIS Enterprise Idea?

* Title: Aggregate layer view (hosted feature layer)
* Description: Roll up a given table/FC. Group the rows by a common ID and add a count column. It would be a dynamic query, not a static output/export.
* Use Case: Join from a parent layer to a child aggregate layer. The relationship of the raw data was 1:M, but the relationship to the rolled-up aggregate layer would be 1:1. Provide a link to this post for additional info.
* Submitting an idea would force Esri to confirm it can't already be done using existing functionality.

Or something like that?

CameronLacelle · Answer

Yeah our team creates SQL views quite often but as it stands right now the data is a hosted feature layer so I believe I can only make a view in Portal via "create view layer" from the hosted.

Luckily I've found that I can add the related table's contents directly to the pop-up, but this doesn't function properly in the dashboard where the pop-up is featured.

We are working on migrating the hosted feature layer for this project into an enterprise geodatabase. We are a Microsoft SQL Server shop.