My company has many parcel layers that store parcels with IDs in one enterprise geodatabase, and tables with landowner information in a separate enterprise geodatabase, all on an instance of SQL Server. Normally for Mapping services I publish them with a join and the attributes and geometry are both available online.
For Feature Access it seems a join is not supported. The Help suggested making a View, however, with the View in the map now I receive the error "Layer's data source is not supported". I thought it might be that the View does not have a GlobalID, but I am unable to add one in ArcCatalog.
I tried a Relationship Class but that doesn't allow me to use the table's attributes for things like labeling and symbolizing.
I must be missing something. How can I serve joined data as a Service with Feature Access?
It seems the View does not work because I am using the CreateDatabaseView tool which actually creates a Query Layer, which is Read Only, discussed here. That makes sense that it would raise an error with Feature Access.
I have Server for Workgroup and from a separate question understand now that I cannot use the ArcSDE command line tools. However if I could, it seems the command line tool creates a View as a new Feature Class, which would seem to support Feature Access. Can anyone verify this?
I don't need this joined table to be edited I just need to be able to take it offline in Portal. That requires Feature Access and Sync capabilities.
I was told by ESRI: "The correct workflow to publish a feature service is by using actual feature class."
There is no way to use a join, view or query layer as a Feature Service. I was told on the phone this morning to try publishing it as a Map Service and retroactively enable Feature Access but I have not tried it yet.
Just to be clear for myself here are the limitations, using an ArcSDE database with SQL Server Express and Feature Service:
1. Join: not supported (but service publisher help suggests using a view instead)
2. View: not supported because it cannot be registered with the database because it does not have an objectid field. I do not understand why the CreateDatabaseView tool turns the objectid field of a view into a long integer, whereas the command line arcsde tools did not.
3. Query Layer: not supported because of a bug that was turned into a known limitation for which I do not have reference information. Also I think I understood correctly that using an SDE database is a limitation. The Evolution of Query Layers | Support Services Blog
We are trying to accomplish the exact same thing on 10.5 and received the same reply from Esri .
Its surprising that they haven't tried to address the issue even after 3 releases. I'm curious to know what workaround strategies you have approached in this context .
The trouble with allowing feature access to views, is that feature access should allow updates to the data source from the client application. This is not always possible with views. However, I reckon ESRI should allow views to be used as data sources for feature access with the 'create', 'delete' and 'update' options unavailable, and the 'sync' option should work one-way only for these services. It can't be that hard to implement.
My work around at my previous job (and will probably do the same here) is to use database views, and a script that automatically runs at regular intervals (eg, overnight or hourly) to copy the view into a feature class (truncating the feature class first). This is not ideal, as the feature class becomes out-of-date as soon as anything in the original data changes. However, it is good enough for many use-cases.
In fact, I have used this work-around so extensively, that I ended up writing a generic view-to-feature-class script, that had a configuration section that specified all of the input views, their related output feature classes, and optionally the 'where' clause to use on the view if not all rows should be included in the output feature class. This way, the one single script would update (copy) multiple views to feature classes all in one hit.
Thanks Tasmanian Irrigation, that would work on a smaller scale I think, but with a global dataset with editors editing across time zones (essentially continuously) we really would want read-only joins to be implemented without such a workaround. I can understand the complexity about editing, but not about read-only feature services.