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

756
3
Jump to solution
04-28-2022 09:35 AM
archeomason
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).

FieldApp_AutoTopDepthCalculation.jpg

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:

XLSForm_AutoTopDepthCalculation.jpg

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...

Thank you in advance!

0 Kudos
1 Solution

Accepted Solutions
DougBrowning
MVP Esteemed Contributor

I did this using max trick like this

DougBrowning_0-1651164831159.png

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

View solution in original post

0 Kudos
3 Replies
DougBrowning
MVP Esteemed Contributor

I did this using max trick like this

DougBrowning_0-1651164831159.png

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

0 Kudos
archeomason
New Contributor III

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

archeomason
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.