Calculating field values based on conditions of other fields - repeats or individual - in Field Form or Feature Report?
I consider myself still a relative noobie with the Survey123 platform, but the more that I work with it, the more I conclude that the data entry form is often the easy part. It's wrangling the post-field dataset that's the real challenge. I’m in this state of, “It feels like it should be able to do this, but I must be missing something…” I've learned a lot thanks to folks' responses to my questions so I'd appreciate anyone's suggestions on how to address a conditional calculation problem I'm having with a wetland form/report I'm building.
I can’t tell which direction I should go to resolve the issue: 1) modify the field form in some way to produce static data that’s compatible with the feature report; 2) code the feature report differently to calculate the values I need from the field data; or 3) look to a different tool for the report output.
I have a repeat where the user fills in or the form calculates several fields for different plants in an observation area. (In reality, there are different, distinct plant strata to record for a given observation area, but for the ease of explanation, I’m just lumping them into one repeat). Here's a crudely-annotated screen-cap of the end-result I’m trying to achieve. It's a USACE wetland data form, which has to generally match this format.
For each record in the ${Plants} repeat, they fill in the ${Species} as an auto-complete select_one_from_file then they estimate the ${Cover} as an integer. The form then uses a pulldata function from a related CSV to fill in that species' ${Status} code (e.g., OBL, FAC, FACU, UPL, etc.). A ${TotalCover} field outside of the repeat sums all of the cover estimates for each of the plants. I have a calculation field that populates the yes/no ${Dominant} field: if(${Cover} div ${TotalCover}>=0.5, 'yes', 'no'). I know that that calculation on the field form is flawed, however, because it will always record as ‘yes’ for the first record because the first record’s cover will always equal 100% of the total cover when that record is active. And that’s a challenge on its own, but you can get around it by filling all the plants out, then going back and refreshing the calculation. Not ideal, but it’ll work.
The real problem I’m running into comes when I try to run different value sums and counts elsewhere on the form (or, as I’m actually trying to do, the feature report) based upon the ${Status} codes of the different plants. For instance, something along the lines of sum(${Cover} where ${Status} = ‘OBL’). Or count(${Plants} where ${Dominant} = ‘yes’ and ${Status} = ‘OBL’ or ‘FACW’ or ‘FACU’). I thought I could set up hidden fields in the form that sum the cover for each of the different ${Status} categories, but to my knowledge there's no Sumif()-type function to do that.
I’ve reviewed the documentation and the message boards, but as far as I can tell, there’s no way to filter repeat calculations on the field form based upon different conditions nor can you format the feature report to calculate conditionals within a repeat. So, it seems like I’m stuck.
Does anyone have any ideas how I can get around this? If it's even possible within Survey123... I just don't know where to go from here
