We created a collector app project last year to inventory catch basin location and attributes. We would like to add an annual maintenance log to the project. I'm thinking I would have one table for the catchbasin physical attributes and a related table (one to many) with an inspection date and activity results. Can collector app be used to add a new inspection record to the related table?
After I posted I came across this article that answers my questions. I still wonder if anyone has done this and if there are any issues? https://blogs.esri.com/esri/arcgis/2015/02/18/related-tables-exploring-new-ways-to-use-collector-for...
Erich,
I would suggest you create a GlobalID-GUID based relationship class for these related records. The related table should have a GlobalID field and the inspection record table should have a GUID field that is related to the GlobalID field in the related table. If the relationships are set up this way, Collector will be able to maintain the related records.
I would consider using Survey123 for the actual inspection form. You can still use Collector for all your mapping needs and have it directly linked to Survey123. I did something similar with Fire Hydrants. You could navigate to the point in collector and via custom popup create a shortcut to open the desired form. There is also a methodology to collect the survey results directly to a related table.
Are you utilizing related tables?
We currently are not but is in the future plans. I did see an article on how to do it and I believe it will get even easier with he next release of Survey123.
We are using Collector extensively to log maintenance, from tree pruning, watering and removal to catch basin inspection and cleaning, with great success. Dan's suggestion to use GlobalID based relationships will make things much easier. And depending on your backend, we are creating views and procedures inside our database that only show items on that map that require maintenance or inspection, whether it's based upon an "open" task that someone has created or based upon some elapsed interval since the last time a maintenance activity was completed. For instance, our stormwater permit requires all catch basins on our campus to be inspected and cleaned if needed every two years. So, if it has been two calendar years since the last inspection was documented, a catch basin shows up on an "Inspection Due" layer in Collector. The inspector can then create a related inspection task which when posted to the database causes the catch basin to be omitted from the "Inspection Due" layer.
These are also fed into operational dashboards that crews use to mission plan in the morning before leaving the shop.
Jade,
I was hoping you might go into more detail about creating views. What are the steps and where are the views?
thank you for any input
Hi Angela,
We are using Oracle Spatial as our Enterprise RDMS so the views are created inside schema level geodatabases in our Oracle db. Some of the scheduling stuff relies heavily on PL\SQL procedures/functions but in general, in the layer definition expression, we are are doing something like:
GLOBALID IN (SELECT GLOBALID FROM <<VIEW THAT RETURNS GLOBALIDs OF FEATURES MEETING SOME CONDITION>>)
In the screenshot below, which shows trash receptacles that need to be serviced (emptied), the Service Frequency (1) is used in conjunction with the last service record's date (2) by RECEPTACLESSERVICENEEDED_V (3) view which returns globalids of receptacles that need to be serviced based on service frequency and last service record.
Here's the SQL of the RECEPTACLESERVICENEEDED_V:
SELECT a.objectid, a.framematrl, a.surfmatrl, a.TYPE, a.receptshape, a.load, a.color, a.owned, a.capacity, a.servicefrequency, a.status, a.needdgnrvw, a.dgnrvwnotes, a.notes, a.globalid, a.created_user, a.created_date, a.last_edited_user, a.last_edited_date, b.created_date lastservicedate, a.servicefrequency, b.nextdatecalculated nextservicedate, a.se_anno_cad_data, a.shape FROM sdelittermgmt.receptacles a JOIN ( -- RECEPTACLES WITH SERVICE RECORDS SELECT receptguid, servicefrequency, receptacleservice.created_date, DECODE ( servicefrequency, '1D', TRUNC (receptacleservice.created_date + 1), '3D', TRUNC (receptacleservice.created_date + 3), '1W', TRUNC (receptacleservice.created_date + 7), '2W', TRUNC (receptacleservice.created_date + 14), '1M', TRUNC ( ADD_MONTHS (receptacleservice.created_date, 1))) nextdatecalculated, RANK () OVER (PARTITION BY receptguid ORDER BY receptacleservice.created_date DESC) rnk FROM receptacleservice JOIN receptacles ON receptacles.globalid = receptacleservice.receptguid -- RECEPTACLES WITHOUT SERVICE RECORDS UNION ALL SELECT globalid receptguid, servicefrequency, created_date, SYSDATE - 1 nextcalculateddate, 1 rnk FROM receptacles a WHERE NOT EXISTS (SELECT receptguid FROM receptacleservice b WHERE b.receptguid = a.globalid)) b ON (receptguid = globalid) WHERE a.status = 'ACTIVE' AND b.rnk = 1 AND SYSDATE > b.nextdatecalculated;
Hope this helps!
Hi Jade,
Thank you. This does help. It gives me an idea of the data structure. Looks like I need to dive into sql, seems to be my reoccurring road block.
Thanks again
angela
Angela Vander Pas
GIS Supervisor
City of Lewiston
T 208.746.1316
C 208.791.9730
F 888.397.8634
215 D Street Suite B
P.O. Box 617
Lewiston, ID 83501-1930
www.cityoflewiston.org<http://www.cityoflewiston.org/>
“To provide excellent public service with entrusted resources.”