Symbolizing Feature Class using related table values

[AndresCastillo]

I am branching off this thread:

https://community.esri.com/message/772784-re-collector-use-related-layer-to-symbolise-feature

I have a manhole inspections fc, related to a inspection history table, using the Parent's 'GlobalID' field (aka gid)/ and Child's 'Parent guid' field in a one to many relationship,
and the table has attachments using gid/Parent guid.

I am trying to symbolize the parent feature class based off fields from the child table.

So far, I've attempted two methods:

1.
I attempted the sql script from the FC in arcmap using this definition query:
SELECT * FROM ssManholeInspections WHERE:
GlobalID IN (SELECT ParentGUID from ssManholeInspectionsHistory)

The resulting fc ends up with 4292 records, even though the inspection history table has 4665 records.

2.

Joining the table to the fc also results in 4292 records, with the 'Validate Join' Button producing a Join Validation Report
Dialog window that says:
The number of matching records for the join:
- 4292 of 7454 records matched by joining [GlobalID] from <ssManholeInspections> with [ParentGUID] from <ssManholeInspectionsHistory>.
Matching records may not appear in table view due to join validation errors.

Cardinality Warning:
Join Validation detected 4665 non-distinct matching records when using [GlobalID] from <ssManholeInspections> with [ParentGUID] from <ssManholeInspectionsHistory>.  For geodatabase data, ArcMap will join all matching records, although only the first matching record will be displayed in the attribute table of <ssManholeInspections>.  This relationship is 1:Many or Many:Many. When working with 1:M and M:M relationships, it is recommended that relates or Relationship Classes be used instead of joins.  Refer to the ArcGIS Desktop Help topic: About joining and relating tables for best practices on creating relationships between tables.

It is fine that 7454 records did not join because not all manholes have been inspected yet.

In both cases, only the first matching record is returned, which is incorrect.

It seems that I can only display the latest record (kind of like a 1:1 join), so I would have to include in the definition query the ability to pick the latest inspection date (which is updated using the editor tracking create date field, named 'InsStart' with Data type of Date) per unique foreign key (Parent GUID).

I suppose the correct query should also yield 4292 records, but it should return the records with the latest inspection date per ParentGUID, instead of  returning only the first matching record. 

In order to verify the expected count, I used the summary statistics tool on max date, case field guid.

This gave me the 4292 records I needed.

Yet, this is static, and I would like to see these records in real time as the data is edited.

These threads also talk about my question, but I would like help to put it into context.

https://community.esri.com/thread/90206

https://community.esri.com/thread/194907-select-records-with-most-recent-date-for-groups-of-points

https://community.esri.com/thread/169714

[AndresCastillo]

The following was created using SQL Management studio:

SELECT ss.GlobalID AS 'FC_Global_ID', ss.OBJECTID AS 'FC_LONGINTEGEROID', ssh.ParentGUID AS 'Parent_GUID', MAX(ssh.InsStart) AS 'Inspection_Date_Time' FROM Sanitary.sde.ssManholeInspections_evw AS ss JOIN Sanitary.sde.ssManholeInspectionsHistory_evw AS ssh ON ss.GlobalID = ssh.ParentGUID GROUP BY ss.GlobalID, ssh.ParentGUID, ss.OBJECTID‍‍

Create Database view gp tool

Register the view with the geodatabase:

https://community.esri.com/groups/geodatabase/blog/2016/12/14/new-at-105-registering-a-database-view...

Enable Editing privileges

My concern is that I'd rather not create a database view, as I heard it slows performance.

If I must use a view, then I might use it as an intermediary which loads/overwrites the refreshed data daily into a table with the same schema as the view.

This makes the data static on a daily basis

For more information about this, read this post I contributed to:

https://community.esri.com/message/774426-re-versioned-views-versus-sql-views-with-a-spatial-column?...

I'd rather create a definition query in the arcmap layer properties, because then I believe this would be a live query on the child table, that I can then join directly to the parent feature class to symbolize off of.

[AndresCastillo]

Based on this documentation, I found out that there is a limitation in using subqueries:

http://desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/sql-reference-for-query-expressions...

Subqueries that are performed on versioned ArcSDE feature classes and tables will not return features that are stored in the delta tables.

For each record in the table, a subquery may need to parse all the data in its target table. It may be extremely slow to execute on a large dataset.

I started to pursue using subqueries to set a definition query on the child table within arcmap to choose the latest inspection record per id.

OBJECTID in (select MAX(OBJECTID) from Sanitary.sde.ssManholeInspectionsHistory_evw GROUP BY ParentGUID)‍

Or

InsStart in (SELECT MAX(InsStart) FROM Sanitary.sde.ssManholeInspectionsHistory_evw GROUP BY ParentGUID)‍

Or

NOT EXISTS (SELECT 1 FROM Sanitary.sde.ssManholeInspectionsHistory_evw f WHERE 'Sanitary.sde.ssManholeInspectionsHistory_evw.ParentGUID' = 'f.ParentGUID' AND 'f.InsStart' > 'Sanitary.sde.ssManholeInspectionsHistory_evw.InsStart')‍

This third query seems as if it should work, but I have not been able to get the desired records from it. 

The first two subqueries returned the desired records for me, yet, when I went to join the queried child table to the parent feature class, using the parent 'global id' field, and child 'ParentGUID' field as the unique identifier, I get this error message from the Join Validation Report:

The number of matching records for the join:
- Unexpected error encountered.
- No matches found by joining [GlobalID] from <Sanitary.SDE.ssManholeInspections> with [ParentGUID] from <Sanitary.SDE.ssManholeInspectionsHistory>.

Any ideas?

[BrandonKeller]

I am doing a very similar workflow and I solved it through the use of a SQL view.  I am joining my MH's to my inspections and in order to return the last inspection (by date) in the related table I use the MAX function. In my view I also calculate the total inspections on each MH and the age of the last inspection.

,MAX(inspections.CREATEDON) AS Last_Inspection
,COUNT(*) AS Number_Inspections

,DATEDIFF(DAY,a.CREATEDON,GETDATE()) + 1 AS INSPAGEDAYS 

Hope this helps!

[AndresCastillo]

Hi Brandon.

I'm not quite sure what you mean.

Can you explain and break down your SQL script for me?

Would it be easier to explain over the phone?

[RickBoggs1]

@ Andres Castillo Were you ever able to figure out the solution to this? I have the same question.

Thanks

[AndresCastillo]

Hi Rick,

Sorry for the delayed reply.

I have to update this thread with the solution.

I have to organize my documentation to strip out sensitive information so I can share.

In the meantime, we can do a walk-through via private message.

[AndresCastillo · Accepted solution]

Attached is the solution, as a word document

(See this correct reply in the context of this thread to see the attachment)

Note that there are 17 pages.

As an alternative to my word document, which uses ArcGIS server, ESRI's  Paul Barker

tells us of how to solve this when relying on ArcGIS online.

Here is his post:

Visualizing related data with Join Features in ArcGIS Online 

https://www.esri.com/arcgis-blog/products/product/mapping/visualizing-related-data-with-join-feature... 

Here is another alternative:

https://community.esri.com/community/gis/web-gis/arcgisonline/blog/2020/03/25/easy-how-to-symbology-... 

Joseph Rhodes‌

Let me know if you have any questions.

[AbiDhakal]

Hello folks,

I'm in a similar situation. I have a feature service with a related record and want to symbolize my feature using the latest record from the related table not as a snapshot but real-time.

I will really appreciate your help.

Thank you.

Abi 

[KevinMacLeodCAI]

 I have the same question but with an added "if" condition. I have Manholes. Actually it's Sewer Structures and some of the features are pump stations, some are manholes. So I want to see just the Manholes in this featureclass.  And I want join the Inspections from the related ManholeInspection table to it; and keep all features in the feature class (sewerStructures left join to ManholeInspections) so even manholes without an inspection are kept. Now first of all, the issue is it randomly will only keep the first record. I want all inspections. 

But.. I also want a layer showing only IF inspections where the attribute 'NASSCO' = Yes (it is an attribute of yes/no) and of that, I only want the MOST recent one because I want a true count of NASSCO-inspected manholes. Thus I wouldn't want to stack them on top of each other if they'd been NASSCO inspected several times. So I'd just want a MAX DateInspected. To ensure just one record.

So, the trickiest question is.....   Show Manholes symbolized by related data, but only if a particular attribute is true. I have SQL Server 2017 and ArcSDE 10.7.1 with ArcMap 10.7.1 and latest Pro. Thoughts all? Can this be done in ArcMap definition query with a subquery or a combination of a SQL view and definition query? Ultimately this will be used to show dashboards and track inspections, for this as well as similar scenario for other datasets.

I would note that this has been easy on AGOL; you can choose which record to keep in a Join and by which field. Not sure why they'd leave out this powerful vital aspect of join logic on Desktop/Pro. All you can do is Keep All Record or Keep Matching. But no choice over which to pick when there is 1-to-Many and multiple hits.

I had thought I could just simply do a view joining Manholes to Inspections, then in ArcMap do a definition query on this to NASSCO=True and then Join this, in ArcMap, to the original feature class. No dice. The problem, is that because it has a definition query, it follows the join and overrides it. And let's say there are 2000 manholes but only 6 that meet the NASSCO=true. As soon as I join the view to the FC; with the view having the Def Query, even if I say Keep All Records, it appears that does not get obeyed, the Def Query follows and overrides that and you get only 6 records. I had hoped just have blanks for the related stuff on all the FC rows except those 6 rows. (in other words 1,994 rows where related data is blank, those 6 rows would have the related data).  I think this will have to be done completely in SQL as it seems SQL is limited in ArcMap and so are joins and working with related data.  For example, I hit another limitation trying to simply show the most recent record with MAX Data.  Seems like ArcGIS can't do a MAX date. 

[DateInspected] in (SELECT max( [DateInspected] ) FROM MyDatasesName.DBO.PumpStationInspections__View GROUP BY [GlobalID])

  Doesn't work in ArcMap, works perfectly in SQL Server as a view.

https://community.esri.com/t5/geodatabase-questions/max-date-group-by-id-in-model-builder/td-p/10082...

If I figure this out, I'll update ....

Overall platform suggestion for 2021: more focus on related data, less focus on 3D, VR, etc.