Calculation question

Question

I have a survey that is long and has multiple calculations in it.  It was working fine until I updated to the latest version on Survey123 Conntect, was there something in this last update that could have potentially affected the calculations in the form? The form is attached.

Damage Assessment.xlsx

SarahLinn1 · Answer

Thank you Phillip. That worked great.

Anonymous User · Answer

Hi Sarah,

I was able to find why the M312 calculation appears not to be working. The reason is due to that in the 3.6 we fixed a bug (BUG-000097105) where default values in non-relevant questions where being stored incorrectly, this should not occur as the question is not relevant so should not have any value until it becomes relevant. In your case, you are using a default of 0 for all your calculation questions in each group and then trying to sum them all together, even if they are not relevant. This worked in 3.5 or earlier, as all those questions would have had a value of 0 stored even when not relevant, however now with 3.6, the non-relevant questions will be null.

In M312 you are simply adding the values together using a simple addition expression (value1+value2+value3...etc), where each question much have a value, as you can not add a value and null together. In 3.6 the expression will not work as you are relying on the answers from all the non-relevant questions.

The correct way to construct the M312 calculation is to use coalescse(${calc1},0) for each question you want to add together in the final total calculation. Updated calculation below for M312, I tested this out with your form and works as expected.

I see you asked this same question back in April 2019, and James replied with the same explanation and suggestion to use coalesce(): Survey123 Calculations not calculating

Secondly, I noticed you are using relevant expressions on both the begin group and questions in the group with the exact same expression. This is no longer required, with 3.6 we fixed an issue where the questions inside a group were not inheriting the relevant expression of the begin group correctly. This now works, so you only need the expression on the begin group, and all questions will become relevant and be displayed when the group becomes relevant. By removing all the un-needed expressions on all the questions, this speeds up the form load time, I noticed it was 1-2 secs faster.

Thirdly, I also noticed that you have used the minimal appearance on decimal and hidden question types. This appearance does not apply to these types of questions so should be removed to avoid any unforeseen issues.

I have attached the xlsx file I modified and it appears to be working with some of those improvements.

coalesce(${CalcRCP24Cost},0)+coalesce(${CalcRCP18Cost},0)+coalesce(${CalcCMP36Cost},0)+coalesce(${CalcCMP24Cost},0)+coalesce(${CalcCMP18Cost},0)+coalesce(${CalcHDPE36Cost},0)+coalesce(${CalcHDPE24Cost},0)+coalesce(${CalcHDPE18Cost},0)+coalesce(${CalcCleanCulvertOver24Cost},0)+coalesce(${CalcCleanCulvert0_24Cost},0)+coalesce(${CalcPavementExcavationCost},0)+coalesce(${CalcExcavationCost},0)+coalesce(${CalcDebrisHauledCost},0)+coalesce(${CalcCatchBasinCost},0)+coalesce(${CalcDitchCost},0)+coalesce(${CalcBoxGuardrailCost},0)+coalesce(${CalcSteelGuardrailCost},0)+coalesce(${CalcRemoveGuardrailCost},0)+coalesce(${CalcRipRapHeavyCost},0)+coalesce(${CalcStoneFillTypeIVCost},0)+coalesce(${CalcStoneFillTypeIIICost},0)+coalesce(${CalcStoneFillTypeIICost},0)+coalesce(${CalcStoneFillTypeICost},0)+coalesce(${CalcBitConcreteSurfCourseCost},0)+coalesce(${CalcsubbaseCost},0)+coalesce(${CalcGranularBorrowCost},0)+coalesce(${CalcGranularBackfillCost},0)+coalesce(${CalcAggSurfaceCourseCost},0)+coalesce(${CalcGrubbingCost},0)+coalesce(${CalcPortlandCementSidewalkCost},0)+coalesce(${CalcConcreteClassBCost},0)+coalesce(${CalcStoneDitchProtectionCost},0)+coalesce(${CalcTopSoilSeedingCost},0)+coalesce(${CalcDisposeGuardrailCost},0)+coalesce(${CalcFlaredTerminalSectionCost},0)+coalesce(${CalcTangentTerminalSectionCost},0)+coalesce(${CalcTrafficBarrierCost},0)+coalesce(${CalcMoveTrafficBarrierCost},0)+coalesce(${CalcDebrisROWChipsCost},0)+coalesce(${CalcChannelExcavationCost},0)+coalesce(${OtherEstimate},0)

Hope this helps.

Regards,

Phil.

Damage Assessment.xlsx

SarahLinn1 · Answer

Hi Phillip,

Specifically the calculation in the M312 cell -  CalculateTotalEstimate is not working.  There is not an error message it's just not calculating.  The confusing part is that I haven't changed anything in XLS.  The only thing that has changed are updating the version of Survey123 Connect and updating to the latest version of the app and iOS.

What Connect version was the survey published with? I am not sure which version the survey was published with. I published it a while ago, and what app version are you using? 3.6.155 Also on what OS? iOS version 13.1.2 Does the same issue happen on all OS? I've used this survey on multiple devices and am having the same issue.