can collector be used to log infrastructure maintenance?

[ErichList]

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?

[ErichList]

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

[Anonymous User]

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.

[ChristopherMcClain]

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.

[AngelaVanderPas2]

Are you utilizing related tables?

[ChristopherMcClain]

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.

https://community.esri.com/message/652708-re-survey123-feature-class-as-a-related-table?commentID=65... 

[JadeFreeman]

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.

[AngelaVanderPas2]

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

[JadeFreeman]

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!

[AngelaVanderPas2]

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