Joining an external table to a hosted feature layer before publishing

[SiranErysian2]

I have several hosted feature layers that I publish as a service. I want to join an external table to each of these feature layers which has a field with a url I want users to be able to view. I joined the table to each of the layers but after I published and viewed the layers in AGOL, the table was not attached. Does AGOL support joins? Is there another way to attach this table to each of the hosted feature layers to be visible in the Collector app that we are using these layers for? I converted the Excel table to a geodatabase table before the join process.

[LanceCole]

Siran‌,

I believe this is by design, in memory joins and relates are not supported to be published.  Please see Author maps to publish feature services.

Joined data

When you publish a map service with feature access capability to a GIS Server site, joined columns are included in the map service but are not included in the feature service. The feature service includes only the columns from the join table (the table or feature class on which you defined the join). If symbology for the layer is based on a joined column, the map service includes the renderer you used to symbolize features, but the feature service reverts to a simple renderer. If the renderer in the layer is based on a column from the left most feature class in the join, the map server and feature server include the renderer.

You cannot publish joined data to an ArcGIS Enterprise portal or ArcGIS Online. Remove joins before publishing a feature service (hosted feature layer) to either of these applications.

That being said, you can publish your features and table(s) with out the joins then create a hosted feature layer view in AGOL.  Please see Join Features analysis tool fro AGOL.  Make sure you check the "Create results as hosted feature layer view" at the bottom of the tool before running the analysis.  Unfortunately, this is a view and not directly editable although you can edit the underlying table and feature services.  However, once have used the Join Features Tool on your data you cannot update the data by republishing from ArcGIS Desktop or Pro when using this method.  Please see Create hosted feature layer views for additional information and limitations.

Hopefully, Collector will support Arcade Expressions in a near future release.  This can be done using a lookup with Arcade in about 3 lines of code to build an expression for AGOL Pop-ups, but it does not function in Collector at this time. Another option would be to append your needed URL to your feature data as an attribute before publishing if you need to regularly update your data.

[SiranErysian2]

Lance,

This worked! However, now I want to strip all my data fields to null and I can’t seem to do that in the now joined table in SQL or Arcade. I am trying to use the Calculate function and not familiar with Arcade trying in SQL: = ‘ ‘ or field = NULL but it does not work.

Looks like I must strip data before joining to table. Any other ideas?

Siran D. Erysian, M.A., GISP,

Research Data Analyst II

_{Think Spatially}

Department of Water Resources

3374 E. Shields Ave, Rm. A-1, Fresno, CA 93726 (559) 230-3312

Siran.Erysian@water.ca.gov

[LanceCole]

Siran,‌

If you created a hosted feature layer view in AGOL using the Join Features Tool, you cannot edit this hosted feature directly nor can you re-publish the data from ArcGIS Desktop or Pro without first removing the layer view.  However, I believe you can still edit the underlying feature data and table that was published to AGOL.  I am note able to confirm this at the moment, but will double check this evening.

Where are you trying to run the calculate function you referenced and on what data.  In Arcade use "Return Null" in SQL it is simply "NULL", both without the quotes.  There is a known issue using Arcade to set strings to Null.  I do not believe that has been resolved to date.  

[LanceCole]

Siran,

An afterthought... give this approach a try since you are trying to display a linked table data in collector.

1) Remove the joins in your map on the ArcGIS platform you are publishing from

2) Add a Relationship Class to the database between the feature class and table you need to link the data.  Note that a relationship class is not the same as creating a in memory join or relates.  If needed, please see Creating a Simple Relationship Class for additional information.  You will need a relationship class for each feature class to be linked to the table.  Although, all of the feature classes can point to the same table, if needed.

3) Publish the feature classes and table to AGOL as feature Service.  The relationship classes will transfer when you publish the data.

4) Create a AGOL Map with the data published and save.  Please insure at least one feature is editable so it will show in the available maps to Collector.

5) Open the Map in Collector.

6) Select a feature that has a relationship class to the underlying table.  A link will be available to the table row in the attribute information for the feature selected as shown below.

7) Select that link and the information from the linked row will be displayed. You can even edit the data by selecting Edit, if enabled.

I hope this is helpful.  let me know if you have any questions.

[SiranErysian2]

Lance,

Looks like I need to copy the related table to each of the geodatabases. There is no way to create a relationship class with a table if its in another geodatabase. Is this correct to the best of your knowledge? DWR Hyperlink_wells_all is the external table I copies to this gdb.

Siran D. Erysian, M.A., GISP,

Research Data Analyst II

_{Think Spatially}

Department of Water Resources

3374 E. Shields Ave, Rm. A-1, Fresno, CA 93726 (559) 230-3312

Siran.Erysian@water.ca.gov

[LanceCole]

Siran,‌

Yes, to create a relationship class the table and feature need to be in the same database or within the same user-schema for a ArcSDE geodatabase.  Please see Modeling limitations of relationship classes.

[SiranErysian2]

Lance,

I published a service with 2 feature layers that have the relationship classes. I included the table in each geodatabase. When I open my map in AGOL one of the 2 hosted feature layers has the link to the table, (show related records) the other one doesn’t. Why would that be? I was hoping I could have 1 table in the map and many layers each with a relationship class to that same table. I have 21 layers I need to put in a map. The table appears in the published map and 2 hosted feature layers.

Please get back to me when you can about this. I need to have this available this week at the latest.

Siran D. Erysian, M.A., GISP,

Research Data Analyst II

_{Think Spatially}

Department of Water Resources

3374 E. Shields Ave, Rm. A-1, Fresno, CA 93726 (559) 230-3312

Siran.Erysian@water.ca.gov

[LanceCole]

Interesting, I have a published mockup I had made the other day with two feature classes and one table for which it is working on AGOL.  However, these were all contained in a single database.  I will look into this further once I get home this evening as I do not have access to the files I used to publish the data.

Just for clarification.  You want/need to access this linked data for use in ArcGIS for Collector not just AGOL, correct?

[SiranErysian2]

Correct. The field techs are going to look at each hosted feature layer in AGOL now since we are off season, and make some decisions on removing some of the well points based on the attributes and the historic data that the link in the table will provide to them. I want them to be able to edit the feature layers to note which ones to drop. The join I did within AGOL worked but will not allow for editing.

Thanks for all you help! I’ll be back in the office tomorrow afternoon.

Siran D. Erysian, M.A., GISP,

Research Data Analyst II

_{Think Spatially}

Department of Water Resources

3374 E. Shields Ave, Rm. A-1, Fresno, CA 93726 (559) 230-3312

Siran.Erysian@water.ca.gov