Link Survey123 Responses to a separate Feature Layer for viewing in a Dashboard

[DavidEvans5]

Fellow Esri users,

We’re looking at creating a publicly accessible Survey123 form for contractors to report Backflow Prevention Device tests. That’s the easy part and we already have the form created. What I’d like to do now is link results (records) created in this Survey123 form to a Point feature layer containing Backflow Device locations (there are only around 150 of them in the system) so that I can create a Dashboard to display the status of whether each Device has been tested or not.

I know that I can use the Analysis tools available in the ArcGIS online web map to run a table join on the two data sets, but I what I really want need is for these two data sources to be continually linked or for the join to be automated in some way.

Is this something that can be accomplished with features hosted entirely within our ArcGIS Online account or would we need to host some/all of the data ourselves through an Enterprise Portal? I’ve seen another topic which asked if Survey123 could automatically update a separate layer and that certainly might be an option we could investigate though the solution involved using Notebook to read, transform and write to the other layer, which was a little beyond my experience and comfort zone so if anyone has a simpler method I would love to get your input. Apologies as well if there's something obvious that I'm missing here or if this question has already been answered (I did look!). 

Thanks you'all

[DougBrowning]

Take a look at the FeatuteSet functions in Arcade.  I do this all the time to lookup data in a child or parent, find the last inspection date, even summarize points in a polygon all on the fly.  Pretty slick.  https://developers.arcgis.com/arcade/function-reference/data_functions/#featuresetbyname 

Then in Ops dashboard you can use Data Expressions which are super powerful.

https://www.esri.com/arcgis-blog/products/ops-dashboard/announcements/introducing-data-expressions-i... 

Hope that helps

[DavidEvans5]

Hi Doug, thank you for the quick response! I've taken a look at the FeatuteSet function and found the following Blog Posts quite informative. Sadly, the example given in the Blog doesn't match my use case. In their example the FeatureSet is used to return the number of point features intersecting a poly feature. It would help me tremendously to see an example or learn what Arcade expression to use in order to join information in the Survey123 table to the Backflow Device Point Feature, using a common field (such as an Account Number).

I think if that is possible using the Feature Set function then I will really be onto something and can use the Data Expressions as you suggested. 

[DougBrowning]

I just use Filter which is just a SQL query lookup to any layer based on the name in the map.

In this case here I am looking up a value from the parent.  I know there is only one parent so just one return - so I can use first.

var sql = "PointID = '" + $feature.PointID + "'";
var tbl = Filter(FeatureSetByName($map,"Points", ['DesignLat'], false), sql);
return First(tbl).DesignLat

But you can also use if

var EvaluationID = Concatenate($feature.PointID, "_", text($feature.FieldEvalDate, "Y-MM-DD"))
var sql = "EvaluationID = '" + EvaluationID + "'";
var tbl = Filter(FeatureSetByName($map,"Floodprone Width", ['EvaluationID'], false), sql);

if (count(tbl) > 1) {
    return "More than one form found"
}
else if (count(tbl) < 1) {
    return "No forms found"
}
else {
    return "1"
}

Or loop through them

var index = 0
var last = ''
for (var f in OrderBy(tbl, "UnknownCode")) {
    if (f.UnknownCode == last) {
        Dict.features[index] = {   
            'attributes': {   
                'dups': last,
            }}   
        ++index
        
    }
    last = f.UnknownCode
}

Hope that helps.  

[DavidEvans5]

Hi Doug,

Thanks again for your response on Friday last week. I jumped right in and tested the Arcade Expressions you kindly suggested and I’m pleased to say that with a little finagling I was able to get it working by substituting the relevant feature and field names as shown below: (Note this Expression was created in the Attribute Expressions section of the Water Backflow Preventer Feature Pop-up, since I’m assuming this is the “Parent” and the Backflow Prevention Device Test Form Survey123 records are the “Child” in this data relationship) 

var sql = "LocationAccNo = '" + $feature.LocationAccNo + "'";
var tbl = Filter(FeatureSetByName($map,"Backflow Prevention Device Test Form", ['LocationAccNo'], false), sql);
return First(tbl).LocationAccNo

Hurrah! But… There’s still a couple more steps that I’m not so clear on that will probably require additional code that you (or anyone else reading this) might be able to help me out with.

First off, I would like if possible to have the symbology of the Backflow Preventer location points reflect whether there is a corresponding Survey123 record or not. This will allow the user to see at a glance which Backflow Preventers have/have not been inspected. My first preference would be to use the Filter functions in the Web Map, but my custom “Survey123 Join” field does not show up in the list that the Filter expression can be built from. The Symbology tools for the layer have an option to create a “New Expression” that take me to a similar Arcade expression builder, however I’m not able to simply copy and paste the same expression I used for the Pop-up (I get a “Parse Error:featuresetbyname is not available” error).

Secondly, I need a way to filter and list the Backflow Preventer locations that do not have associated Survey123 records that could ideally be outputted to a .csv file (with name & address information) to be used for mail merges etc. I notice that when I view the table in the Web Map or in Web App Builder there is a column for the joined data but the values are not initially shown (I have to click on a little “Show” link to make them show up). Would adapting one of the other Arcade Expressions you suggested solve this? (I chose the first one because it was the simplest).  

Finally, this workflow is intended to be performed annually, so any advice on how to expand the expression to look at a date field and return/join only the results within a calendar year would be most appreciated. If this is not possible or is exponentially more complicated, a work around I think could use would simply be to export out the previous years Survey123 reports and then wipe the data table clean at start of each year, but this is doesn’t seem like the most elegant solution.

Thanks again for any help/advice you can provide

[DougBrowning]

No you cannot use Arcade to symbolize.  Prob its biggest weakness.  Its a speed issue.  Imagine having to run all those Arcade at once for a map display.  Same thing with the attribute table, just too slow.  So yes it gets limiting.

It is mostly for popups

I would also look into the new Attribute Rules coming in Field Maps - in beta now.  We also go old school sometimes and run a Python script each night to update values.

Sorry prob not what you wanted to hear.

[DavidEvans5]

Hi Doug,

Thank you again for being so helpful. Running a Python script doesn't sound like a bad option, especially if it can be automated somehow. Are you using them for a similar application to what I've described (joining Survey123 record data into another feature)? If so would you be willing to share here (or offline) or is there another ESRI resource you can point me toward to help get me started?

Would this approach be possible with everything being hosted under our AGOL organization account or would it need to be running in an Enterprise Portal?

[DougBrowning]

Well the great thing with Pro is you can run the same tools on a hosted service.  It will grab your login from Pro for up to 2 weeks or so.  Just give it the URL to the service anywhere you would use a GDB..  A script would just be a join and a field calc then release the join.  Pretty straight forward.

[DavidEvans5]

Yeah, that's an option for sure, especially if everything is hosted in our AGOL account. 

If there's a more automated option I'll leave this question open for suggestions until the end of the week. 

Thanks again!!