S123 Template Reports 'Where' under $layers

[TheoHorton]

Hi there

I have got a feature service for wildlife patrols with three layers - one was used to create an S123 form (patrol_summary), the other two layers (patrol_route and wildlife_sightings) were used to create a quick capture project. 

Once data has been captured using QC you would then fill out the patrol_summary survey and as they are in the same feature service you are able to reference the QC layers in the survey123 word template to create a patrol summary report. 

I have managed to use the following to successfully produce a table that lists all of the wildlife sightings in the QC layer. 

Species	Age / Sex Group	Number Seen
${#$layers[“wildlife_sightings”]} ${species}	${age_sex}	${no_seen} ${/}

However, this returns ALL of the wildlife sightings. I have tried to filter the wildlife sightings using the where function and the shared 'patrol_id' field which links all the layers. See below. 

${#$layers[“wildlife_sightings”]|where:"patrol_id='${patrol_id}'"}

This however does not work. I have confirmed I can do this if I input the actual patrol_id number eg. ${#$layers[“wildlife_sightings”]|where:"patrol_id=20260218-1931-TH"} but the dynamic capabilities of using ${patrol_id} which is selected during the patrol_summary survey does not appear to work. 

Is this not possible and if so is there a work around?

Thanks. 

[Neal_t_k]

Those SQL statements can be finicky, try one of thse:

${#$layers[“wildlife_sightings”]|where:"patrol_id='"${patrol_id}"'"}

${#$layers[“wildlife_sightings”]|where:"patrol_id='"+${patrol_id}+"'"}

You may also have to tell it which layer patrol_id is coming from: 

https://community.esri.com/t5/arcgis-survey123-questions/summary-section-where-doesn-t-work-with-que...

[TheoHorton · Accepted solution]

Thanks for the response Neal. 

ESRI support actually managed to come up with the solution for this one. 

Species	Age/Sex Group	Number Seen
${#$layers[0]} ${if $layers[5].patrol_id == $layers[0].patrol_id } ${species}	${age_sex}	${no_seen} ${/} ${/}