Has anyone published a feature service based on query definition of related table and get Collector to work offline?

[KevinMorris2]

Anyone successful publishing a feature service to ArcServer 10.5.1 for ESRI Collector with a definition query based on related tables that have required geodatabase relationships set up? I can create the feature service and view and see the features in ArcServer, Portal and Dynamically in Collector, but cannot download a replica to Collector for offline use. Any suggestions?

Any use of the supplemental tables in the definition query causes ArcServer 10.5.1 to fail to create a replica with the error "The map 'HazardTrees' failed to download with the message:Failed to create replica. The Server Log posts Error executing tool. CreateFeatureServiceReplica Job ID: jc5e429ca48084231aad4b36d7e4d0d8d : ErrorMsg@SyncGPService:{"code":400,"description":"Failed to create replica."} Failed to execute (Create Feature Service Replica).

I remove the complex query that worked in "real" time with a query to a single related table and I still cannot generate a replica.

TreeNumber in (SELECT [SEKI_HazardTrees].[dbo].[Veg_HazardTreeSurvey_tbl_VW].TreeNumber from
[SEKI_HazardTrees].[dbo].[Veg_HazardTreeSurvey_tbl_VW] group by [SEKI_HazardTrees].[dbo].[Veg_HazardTreeSurvey_tbl_VW].TreeNumber)

Background:

Hazard Tree (Points)

Hazard Tree Surveys (table)

Hazard Tree Actions (table)

Background:

SQL Server 2016

Microsoft Server 2012

ArcServer 10.5.1

SDE Geodatabase 10.5.1 / also tried in 10.2 (prior to migrating to 10.5.1 feeling that upgrading was necessary).

Collector connecting through Portal 10.5, where the url was added to portal for the feature service and a map created for ESRI collector in Android.

[MicahBabinski]

Hi Kevin,

What field types are used as the primary and foreign keys of the relationship class(es)? I believe that might impact the ability to create replicas for offline Collector use.

Micah

[KevinMorris2]

The Hazard Tree fc (point) and related tables use TreeNumber which is unique and of type Numeric. We do have global ID's also, but these are not utilized for any of the relationships, but only as unique placeholder to allow creation of unique records in the field and versioning (the feature classes and tables are versioned). The schema works fine for collector (allows creation of replica and offline use) as long as I don't add a definition query on the point feature class referencing the related table/s (actions, surveys or a combination of the two).

e.g. this works as a definition query on Hazard Trees (Needs Action) and I can create a replica for collector:

TreeNumber IN (3,4,5,9,10,22)

but this does not work (test case only as the query really doesn't make sense except for testing, note: I also tested the query using just the simple table name up to the fully qualified database.dbo.table names): 

TreeNumber in (SELECT [SEKI_HazardTrees].[dbo].[Veg_HazardTreeSurvey_tbl_VW].TreeNumber from
[SEKI_HazardTrees].[dbo].[Veg_HazardTreeSurvey_tbl_VW] group by [SEKI_HazardTrees].[dbo].[Veg_HazardTreeSurvey_tbl_VW].TreeNumber) 

[MicahBabinski]

Hmmm, in that case you may want to check to see if your ArcGIS Server site is configured to use "standardized queries." I guess it's a security setting at the ArcGIS Server site level that is supposed to prevent SQL injection attacks. The default setting I think is to only allow standardized queries. 

About standardized queries—ArcGIS Server Administration (Windows) | ArcGIS Enterprise 

From my read of the documentation, your use of a subquery may be causing issues when creating the replica. However, I am not sure why you would have been allowed to publish the service in the first place.

I hope you figure it out!

Micah

[KevinMorris2]

I do have standardized queries turned on for security, but my understanding is that REST standardized queries are independent of the definition queries applied to feature classes in ArcMap used to publish the rest service. This appears to be the case as I can review the data "real-time" in Collector over WIFI.

[KevinMorris2]

All,

I opened a work ticket with ESRI and they identified the issue as a bug.

[JonSwoveland]

Hi, just found this thread, and I suspect I'm experiencing the same issue.  In my case,  I have a read-only Feature Service with multiple layers.  Each layer points to the same SDE feature class, but uses a Query Definition to filter the types of features included in the layer.

When using this Feature Service in Collector, everything works fine online, but when attempting to download the map, it fails creating the replica.

Any more information on that bug ESRI found?

[KevinMorris2]

I have not heard back from ESRI. Perhaps they will respond to this thread.