Setting up a related table for Data Collection in ArcGIS Online and Experience Builder
Setting up a related table in ArcGIS Online
Step one is to decide what the database needs to contain and what fields will be included.
In this example, I'm setting up a collection app for water main flushing. There are forty-some locations that must be flushed monthly, and they time the flushing and take water samples during the process.
From the PW dept I got the paper form they use to log the flushing and took note of the values they fill in, which will become the fields. The address fields and the comment field can be in the main table because they don't change. In fact, they will rarely add or move a location. The related table needs to have the data that is collected at each flush cycle. These include things like the start and stop time, the number of gallons flushed, and the chlorine readings. Each flush cycle will get new values in these fields, and you don't want to overwrite the old values because you need a running history of the flushings. There is a month-end report that has to be done for water quality, as well as a quarterly report done for the total gallons of water flushed (someone has to pay for that). By archiving the values you can generate any of these reports at any time.
One field, however, presents a problem - the Date_Flushed field. While you want to store this with each individual flushing, if you put it in the related table you can't use it to symbolize the map. One thing they want in the map is to color code how long its been since a line was flushed because if it's more than a month it become a priority. The solution is to make two date fields - Last Time Flushed in the main table and Sample Date in the related table. Since the input forms can populate these automatically, there really isn't a problem with having it twice.
I fixed up a spreadsheet with all of the values from their form becoming a column, and made sure the names were appropriate for field names. And I added a Site ID field that can be used to manage the relationship between all the files later on. Then in the spreadsheet calculated a unique integer value for each site into the SiteID field. Then exported to a CSV file. I was able to import the addresses into this table with a copy/paste from another list.
This gave me a table that replicated the data collection form. Then I dropped that into ArcGIS Pro and geocoded the addresses to make a point feature class from the addresses.
A quick note about doing this in Pro and where this is heading ...
The new point feature class has all the physical locations, plus all the fields in it. What I want to wind up with is a point feature class with the physical locations and the Last_Date_Flushed field and the address fields. Then the rest of the fields will be in a stand alone table with no geometry. Both of these things could exist in ArcGIS Pro, but what isn't available in AGOL is the relationship class that will tie these two files together. Relationship Classes can only be built in Pro ... but they CAN be shared in AGOL. So the process will be to fix this up with everything I want THEN share it to AGOL. And once it is shared AGOL will show the Hosted Feature Layer of location points and the table of flushing values, it doesn't show the relationship class. IT'S STILL THERE ... but AGOL has no way of showing it. The table and feature layer can be used in all the new Experience Builder, Dashboard and Field Maps apps, but it can only edit through the relationship in Web App Builder. Perhaps this will change in future (and I'll certainly push this in all beta testing).
So for now, let's finish the setup in Pro.
My plan is to make a new empty table and use the new point feature class as the template for the fields. Then I can choose just the fields I want to have in the table, and set the field types to be whatever I want instead of what the CVS import sets up automatically (which is usually wrong). When that's done I'll delete the fields from the feature class that are in the table.
You can see here that I started the table creation for a table called Water_Sample_Readings, imported the schema from the point layer, and I'm changing the field type for some of the fields. This example has start and stop fields for both the time it was flushed and the time the CL2 samples are taken. The date fields can store time that can be extracted later with Arcade.
Next I deleted the fields that aren't needed here but will appear in the feature class. With everything set, I have this:
I finished the creation of the table and it's ready.
Next I'll move over to the feature class and delete the sample values fields that are now in the table. I also changed the name of the Last Flush Date Field.
Save changes and the two files are ready to go!
Now for the relationship class. This is created at teh geodatabase level - it can't exist in a feature dataset because it hast to use the table, and a table can't exist in a feature dataset.
Right click the GDB name and select New> Relationship Class. Setting the parameters is a little tedious, but it's actually pretty simple. The origin table is the sites, the destination table is the readings, and it's a one to many relationship using SiteID as the key field. The other setting are just procedural.
In the catalog tree you can see the point feature class, the table, and the relationship class.
All that's left is to share them in AGOL. Note that once they are shared, the copies of these items that reside in the geodatabase can be deleted. They won't appear in any maps and won't be edited or updated. All the work will occur in AGOL.
I also took the default basemap layers out of the Pro project - they won't be copied to AGOL but they will throw a caution error when you run the share process.
So the Contents Pane will look like this:
Finally, it's time to share these files. Go to the Share menu and select Web Layer > Publish Web Layer
Set up a name with a summary, tags, and sharing permissions and click Publish.
Note that all this prep work has to be done in ArcGIS Pro, but after that everything else happens in ArcGIS Online.
-------------------------
ArcGIS Online
-------------------------
You should now have a new hosted feature layer in AGOL with a point layer and a table. Note that there isn't a relationship class shown.
If you want proof of the relationship class, you can go to the Data tab and look at the fields. You'll see a field called Site ID shown in italics with no values and a lock next to it. THAT'S the relationship keeper.
And if you look at the Fields list the italicized field exists with the field type of Related.
So it's there! And you'll see a similar thing in the table.
Let's make a map of the data. From the details page (or using whatever other process you are familiar with)) open the data in Map Viewer and save as a map.
The trick to using the related table in Experience Builder is that you have to build an input form and identify the related fields in the form. If you’re doing this in Web AppBuilder you don’t need to build the form because Web AppBuilder doesn’t read it anyway.
To do this, with the map open in Map Viewer click the Forms button.
Click the button to build the form using the pop-up (you can easily add, reorder, or remove fields if it’s not exactly what you want). In the Form Builder on the right you’ll see a category called Relate Records. This identifies any related tables and which field is used as the key.
You can drag the related field into the form (in this case Site ID) or because I only have 1 I clicked Add All. There are a few parameters you can set which are pretty self explanatory. I set mine to sort and display the sample date.
Click OK to save the form. My map has two layers with related tables and I had to build the form with the related records form element in the second layer, too.
Save the map and exit Map Viewer.
The related tables are supported both in Web AppBuilder and now (as of Oct 2023) in Experience Builder. The first set of instructions are how to do this in Web AppBuilder and further down are the instructions on doing this in Experience Builder.
So let's do the Web AppBuilder first.
From the Water Quality Testing Sites Map details page, click Create Web App> Web AppBuilder
Name and create the web app.
You can set up a theme and colors and all that ... I'm just going to focus on the editing widget, which is where the data collections take place.
Go to the Widgets tab and click one of the empty widget placeholders. Select the Edit widget to go there.
Down at the bottom, change the 'Editable layers settings' to Custom.
Here you can set which layers or fields can be edited. I set it so that the sample sites can't be deleted.
Click OK and save the app. You may also want to set the Edit widget to be open when the app starts (done by clicking the little circle in the widget icon).
After some use, you may want to also set which fields are displayed or lock editing for them.
The map view in the app configuration is live, so you can test it here.
Click the Edit tool, then click on one of the sites.
The popup will show a few fields, and display the related table at the bottom.
Click the Edit pencil. Since this is the first time to try and colelct a sample, there is no data to display.
Then click the big PLUS sign in the upper right.
A new record is added tot he related table and you are prompted to enter values. The technician would make his readings and enter the values.
When finished, close the window and the values are saved.
Test it out by click in the same location again and open the related table. You'll see data listed now (assuming you actually entered a value). It shows them by record number, but you can have it display a different field by clicking the tiny drop-down arrow and selecting a field (such as sample date).
If anyone figures out how to make the sample date display the default, please let me know!
Experience Builder.
Now let’s build this in Experience Builder.
In Experience Builder you’ll create a new project. For this example I just used the blank screen because I’m only going to put two widgets in it.
When this is created, drag a Map widget onto the design canvas. I set this to use the Water Quality Samples map – the one with the forms set up.
Then drag in the Edit widget.
I selected the map as the input for editing and imported all of the map’s layers. You can do this one by one if you like but this is a just a quick example.
The widget will now show the layers you chose, as well as a Select button.
Clicking the layers at the bottom will add new features to the map, and clicking Select will let you select an existing feature. I want to select an existing feature because I want to add records to the related table.
To test this I went into Live mode and selected a feature. The Edit widget now displays the tools to select or add features, but also lists the entries from the related table. I can click the pencil icon next any of the records to edit them AND I CAN CLICK ADD AT THE BOTTOM! This creates a new record and lets me enter new readings.
THAT'S IT!! One way to do this in Web AppBuilder and one way to do this in Experience Builder.
It's a tedious journey but not a hard one. Now you can open this app in the field, select a sample site, add a record to the related table, and record the test readings.
As the table is populated, you can use it in other apps. Here's an example of a dashboard that displays charts of the readings, list of values at any one site, or display calculated values using the sample values. Note this one also has selection filters so you can show all of the sites, filter to a particular site, and filter to a range of dates.
I even added a little Arcade code to change the color of the display box for pH level if the fall outside normal thresholds.
Give this a try and share your successes with others.
