Brainstorm help - calculating averages across nested repeats

672
4
01-26-2023 08:57 AM
Katie_Clark
MVP Regular Contributor

I'm a bit stuck on another problem with a form I'm trying to create for field data collection, coming here in the hopes that someone can help me brainstorm - this community has been such a great resource before on getting some fresh perspectives to help "get the wheels turning"!

Here's what I'm trying to do: 

The team needs to collect vegetation data for quadrats along a transect. The number of quadrats will vary based on the length of the transect, so I need to allow it to be variable. At the end of collecting veg data in each quadrat, the AVERAGE percent cover for each species listed needs to be calculated for the whole transect. However, a species won't necessarily be found in every quadrat. 

So, the schema I would like to do (for the best user interface) would be a repeat question (for quadrat number) with a nested repeat for vegetation. I need to identify all the species identified within the transect (no duplicates), and then, for each instance that species is observed, add the percent cover that was recorded for that species. Then divide that sum by the total number of transects to get the average percent cover for the species. I then need to be able to retrieve this value for each specific species to pull into a report, which I guess presents its own problems due to the variable nature of the number of species that will be observed in a quadrat. 

I'm assuming a JavaScript function is the best way to approach this, perhaps something with dictionaries where key:value pairs are the species [key] and percent cover [value]. But I'm just struggling a bit on how to get to the "next step" in developing that. This is where my inexperience really shows...

Any thoughts are greatly appreciated!!!

Best,
Katie


“The goal is not simply to ‘work hard, play hard.’ The goal is to make our work and our play indistinguishable.”
- Simon Sinek
0 Kudos
4 Replies
abureaux
MVP Regular Contributor

To begin with the KISS principal, why not just use a single repeat and have the user enter the Transect and Quadrant data each time? That would vastly simplify this.

You could then format those two integer fields as a Spinner and use a simple formula in an index-repeat to pull the Transect and Quadrant from the previous repeat item so they only need to enter the most recent number. This would be a simpler deployment and still minimize data entry on the tech's part.

0 Kudos
Katie_Clark
MVP Regular Contributor

So, I actually did think about something along these lines, but was worried it would complicate the user interface too much so that the field crew wouldn't be on board...

First off, there isn't specific transect data to enter, because one transect is one form submission. So that's good! And having them enter the quadrat number wouldn't be too bad...

I want to make sure I'm following what you're suggesting though. Are you saying that for a record in the repeat they would enter a species name (from a standardized drop down) and the percent cover and the quadrat number it's in, and then click the + sign for a new record for each new observation? 

Best,
Katie


“The goal is not simply to ‘work hard, play hard.’ The goal is to make our work and our play indistinguishable.”
- Simon Sinek
0 Kudos
abureaux
MVP Regular Contributor

Correct.

As DougBrowning mentioned above, data analysis should be done on the database end. But a nice summary table for the tech can be helpful too. I do this in a lot of my sampling forms, but have the summaries set to null. If you do include a summary that uses concat() and is NOT set to null, be aware that the default field length is only 255 characters. Anything over this will result in an error when you try to send data. Setting these fields to null will remove that issue, and since you want to do data analysis at the database end anyways, you won't need them.

It sounds like your form is quite small, so I think you could get away with a single repeat. But if you really do need a nested repeat, you will have to get a little fancier and double up on the calculation (also as DougBrowning mentioned above).

DougBrowning
MVP Esteemed Contributor

Sounds like you are trying to do analysis in the form.  I have found this does not work out well.  The biggest gottca has been users doing QA or other data fixes in the web map or dashboard.  The calcs do not refire of course so the summary values are often wrong.  If you calc in the form and then just print out a report where is the QA?  Just assuming the crew did it all perfect the first time if not realistic.  

A lot of what you list could be done in Arcade and displayed in a Dashboard or even a PDF summary using Data Driven pages or Python/ReportLab (we do both and integrate them).  This way if someone sees a crazy number they can edit the data then the summaries all live update or run overnight.  Getting people off of the addiction to static PDFs has been slow going but Dashboards are getting us closer every day.  PDFs just turn into a pile of paper then everyone says they are busy drowning in paper all day - endless cycle.

I know not totally what you asked for but I think you will spend a ton of time on something that will not work out.  BTW I am doing quadrat and veg surveys also in Wetland, Lotic, and Terrestrial systems.  We collect thousands of plots with millions of records per year nationwide.  So a lot of lessons learned.

Now that said we do have a lot of data summary pages for the crews.  We just do not use these numbers as the final.

For nested repeats you need to double sum at each level.

Level 1

DougBrowning_0-1674762924718.png

Level 2 sums the sums

DougBrowning_1-1674762957870.png

 

Using Grid helps a lot for display

DougBrowning_2-1674763005643.png

Red warnings are great

DougBrowning_3-1674763190994.png

I also use emojis 

DougBrowning_4-1674763253001.png

 

But your calc by species and such is going to be too complicated prob.  Piece of cake in Python though.

Hope that helps hard to explain it all.  Feel free to reach out to me for more in depth or copies of the forms.

0 Kudos