AnsweredAssumed Answered

Symbolizing Feature Class using related table values

on May 18, 2018
Latest reply on Mar 16, 2020 by AlabamaForest

Like • Show 1 Like1
Comment • 8

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.

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

andrescastillo08

Mar 25, 2019 12:45 PM

Correct Answer

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:

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

Let me know if you have any questions.

See the reply in context

No one else had this question

Outcomes

8 Replies

andrescastillo08 Jun 5, 2018 8:14 AM
Mark Correct
Correct Answer
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-with-the-geodatabase
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?commentID=774426#comment-774426

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.
Like • Show 0 Likes0
Actions
- andrescastillo08 @ andrescastillo08 on Jun 5, 2018 7:21 AM
  Mark Correct
  Correct Answer
  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-used-in-arcgis.htm#GUID-F5320DB5-4E54-4D49-BF31-A58F5379ED9F
  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?
  Like • Show 0 Likes0
  Actions
brandon.keller May 21, 2018 10:23 AM
Mark Correct
Correct Answer
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!
Like • Show 0 Likes0
Actions
- andrescastillo08 @ brandon.keller on May 21, 2018 12:36 PM
  Mark Correct
  Correct Answer
  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?
  Like • Show 0 Likes0
  Actions
bogg2050_Otter Aug 16, 2018 7:25 AM
Mark Correct
Correct Answer
@ Andres Castillo Were you ever able to figure out the solution to this? I have the same question.
Thanks
Like • Show 0 Likes0
Actions
- andrescastillo08 @ bogg2050_Otter on Sep 5, 2018 6:46 AM
  Mark Correct
  Correct Answer
  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.
  Like • Show 1 Like1
  Actions
andrescastillo08 Mar 25, 2019 12:45 PM
Unmark Correct
Correct Answer
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-features-in-arcgis-online/

Let me know if you have any questions.
Attachments
- symbolizeFromRelatedTable.docx159.3 KB
1 person found this helpful
Like • Show 2 Likes2
Actions
AlabamaForest Mar 16, 2020 12:20 PM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Symbolizing Feature Class using related table values

Outcomes

Attachments

Related Content

Recommended Content

Incoming Links