# If() statement in repeat section, integer question's 'calculation' column not functioning

756
3
04-28-2022 09:35 AM
New Contributor III

Hello, community -

I hope someone might be able to help me troubleshoot a conditional formula in an integer question's 'calculation' field. The field is inside a repeat and the formula's condition references the repeat record's position(..).

For context: I've developed a form where the user records different soil strata as they dig. The attributes of each of these strata (color, consistency, observations, etc.) are recorded in a \${SoilLevel} repeat. The user records the top and bottom depth of each of these strata (see below).

I'd like the form to fill in each level's Top Depth (\${SoilTopDepth}) automatically with "0" for the first level (\${LevelNumber}) and the previous level's Bottom Depth (\${SoilBotDepth}) for all levels after that.

The formula I'm using in the \${SoilTopDepth} field's "Calculation" column isn't populating the field on the form:

if((position(..)=1), 0, (once(max(\${SoilBotDepth}))))

For broader context, here's a screencap of the relevant section of the XLSForm:

Can someone point out what I might have wrong?

Also, I was curious if my calculation formula for the \${LevelNumber} field (once(count(\${SoilLevel}))) might work better if it were replaced with position(..). Just curious about that one...

1 Solution

Accepted Solutions
MVP Esteemed Contributor

I did this using max trick like this

This is a soil pit form.  We do share our forms out to the public if you just want mine.  Just pm me.

Hope that helps

3 Replies
MVP Esteemed Contributor

I did this using max trick like this

This is a soil pit form.  We do share our forms out to the public if you just want mine.  Just pm me.

Hope that helps

New Contributor III

Thank you very much, @DougBrowning! I will try that out. And I just may take you up on your offer!

New Contributor III

That worked like a champ! Thank you very much.

For what it's worth, I did have it working using the indexed-repeat() function, where it would populate from the previous record's (position(..)-1) bottom depth, but I had this warning prompt saying I had a circular reference that might cause errors in calculations. So I was trying to find a better option.

Yours seems to work great. Thank you very much, sir.