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

[CameronLacelle]

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.

[Bud]

I don't have experience with Portal, and I'm not quite following your last paragraph:

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.

But a database view or query layer might do what you need. There is a Make Aggregation Query Layer that could be used to get a list of playground inspections and also a field that is a count of the related playground equipment sub-inspections. But that tool is buggy.

If you are unfamiliar with SQL and you want to share some fake data via Excel or a mobile geodatabase, I could show you how to do it via SQL, which could be used in a database view or query layer.

You could share screenshots of fake input data, and mock up the result you want in Excel and share that as a screenshot, to make it clear what you're looking for.

What kind of database is your enterprise geodatabase? I.e. Oracle 18c.

[CameronLacelle]

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.

[Bud]

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]

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.