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
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.