How to Automatically Subtract Values from a Repeat in Survey123, Get the Absolute Result, and Find the Maximum Value?

[MGe]

Hello everyone,

I have a form that includes a repeat block, and within this repeat block, there is a single decimal field where users will input values. I want to achieve two things:

1. Automatically calculate the subtraction of all these entered values in another field outside the repeat block. If the result is negative, I want to convert it to a positive number (i.e., take the absolute value).
2. Automatically find and display the highest value entered in the repeat block in another field.

For instance, if the repeat block has values like this:

Repeat Block:

1. repeat value = 10

2. repeat value = 5

3. repeat value = 3

The resulting fields should display:

• Total absolute subtraction: |10 - 5 - 3| = 2
• Highest value: 10

I've tried using calculations within the XLSForm, but I'm struggling to find a way to dynamically iterate through the repeat entries and perform the subtraction. Does anyone have experience with this or know of a solution that can be implemented directly in Survey123? Is this even possible to achieve within the Survey123 framework?

If this cannot be achieved directly in Survey123, I'm open to suggestions involving webhooks or other post-processing methods.

Any help or guidance would be greatly appreciated!

Thank you!

[DeonLengton]

Can you perhaps post your excel design file?

[MGe]

Thank you for your fast reply!

Due to data protection regulations in Germany, I can only upload a reduced version of the excel design file. This version includes necessary elements to ensure compliance.

In the repeat section, there are additional decimal values where the same calculation is performed for each. If you need specific data not included, please let me know.

Thank you for your understanding.

[DeonLengton]

Hi @MGe 

Not sure if it solves your problem but see the attached excel.

I was not sure if you always subtract the repeat values from the first repeat value...

In your example you started with 10. I assumed then that you subtract the repeats after that.

You can calculate the Absolute of a value by squaring it and then getting its square root.

Hope that helps

[MGe]

Thanks!

i can show you the formula from the excel table, which should be transferred to survey123. please note that there can be any number of surveyors (Gutachter)

I will try your example and let you know!

[MGe]

The formula for finding the maximum value works perfectly, but unfortunately, the subtraction isn't working as expected. In my test, I got a result of 7 when the value should be 2. Each value should be subtracted from the previous one.

[ChristopherCounsell]

Each value should be subtracted from the previous one.

What about the first one? This needs to be clarified - the first value sets a total, subsequent values are subtracted, then you convert the final number to a positive?

You could do 'repeat 1' minus the sum of all repeats except repeat 1. For ${value}:

indexed-repeat(${a_param}, ${gutachter_loop}, 1) - (sum(${gutacher_loop})-indexed-repeat(${a_param}, ${gutachter_loop}, 1))

Then do another calc to mimic the absolute function:

if(${value}-${value}>0,${value}-${value},(${value}-${value})*-1)

for max value, you can just do max(${gutachter_loop})

[MGe]

Thanks for your answer!

The numbers are independent of each other. 
It could also be | 3 - 10 - 5| = abs(-12) = 12

The second value should be subtracted from the first, then the third should be subtracted from the result, and so on with the fourth, fifth, and so on. This involves values that an appraiser records on-site, followed by another appraiser who comes to measure the same values again. Each entry should simply be subtracted one after another, and the result should be made positive (if the result is a negative number). So, it's just (1)-(2)-(3)-(4)-(5)-(6)-(every other repeat). 

[ChristopherCounsell]

This involves values that an appraiser records on-site, followed by another appraiser who comes to measure the same values again.

If you have people coming and going, are you using the inbox or just editing the same survey?

You could also use the position function and calculate the current repeat value based on the previous repeat value, then update the parent table based on the last result (count the repeats)?

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm#ESRI_SECTION1_AD27BB0...

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformrepeats.htm#ESRI_SECTION1_9F07ECB9...

Or maybe your workflow could be adjusted entirely. If you have people coming and going and calculating within the one repeat, maybe use custom url schemes, arcade and post-submission processing to do the stuff. Keep the complexity outside of the data collection.