More Examples for Survey123 Reports

Question

I have a 5-level relational database, and am wanting to write reports with a summary section and then the details in repeats. I am completely stumped on how to summarize the area for all of one type of child feature, like repeat1_polygon. The errors are "cannot parse field, field not available in this scope", and such. I've experimented with the Note on this page "To access questions in a nested repeat,..."which also addresses duplicate layer (or other object, seemingly) names. I've read through the report queries page you reference above, and this blog post by @IsmaelChivite . In the summary section, I'd like to sum the area for repeat1_poolygon. An individual record an be retrieved by calling ${ Planting_Unit.Planting_Unit_polygon | getValue:"area":"square-meters":"planar"}. A non-geometric field can be accessed by "sum,pop2000,populationTotal" in the stats statement. $<$summary> ${Planting_Unit ["Planting_Unti.Planting_Unit_polygon" or variants are declared out of scope] | stats:"sum, [[what goes here??]], PU_SumArea"} $ In the summary section, if getValue syntax is inserted into the "outStatistics" portion of the stats format, it breaks the interpreter because of confusion around opening and closing of brackets, as it does with area(${Planting_Unit_polygon}). Declaring variables, or other such Arcade syntax seems to be not interpreted. I can do this in a popup, it is frustrating to not be able to execute such a summary in a report... How can I access the area field to sum the area of (in my example) all planting Units on a Restoration Site? Here is an unanswered question on gis.stackexchange.com for another example of what one might want to pull out of a report. Thanks for any and all help! https://gis.stackexchange.com/questions/383623/how-to-write-syntax-to-sum-values-for-unique-ids-in-s...

J_Buck · Answer

I have a similar question.

I want to run an outStatistic on a parent field, but within the child layer's aggregate function.

Example

${#childlayer|stats:"sum,childField,sumchild; count,parentLayer.parentField,countparent"} ${sumchild/countparent} ${/}

It seems you cannot use the repeat reference syntax (in red) within these aggregates.

It throws a parsing outside scope error for the "parentLayer.parentField" syntax

Is there another way you can run this calculation or is there a syntax that will work to reference a parent layer in the child layer's aggregate function?

DavidRichey · Answer

@alison_gou ,

Really appreciate your help. Back on this hunt, and have made some progress, but am still not "getting it" in terms of the nesting of the array opens and closures to refer to a constraining site record.

Object Id

Site Name

Submitted by

Submitted time

Invasive Vegetation units

Monitoring Reports

${#RAMD_1_5_Site | orderByFields:"objectid DESC"|resultRecordCount:10} ${RAMD_1_5_Site.objectid}

${RAMD_1_5_Site.Site_Name}

${RAMD_1_5_Site.Creator}

${RAMD_1_5_Site.CreationDate}

${#Invasive_Vegetation_Unit | stats:"count,objectid,IVU_Count"} ${IVU_Count}

${#MonitoringEvent_Invasive_Veg | stats:"count,objectid,IVU_Count"} ${IVU_Count} ${/} ${/} ${/}

The above syntax does not throw an error, but returns the total number of repeats in the entire database for each row in the Invasive vegetation units and Monitoring reports columns, where my desire is to show these values for each RAMD_1_5_Site record (GroupBy). How should I nest the calls to the child objects in this context? I've been using the spaghetti on the wall method without a lot of headway.

Another statistic I'm trying to develop is to tally the number of parent records based on a condition of a child object. The statement ${RAMD_1_5_Site|stats:"count,objectid"} will get the number of records (selected or total, depending on context), and the statement ${RAMD_1_5_Site|where:"1=1 !important"|stats:"count,objectid"} will return a count of all parent records. In the where portion of the clause, I'd like to refer to RAMD_1_5_Site.Invasive_Vegetation_Unit.MonitoringEvent_Invasive_Veg where:"MonitoringEvent_Invasive_Veg.Invasive_Veg_ME_Species in ('Himalayan_blackberry', 'Japanese_knotweed')"

Lastly (thanks for your patience), My current learning process is to edit the template, load it to check syntax, and the test a sample to see if the values are correct. The checking of syntax does not ensure a printable report. Is there a more efficient way to test syntax--similar to the Arcade sandbox idea?

Alison_Gou_ · Answer

Hi @DavidRichey

I have created a survey based on your question, trying my best to get closer to your request as possible.

Here is my data table on Data page where to use feature report in Survey123 website.

What I have been trying to do is to : sum up pump1 when station value is 2.(I think this is similar to your request and this request)

Here is my syntax in feature report template:

${#Form_14 | stats:”sum, pump1,pump1_sum”:”station”:”station=2"}
${pump1_sum}
${/}

and I got right result from the above syntax, which is 300.

Here is some tips to get the right syntax and I use mine as example to explain:

1. # and {/} should be used together as the result is an array;

2. pump1_sum is a temporary key to extract value from array to display on feature report, therefore, I get to see right result -300 in my report;

I got my syntax by reading this Aggregate functions in this help doc.

Hope this helps.

Alison