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