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
