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)
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.
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.
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
The following was created using SQL Management studio:
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.