In the post below it describes using ArcGIS Collector to carry out inspections of assets by creating a relationship to an inspection table. This works really well and is a good way to separate asset edits from maintenance operations, however I cannot work out how to update the symbology to display when an asset has had an inspection.
If you have a large number of assets then you need a method for the field works to keep track of what has been inspected and what is still due, I originally thought of having a 'inspection_status' field that would be set at the start of the season to 'Due' and would have a default setting of 'Not Due' so that every time an inspection took place the field value would automatically change from 'Due' to 'Not Due' without the field worker having to do anything other than fill in their inspection form.
I was then going use the icons below depending on which value the feature had for 'inspection_status' The only problem is that this field is in the inspection table and not accessible from the feature class to use to set the symbology!!!! Arrrgh
Does anyone know another way to achieve the same result please or could this be considered for a future enhancement.
Solved! Go to Solution.
Database Management tools are: SQL Developer Studio for Oracle, Microsoft SQL Server Management Studio for SQL, PgAdmin III for PostgreSQL. They are the tools provided by the database software for managing the database.
I am no longer using the views - the performance of the views was very slow.
In Oracle, with "in" statements like mine, if I had hard coded values like this:
SELECT * FROM myTable WHERE value in (1,2,3...1001);
It would not have worked, because oracle only allows 1000 values in such a statement, but selecting values in another table is fine. You probably don't need to worry about this but it has caused issues for me in the past so I wanted to make sure to share it.
Do you have multiple inspections in your inspection table for one manhole? From what I can tell it looks like you have duplicate ParentGUIDs in your related table - you'll need a way to only look at current or active ones. I do this in my tables with the "ACTIVE = 'YES'", that way old contact information (no longer 'active') isn't a factor.
By the way, thank you very much for specifying how to denote a versioned table in sql.
Since I can only show the current record, then 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') per unique foreign key (Parent GUID).
These thread talk about this, but I would like help to put it into context.
How would I verify I end up with the desired query?
I'm happy I can help a little!
As for verifying your query that one is tough no matter what because you're going to need to verify the correct number of records. If you are able to have the data maintainers verify, that is always good because no one will know the data better, maybe they even have an SSRS report or something similar from another system that can give you the correct number of records you should be looking for in your query. You will really need to learn the data well if you want to be sure of your SQL query results (this is true across the board with any data you'll ever interact with and build systems to update). If you have a DBA who is good with SQL you might reach out to them for good resources and help!
Yes, you are right.
I suppose the correct query will yield 4292 records, but the query should return the records with the latest 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.
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?
the reason I asked was because when I was trying to find a solution to this I tried a load of different approaches, definition querys, views, labels etc all of which worked fine in ArcMap or pro but none of it worked as required in collector once published.
Its been awhile since testing mind so very interested to see how you get on.