Adding related table to ArcGIS Online feature layer

[EricGardecki1]

I'm trying to figure out how to add a relationship class (related table one-to-many) to a feature layer within AGOL. I do not have ArcGIS Desktop or Server (do have QGIS but I don't think that will help here).

The only info I was able to find led me to believe that it may be possible by editing the service definition JSON but I can't find any reference as to how to edit it to make it work.

What are my options here?

Also, is viewing/editing/adding to related tables supported in AGOL web apps yet (not just Collector)?

Thanks!!

[Otaki]

Is there any updated documentation for this problem? 

Thanks in advance! 

[KhaledHassen]

Eric,

This is not a relationship item and it is not related to the portal API. As you have noticed it is related to the layer relationships and can only be set using the feature servivce admin API. I will check check and see the documentation about this. The above example model one-many relationship between layers. You can define the relationship as a composite or not. In a composite relationship, the destination row will be deleted if you delete the origin feature/row.

I might be able to walk you through this if you have any issues.

Khaled Hassen

[XanderBakker]

Hi Eric Gardecki ,

Just wondering what you want to do once you have your relationship class defined in the service?

I ask since there are a couple of "solutions" that will work without having a relationship class in your data. An example is filtering data in a dashboard (operations dashboard will not recognize your relationship class and you can define fields that will be used to do the cross filtering). Another example is Insights which will allow you to configure relationships in your worksheet and if you want to visualize an aggregation of your related data in for instance the pop-up of a feature you could retrieve the related records without the need for a relationship class using Arcade. A description of how this works can be found here: https://community.esri.com/docs/DOC-12773-using-featuresetby-functions-in-arcade-to-drill-down-to-ot... 

[KhaledHassen]

In feature services, client normally use queryRelated API to find out related rows to an origin or vise versa.

Another use case is to define composite relationship between origin layer and its destination layer. Deleting an origin row would delete the related row and if it is not composite the feature service would set null or remove the related rows by setting null in the foreign key.

In the top of all these, editing is the main requirement to use relationships.

All other solutions in arcgis are read only join.

We do offer similar functionality (similar to insight) build in the feature service to do join and combine the two layers into one join layer. If the join layer is what the client needs, it will be good to continue and model it inside the feature service rather than external services like insight since all clients can use the REST API on the feature service join layer as any other non-join layer.

[EricGardecki1]

I am trying to do something similar to the esri Collector sample for fire hydrant inspections.  I will have a layer for catch basins, that need inspecting.  Each inspection needs to be recorded and kept for historical purposes.  Hence the related table, which would house the inspections for the catch basin layer.  So when a user does an inspection via Collector, they click on the feature and add a new related record.  The inspections MAY need to be entered via a web app at times, and I will need to display the features in a manner that shows which features have been/not been inspected within a given time period.  I will need a dashboard displaying assorted status information.  

I haven't looked into what issues I may encounter when trying to used related records to symbolize and extract for a dashboard but hopefully that wouldn't be a problem.

If there is another way to accomplish this, i'm all ears!

Thanks

[XanderBakker]

Hi Eric Gardecki ,

For the workflow that you describe a relationship class is indeed required. I hope that with the suggestions provided by Khaled Hassen you will be able to create the relationship class in the service. You may want to consider a Desktop license for the future to make this process a lot easier and enable other capabilities. 

If later on you want to have some ideas on visualizing the results in a Dashboard or using the Arcade functions in a pop-up, just tag me. 

[EricGardecki1]

Xander Bakker‌

I'd like to symbolize a layer based upon data in the related table.  Particularly, with a layer of catch basins, and a related table of inspections that contain a date.  I'd like to display the catch basins in a manner such as those inspected this year be green and the others, red.  Is this doable?

You also mentioned visualizing the related data better in the popup...can the popup list all of the related data without having to open it separately?

Thanks!

[XanderBakker]

Hi Eric Gardecki 

I'd like to symbolize a layer based upon data in the related table.  Particularly, with a layer of catch basins, and a related table of inspections that contain a date.  I'd like to display the catch basins in a manner such as those inspected this year be green and the others, red.  Is this doable?

The answer is no and yes... At this point the Arcade functions that allow access to other tables and layers does not work in the symbology profile. The reason is that this would cause a big impact on performance. However, if the data is not dynamic you can use the field calculation to calculate a value based on the related records and symbolize on that field. The downside is that when the data changes this will not be reflected in the symbols. You could re-calculate the field every X time, but that is not very user-friendly. It would be great is the field calculation allowed for some configuration that would trigger a calculation just like a scheduled task.

You also mentioned visualizing the related data better in the popup...can the popup list all of the related data without having to open it separately?

Yes this is possible (up to some extent). In the post that I shared before: Using FeatureSetBy functions in Arcade to drill-down to other layers and tables I list all the dates and types of maintenance for a hydrant shown in a list. 

Depending on what you want to do I suppose it will be possible. 

[AndresCastillo]

Hi XanderEric Gardecki, Khaled Hassen , and Adrian Welsh , 

FYI, and in case you might find it useful:

I have come up with a solution to symbolizing from a related table in my post:

Symbolizing Feature Class using related table values 

https://community.esri.com/thread/215046-symbolizing-feature-class-using-related-table-values  

There is a 17 page word document with details on how to accomplish this (using a desktop license).

[KhaledHassen]

Here are the steps you need to copy the layer from one service to another; i.e., from service1 to service2

1. Create an empty layer (table you need) using service addToDefinition admin API. This will add an empy table or layer to the feature service. You will need to copy the list of fields from the source layer (service1) into the definition below which will live in service2.

help url: Add to Definition (Feature Service)—ArcGIS REST API: Services Directory | ArcGIS for Developers 

https://url/ArcGIS/rest/admin/services/<service2>/FeatureServer/addToDefinition

Json to use will look like:

{

  "layers" : [

  {

     "name"  : "mylayername",

     "type" : "Table", // you can also make a feature layer by using Feature "Feature Layer" instead of "Table

    "fields" : [

    {

        name" : "myFieldName",

        // copy the rest from the original layer

   } ,

  ...

  ]

}

]

}

2. Export the layer from service one into a filegdb.

3. use Update data from the online UX to append the filegdb into the empty layer you created earlier.

By completing 1,2,3 then you have move the table from service 1 to service 2.

Then you will be able to add the relationship as we talked earlier.

Hope that helps.

Khaled