Latest Contributions by AndresCastillo

I see what you mean. When I removed the '_evw' from the query, it worked. Unfortunately, I'd rather see the edits in real time. I might end up going with a spatial database view, which will allow me to see these real time. The spatial database view would be something along the lines of the following when trying to symbolize a feature class from the attribute fields of a table: CREATE VIEW TestAndresMaxDateEquals AS SELECT ss.OBJECTID AS 'FC_LONGINTEGEROID', ss.GlobalID AS 'FC_Global_ID', ss.SHAPE AS 'ESRI_Shape', ssh.InsStatus AS 'Inspection_Status', ssh.REPSTATUS AS 'Repair_Status', ssh.CONDITION_GENERAL AS 'General_Condition', ssh.InsStart AS 'Inspection_Date_Time', ssh.ParentGUID AS 'Parent_GUID' FROM Sanitary.sde.ssManholeInspections_evw AS ss JOIN Sanitary.sde.ssManholeInspectionsHistory_evw AS ssh ON ss.GlobalID = ssh.ParentGUID WHERE ssh.InsStart = (SELECT MAX(ssh2.InsStart) FROM Sanitary.sde.ssManholeInspectionsHistory_evw AS ssh2 WHERE ssh2.ParentGUID = ssh.ParentGUID)‍‍‍‍‍ Caveats: 1. I will have to register the FC and Table involved in this view as versioned with the option to move edits to base, which may not detect conflicts on the edits. I am willing to overlook this because I think conflict would be minimal in my application. 2. So that my application can see the edits real-time, I will publish the source mxd with the layers pointing to the default version. I will update this if I decide something else here, and in my post: https://community.esri.com/thread/215046-symbolizing-feature-class-using-related-table-values Please let me know if you see any weaknesses in my query. I can't thank you enough Joshua for your help. EDIT: I am debating whether or not to replace the '=' operator with the 'in' operator for the subquery in my query statement. Any feedback? ... View more

Yes, I searched, and it brought me to the thread that suggested the single quote. My DBA and I tried looking at it on ssms, but couldn't figure it out. I will continue searching for more solutions. Thank you Joshua. ... View more

I tried: NOT EXISTS (SELECT 1 FROM [sde].[ssManholeInspectionsHistory_evw] f WHERE [sde].[ssManholeInspectionsHistory_evw].[ParentGUID] = f.ParentGUID AND f.InsStart > [sde].[ssManholeInspectionsHistory_evw].[InsStart])‍ NOT EXISTS (SELECT 1 FROM [sde].[ssManholeInspectionsHistory_evw] f WHERE [sde].[ssManholeInspectionsHistory_evw].ParentGUID = f.ParentGUID AND f.InsStart > [sde].[ssManholeInspectionsHistory_evw].InsStart)‍ NOT EXISTS (SELECT 1 FROM sde.ssManholeInspectionsHistory_evw f WHERE sde.ssManholeInspectionsHistory_evw.ParentGUID = f.ParentGUID AND f.InsStart > sde.ssManholeInspectionsHistory_evw.InsStart)‍ None worked. ... View more

SQL server 2014.120.5579.0 EGDB is 10.4.1 Removing the single quotes within arcmap definition query gave me the following message: ... View more

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? ... View more

I was able to successfully run the sql query you provided on arcmap 10.4 by adding single quotes around the column names, and replacing the letters fc with the actual table name.  See Chris Wells' comments in this thread: https://community.esri.com/thread/24148 This is my definition query within arcmap: 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')‍‍ There are no error messages when I validate the query. Yet, when I execute the query, I am getting the same number of records I had when there was no query.  FYI, The ParentGUID field is a GUID Data type, and contains duplicate IDs. The InsStart field is a Datetime Data type, and contains unique Dates for every record. Joshua Bixby, Can you please explain what this NOT EXISTS sql statement is doing, and how I may fix what I may be doing wrong? ... View more

How do I message you? ... View more

We are trying to symbolize the parent feature class off a field in a related table. Is this your feature architecture as well? Can you share your script? How did you set your script to run on edit? ... View more

This thread might also answer this: https://community.esri.com/thread/194914-using-a-python-script-to-reconcile-and-post ... View more

Registering the data as versioned without the option to move edits to the base table is beneficial because the other option does not allow for conflict detection, nor replication. See this: http://desktop.arcgis.com/en/arcmap/10.4/manage-data/geodatabases/data-maintenance-strategies.htm ... View more

Can you share what you tried that worked? ... View more

I would think if the data were versioned, you would have to use a versioned view. See: http://desktop.arcgis.com/en/arcmap/10.4/manage-data/using-sql-with-gdbs/read-sqlserver-versioned-data-with-sql.htm ... View more

I haven't tried to publish yet. I will post what I end up doing as soon as I can. ... View more

My DBA and I came up with this: SELECT ParentGUID AS 'Parent_GUID', CONCAT(CONVERT(nvarchar(30), MAX(InsStart), 110), ' ', CONVERT(nvarchar(30), MAX(InsStart), 108)) AS 'Inspection_Date_Time' FROM [sde].[SSMANHOLEINSPECTIONSHISTORY_EVW] GROUP BY ParentGUID ‍ Create Database view gp tool on the SSMANHOLEINSPECTIONSHISTORY_EVW table. Join the view to the parent fc, matching records only, using the Parent Global ID/Child GUID as the join fields Then also join the SSMANHOLEINSPECTIONSHISTORY_EVW table to the parent fc, matching records only, using the Parent Global ID/Child GUID as the join fields Add the parent fc several times to perform the same operations to symbolize based on field categories, using the same view and same table. I do notice the slow performance you were talking about with views, which is why I'd rather do this within arcmap as a definition query on the parent point fc layer itself, using something similar to the subquery you mentioned like this: SELECT * FROM ssManholeInspections WHERE: GlobalID IN (SELECT ParentGUID from ssManholeInspectionsHistory) Any other thoughts? ... View more