Symbolize published layer using related table attribute

[LauraCarson]

I have a parcel dataset with a related table that I have both published to my company's Enterprise server. We are currently symbolizing the parcel feature class based on a "priority ranking" field. We want to avoid adding any additional fields to our main parcel feature class and any project specific attributes will be added to the related project data table. That said, some projects want to rank the parcels differently than others, and as such, we add a ranking field specific to that project in the related project data table.

There is not currently a way (that I know of) to symbolize the parcels based on their related table rank attribute and we do not want to utilize any of ArcGIS Online's analysis tools (e.g. joins) to accomplish this. The capability to symbolize off a related attribute would be ideal.

Feature class currently symbolized by priority rank attribute in the main parcel dataset:

Related record showing the related ranking attribute I want to symbolize the parcel layer on:

[MaryDavis1]

Would also love to be able to filter on a related table attribute.

[DavidForbuss1]

I do this from Enterprise geodatabase layers by creating SQL views.  Essentially, you create the view in the gdb and publish it, then symbolize off of that.  Here's an example of how we symbolize our pipes layer on when it was last TV'd (the related worklog table): 

SELECT a.*, b.pipelength, b.workcode, b.date_ FROM ssgravitymain_evw a JOIN TVWorkLog_evw b ON a.facilityid=b.facilityid

a. being the parent layer (ssgravitymains) and b. being the related TVworklog....this way I only grab the pipelength, date and workcodes from the child table.  

Unfortunately I haven't been able to get this work for a hosted layer, so this might not be possible for you depending on your data structure.

[TanuHoque]

In ArcGIS, in order for you to symbolize a feature by an attribute, that field/attribute must be part of the layer.

Since, your additional attributes are in a related table; there are a few things you can do that I can think of:

1. what @DavidForbuss1 suggested above i.e. create a view
2. Simply join your parcel layer with the related table. When both your parcel feature class and related table are in the same database, the performance should be on par with the view approach. But user experience-wise it is simpler, and no need to worry about how to create the join using sql.
3. create a query layer. It is same as view except that it is a layer stored with the map or aprx. Whereas view is something you have to persist in the database and that requires privileges. Plus query layer provides some advanced capabilities, not necessarily need them in this case.

hope this helps.

Tanu

[LauraCarson]

Thanks @DavidForbuss1 for the suggestion. Do you know if the SQL Views are editable? We want to make sure our end user can still edit the data that is being symbolized off the related attribute. This could be a potential work around that we're going to test, however it's not ideal to have a SQL View published each time we want to symbolize on a different attribute. We'll give it a shot and see how it goes though. Thank you!

[LauraCarson]

Thank you @TanuHoque for the response. We are going to test out David's recommendation using SQL views and we'll try out the query layers you recommended as well. It still would be more convenient for this capability to be built in to map/app configuration post publishing as a feature service. Is that something Esri plans to incorporate in the future? It seems related attributes are quite limited in their functionality in web apps, not only in symbolizing, but also in the filter widget and displaying related attributes joined to its feature class in the attribute table.

[DavidForbuss1]

As far as I know they're read only.  I haven't found a way to get them to be editable.  Feel free to reach out if you need help with setting it up.  I'd be happy to share some of the views and maps we've created.

[TonyContreras_Frisco_TX]

While this would be great functionality, I think there might be a technical limitation, since there are different types of relationships (1:1, M:N, 1:M, etc). In the case of 1:1 relationship, this would probably work, but what happens when one feature has more than one record in the other table that is related to it? Which one of those values in the designated field is used to symbolize that feature?

May I ask why you are not joining the parcel feature class to the parcel table? Is it because you don't want to have to set up the join (and add the table) each time you add the Parcel feature class to a map? If that is the case, the database view with join is a better choice. Users can still edit the table on it's own and the changes will instantly be reflected in the view. They just won't be able to edit directly through the view.  Going back to a layer joined in your map, maybe you could save it as a layer file after you set it up and re-use it for other maps?

[LauraCarson]

I just wanted to follow up that I talked with Esri staff at the Esri UC recently regarding my question.

To summarize: I have a 1:1 relationship of a table record to a parcel feature class and I want to symbolize on my related table attribute and/or use it in a widget within my web application. My team also needs the capability of editing the feature within the web application.

Esri staff's recommendation was to perform a join on the layer prior to publishing. They said there was no other workaround to maintain editing capabilities. The downside of this is that I'm going to be left with several published services from the same layer just so that I can enable a join on my published feature service. I have multiple feature classes where I will have to perform the same join/publish process so I can see the published services of the joins getting out of hand. During one of the Esri sessions I attended regarding troubleshooting Enterprise, the Esri presenter even mentioned that multiple services published on the same layer can cause performance issues.

I would still love to see if a solution might get created to solve this issue in a later release.

[DavidForbuss1]

Hi @LauraCarson , 

We recently switched to using an arcade script to display the information directly in a popup, and it runs off an expression (below).  It seems that you could use that same script to do an on-the-fly join in the webmap and symbolize off the created columns.  I'll see if I can get it to work on my end.  Here's the script I'm using for the popups.  I found it in one of these forum posts, but I can't remember where I found it, or else I'd give that person some much deserved credit for conjuring it up : )

// Access 'Inspections' table as a FeatureSet
var portal = Portal("PORTAL HERE")
var inspections = FeatureSetByPortalItem(portal,
"FEATURE SERVICE ID NUMBER HERE", LAYER#, ['FIELD1', 'FIELD2',
'DATEFIELD'])

// Filter related features by using a common attribute
var facilityid = $feature.PARENT_ID
var filterStatement = 'CHILD_ID = @PARENT_ID'

// Related features as a variable
var relatedData = Filter(inspections, filterStatement)

// Sort related features by oldest to newest
var relatedDataSorted = Top(OrderBy(relatedData, 'DATEFIELD DESC'),1)

// Build the pop-up string by iterating through all related features
var popupString = 'Last Flushed Date: '
for (var f in relatedDataSorted){

popupString += Text(f.DATEFIELD, 'MM/DD/YYYY')

}

DefaultValue(popupString, 'No measurements to show')