Related Data in ArcGIS

26901
17
11-18-2016 01:16 AM
MichaelKelly
Occasional Contributor III
32 17 26.9K

Over the past couple of months, I have being working quite a bit with related data in ArcGIS Online and associated apps. In this blog, I am going to discuss some of my experiences to help others get a better understanding of the capabilities available to them and how they can be setup.

What is Related Data?

The answer is in its name! Related data is data that has an association or relationship with another piece of data. I think the easiest way to explain related data is with a real case example:

Lets say you were collecting housing data in a particular area. You want to collect data about the house itself, for example its address, type, condition, date built, etc. You also want to collect data about the residents in each house. For each resident, you want to collect their name, date of birth, occupation, etc. Some houses will obviously have multiple residents. If you were to store all this data in the one table, you would have to have an infinite list of fields for the residents (resident one name, resident one date of birth, resident one occupation, resident two name, resident two date of birth, resident two occupation, etc.). This is obviously inefficient and impractical. In order to deal with this, you setup two separate tables, one for houses, and one for residents. You setup a unique identifier for each row in the houses table, and then for each resident we store a reference to this meaning that you can look at house, and then check what houses are related based on this value. In database lingo, this structure has a 'cardinality' of one to many, whereby the house has an association or relationship with multiple residents. an illustration of this can be found below:

Example Database Schema with Related Data

There are two other main types of relationships; one to one and many to many. A one to one relationship refers to cases whereby one record is associated with another record. For example a husband may be associated with his wife. A many to many relationship refers to cases whereby many records are associated with many other records. Students and subjects is the typical example that is used. A student may study multiple subjects at school, while a subject may be studied by multiple students.

Finally relationships can also be nested. You may want to store related data about related data! For example, you may want to gather information about the cars that residents own. In that case, your data would contain information about Houses which may contain many Residents who may own many Cars. You can find out more about Relationships and ArcGIS here.

So how can you use these kind of data structures in ArcGIS? You first design the data structure in your desktop software such as ArcGIS Pro/ArcMap/Survey123 Connect for ArcGIS. Once you have the data structure setup, you can start to edit the data. You can edit through ArcGIS Pro/ArcMap, or your can publish to ArcGIS Online or ArcGIS Enterprise and edit data through the various apps that you configure in ArcGIS Online/Portal for ArcGIS. I have put together some videos which demonstrate how you setup the data structure - this is typically the part which people can struggle with but hopefully the videos help you get a solid start. Once you have the data structure setup and published to ArcGIS Online/ArcGIS Enterprise, you configure the various apps as per usual.

Designing your Data Schema

Related data is supported when using ArcGIS Online hosted feature layers or feature services from ArcGIS for Server 10.3.1 or later. Typically these are designed via ArcGIS for Desktop (ArcGIS Pro or ArcMap), but there is now an option to create them using Survey123 Connect for ArcGIS (once you only need to collect point features). When using related data, it is strongly recommended that you use GlobalIDs when defining relationships. User-maintained relationships are not recommended. Check out the videos below for the workflows that I typically follow. In each video, the same data schema is created so you can compare their setup.

ArcGIS Pro (link to video here in case it doesn't display properly)

ArcMap (link to video here in case it doesn't display properly)

Survey123 for ArcGIS (link to video here in case it doesn't display properly)

Survey123 for ArcGIS can only design data schemas for point features

Mobile Data Editing

Survey123 for ArcGIS (Documentation here)

Add

Update (Attachments not currently supported)

Delete

If you just need to add new point features with related data, Survey123 for ArcGIS is probably your best bet. It has a very intuitive interface and was specifically designed to be used offline. It supports one-to-many feature-to-table relationships. See v2.4 Release Notes (Section: Support for editing repeats) for full notes on editing related records support.

In order to collect related data using Survey123 for ArcGIS, your data schema must have been published directly to ArcGIS Online/Portal for ArcGIS using Survey123 Connect for ArcGIS (unless you want to check its advanced features)

Feature services with related data created in Survey123 Connect for ArcGIS do not currently work in Collector for ArcGIS (Resolved as of Survey123 Connect for ArcGIS v1.10.43)

Collector for ArcGIS (Documentation here)

Add

Update

Delete

Collector supports the following relationships as documented here:

  • Feature-to-table relationships (one-to-one and one-to-many)
  • Feature-to-feature relationships (one-to-one and one-to-many)
  • Table-to-table relationships (when the first table is accessed through a feature)

Once you have your feature service published as documented in the Designing your Data Schema section above, all you have to do is add the feature layer to a webmap and share the webmap with a group. Once you have done so, your map should appear when you login to Collector. You can alter how the attributes can be viewed/edited by configuring the webmap popup for the layer and table.

Further information about related tables in Collector for ArcGIS can be found in this blog post

Data Editing on the Web

A common misconception is that ArcGIS Online does not allow you create apps which can edit related data. OK, until the September 2016 release you couldn't edit related data in Web AppBuilder. But there have been configurable templates that allow you to add and update related data available for some time. Here are some of the options that are currently available to you:

Web AppBuilder for ArcGIS Edit Widget

Add

Update

Delete

This is definitely a popular update from the September 2016 release. This widget gives you the ability to edit related records within Web AppBuilder!

ArcGIS Online Attribute Table (Added 20th December 2016)

Add

Update

Delete

With the December 2016 ArcGIS Online release, the ability to edit attributes directly through the Attribute Table became possible. This is something that was suggested via the ArcGIS Idea's site some time ago. Related data can also be viewed and updated through this interface, so it is quite a useful feature.

Feature Layer Item Details Page

Add

Update

Delete

With the June 2016 ArcGIS Online release, the ability to view and edit related data was added to the item details page. This functionality is quite a nice way of viewing the raw data including its relationships and attachments (pictures, documents, etc.).

Crowdsource Manager

Add

Update

Delete

This template is available when you create what's known as a configurable group template. The app looks at all the webmaps shared with the group, and bases the application on these. The key is to check the box beside 'Display related records using the popup configuration'.

Here is a link to a sample app created with this template

Crowdsource Reporter

Add

Update

Delete

Again, this template is available when you create what's known as a configurable group template. The app looks at all the webmaps shared with the group, and bases the application on these. The key is to check the box beside 'Display related records using the popup configuration'.

Here is a link to a sample app created with this template

Crowdsource Polling

Add

Update

Delete

This configurable webmap template differs from the previous two apps, in that it is based on a single webmap, not a group.

Here is a link to a sample app created with this template

Data Editing on the Desktop

ArcGIS Pro

Should you have ArcGIS Pro, you also have the ability to edit feature services live in ArcGIS Online. This can be done by searching through your portal for a webmap, and opening that webmap. Once this is done you can use the Edit tab to add modify and delete features as necessary! See this documentation for further details.

Edits cannot be undone if modifying ArcGIS Online feature layers

ArcMap

With ArcMap, the workflow isn't quite as tailored as with ArcGIS Pro. You first need to make a local copy of the service, and then sync your local edits with the server. The edits aren't done in real time like with ArcGIS Pro. You can check out this documentation for further details.

Conclusion

There is quite a bit covered in this blog post, but hopefully it will help you get a better understanding of how related data can be used in ArcGIS Online. Please feel free to comment below if I have missed out on any capabilities or made mistakes along the way. I will do my best to keep this up to date as new releases come out.

17 Comments
FinnianO_Connor
Occasional Contributor II

Nice blog post, very informative. Do you have a way of labeling features in an ArcGIS Online web map based on the related data? Or getting the data through a popup? It's great to be able to collect the data in a well constructed schema, but to be really useful being able to display/print the data through web maps would be great.

Cheers,

Finnian

MichaelKelly
Occasional Contributor III

Hi Finnian O'Connor, thanks for your comment. I don't believe there is currently a way to label based on related data in ArcGIS Online, although you can create charts, etc. You can configure popups to display information from related records - the functionality varies depending on the relationship. You can check out the documentation here.

The only way I can think of to view related data purely in a popup is to use the Edit widget in Web AppBuilder but only allow the viewing of records, not editing. At the moment, the typical workflow lets you click a point to view its attribute information in a popup, and then click a button to open a table with the associated related data.

MikeSullivan
New Contributor III

Very good blog post, but, the Edit solutions in WebAppBuilder works for online hosted services only.  I have a feature service that is hosted on our instance of ArcGIS for Server and then Added as an item to our Org Site, but the same functionality does not work.  I am able to edit existing related records, but the Add + button does not work on the Edit widget for my related tables hosted internally and shared with the Org site.  Does ESRI have any plans to make sure the same functionality works with internally hosted services?  This seems to be something that should have already been implemented in the Edit Widget tool.

MichaelKelly
Occasional Contributor III

Hi Mike - as far as I am aware this should work. It sounds as though the relationships have not been setup correctly given that you can edit existing records (which would already have relevant parent/child fields populated) but not add. I would suggest you get in touch with support to troubleshoot.

It could be worth seeing whether you can add related records via Collector, and whether records are in fact being added, but not being related to the relevant parent - take a look at the table of the child and see if records are being added. Keep in mind that editing must start from the origin and a many-to-many relationship is not currently supported.

FinnianO_Connor
Occasional Contributor II

Mike, from what you have described I think I have the very same setup - a feature class with a related table (one-to-many relationship on GlobalID) stored in an ArcSDE geodatabase with a service published to ArcGIS Server which is then added as an item on AGOL. The feature layer is added to a web map which then allows editing. Also the web map is consumed through Collector for online/offline editing.

Cheers,

Finnian

GelcysWilliams
New Contributor III

I have an open ticket with ESRI now to understand how I can use relates and relationship classes published in a map service locally - consumed by ArcGIS Online.  The map services are published using 10.2.2 server.  We use the service with several relationship classes in CityWorks without issue - but when I bring the map service into ArcGIS Online, we have issues seeing some of the relationships - do I understand correctly that ArcGIS Online can't use relationship classes published in services at 10.2.2?  We have a relationship class between a fc and table, and then another two between that table and two other tables.  The relationships between the tables is seen on the rest server, but not the one between the fc and table??  

MichaelKelly
Occasional Contributor III

Hi Gelcys - I would suggest you continue to progress this with support. My understanding is nested relates are supported but I'm not sure at what version. If you come to a resolution, perhaps follow up here in case others encounter a similar issue.

yaserkhouja1
New Contributor III

Thank you Mike for this great blog! 

Two questions for you please!: 1/ If I already have a feature layer with relation table, but without a global ID. It just have a unique PK, and FK. How can I add the global ID to substitute the current FK? 

2/ is there a way to use Esri dashboard or filter widget with the relation tables not the feature layer? 

Thanks,

MichaelKelly
Occasional Contributor III

Hi Yaser - glad you like the blog! In answer to your questions:

  1. You would have to add a Global ID to the parent feature class, output the feature class as a table and remove duplicate Parent ID's. Then join this to the child table so you have access to the appropriate Parent ID (new Guid) and copy this into a new Guid field associated with the Child record. Then remove the old relationship and add the new relationship. Note I have not done this, but I think this workflow will work. *First create a copy of the geodatabase so you have a backup.*
  2. You can filter the child records from the webmap, and base Dashboard values on these. With 1-1 relationships, you can add relevant attributes to a popup. With 1-m relationships, you can add statistics of numeric values (sum, min, max) to a popup.
mohamedhamdaoui
New Contributor

First i would say thanks for this all informations, all this steps work very will but in the final when i export my file to ArcGIS Desktop the informations on the table do not display and show me in a tab that i have an error in the colon (Parent guid) so i can't access to this informations of the table.

AndresCastillo
MVP Regular Contributor

Michael Kelly,

I do not understand what you mean in #1. 'You would have ........'

Can you please expand?

JohnRitsko1
New Contributor III

When will AGOL support being able to allow access to the related records in the "Manage Label's" using arcade expressions?  Right now I can only use my related table with a popup but I require a label with the related information.  Thank you. 

Something like this would be great if it worked:

$feature.relationships/0/LASTNAME

RitaMatildes2
New Contributor II

Hi,

I have come across this blog entry which seems really interesting!

I would like to ask for some help regarding editing related records in web appbuilder.

Situation: I have a table of book records and a point feature of locations: N point features for every record on the table (one book mentions N places around the globe and I want to represent each of those places as a point feature related to that book)

In Pro, I have set up a RSC of many to many as I am not able to set a 1:n relationship that allows me to work correctly with a table as origin and feature as destination. Not a very friendly workflow but I can associate N points to one record on the table.

GOAL:  in web appbuilder, garantee the possibility of an user to follow this workflow:

- place a point somewhere and fill the attributes of the book on the table

- place a N points in the map , but relate each to the same book (as the book mentions N places)

In the figure below, I have a new point created in the map and the record I wish to associate is selected in the table of attributes. In the popup I have no option to add this selected record ou "call" it somehow (OID, GlobalID....), just add a new record, but this would duplicate the already existing record...

IS this possible? Is this a wrong approach to the problem?

Some light would be highly appreciated!

Thanks in advance.

Rita

José_AntonioAnta_Viguera
New Contributor

Hello Rita,
I have the same problem.
Have you managed to fix it how?

Thanks for your help

Suedietrich
New Contributor III

Thanks for the very informative post. I am having trouble with configuring the database (fields, domains, and relationships) for my project that requires the public to rank each project ("low priority", "medium priority", and "high priority"). I have 10 recommendations and I want the public to rank each project in order of importance to them (from 1 to 3) so that the final analysis would tell me which project had the "high priority", "medium priority", and "low priority". Thank  you in advance.

Wolfgang_Grunberg_AZ_DFFM
New Contributor III

@MichaelKelly Thank you!

The ArcPro video and this article lead to a breakthrough for me in setting up one-to-many relates in AGOL. 

Is there a similarly thorough and intuitive article/video on how to ingest (denormalize) relates in AGOL App Builder, Dashboard, etc.?

 

UPDATE: AGOL's Classic Map Viewer's Analysis tool allows one to create dynamic (self updating) Hosted Views of feature/table joins. This way you can create a flattened (denormalized) View of your 1:M relates for presentation use (Dashboard, etc.).  Another game changer for us.

HermanLouie1
New Contributor II

Great video on manul method of creating a GeoDB structure for AGOL

I have 1000's already created Houses points in a read-only in a Hosted Feature Layer in AGOL and I don't want users to CRUD the features.

  1. Can I use the same GeoDB Relationship structure with read-only and not use GUID in Houses but a different unique id?
  2. Setup a temporary Houses Feature Layer Ad-hoc for review after, I just wondering what kind of ID to relate back to the Residents Table?

 

 

About the Author
Technical Sales Engineer at Esri Ireland