Complex calculations in a Repeat

[Phein62]

I'm using Survey123 Connect, v. 3.22.53.  I have repeats in my survey -- based on the Advanced Template -- where I collect information on concrete surface abnormalities, including type (cracking, spalling, etc), length in inches, and recommended action (monitor, repair, no action).

I can get the sum of all the lengths for each entry in this repeat by creating a group outside of the repeat that references named fields from within the repeat, e.g.:

begin group            Summary            Summary of Cracks

decimal                     TotalLength       Total Length of Cracks                 sum(${Length})

end group

The repeat looks something like:

begin repeat        Surface                      Surface Condition

begin group         Surface1                      </>

decimal                  Length                     Length

select_one           DamageType           Type             [NOTE:  This would be cracking, spalling, etc.]

select_one           Recommendation    Recommendation   [NOTE: This would be Monitor, Repair, No Action]

end group

end repeat

What I need to be able to do is get the sum of cracks based on which type of abnormality it is, that is, I only want to include the length of cracks, not the length of spalling, and only those recommended for repairs, not the ones where the recommendation is monitor or no action.

[Neal-Kittelson · Accepted solution]

You can achieve this by creating a hidden field inside the repeat that captures the length only for cracking and only when repairs are recommended. Then sum that field outside the repeat.

begin group            Summary            Summary of Cracks

decimal                     TotalLength       Total Length of Cracks                 sum(${CrackLength})

end group

The repeat looks something like:

begin repeat        Surface                      Surface Condition

begin group         Surface1                      </>

decimal                  Length                     Length

select_one           DamageType           Type             [NOTE:  This would be cracking, spalling, etc.]

select_one           Recommendation    Recommendation   [NOTE: This would be Monitor, Repair, No Action]

hidden                   CrackLength                 Calculation: if(selected(${DamageType},'cracking') and selected(${Recommendation},'Repair'),${Length},0)           bind::type: decimal

end group

end repeat

[Phein62]

Thank you very much!   Worked like a charm, and I can see other uses for that syntax.

[Phein62]

Thank you so very much! I'm going to print this and pin it to my bulletin board.