Survey123 Calculations not calculating

[SarahLinn1]

At one point the calculation field (line M309) was calculating.  Now it won't work and I cannot figure out why.

I had to make schema changes and that's when it stop working.

I made the schema changes at the database level, overwrote the existing feature service and added the new fields to the survey.  Those news fields have nothing to do with the calculation but that's when it stop working so maybe it's connected.

I've read this post, https://community.esri.com/thread/213589-calculate-not-working-with-survey-27>  

Wondering if maybe it has something to with the relevancy/calculation issue mentioned above?

[JamesTedrick]

Hi Sarah,

I don't see a calculation on line 309 of the excel sheet (EditedBy field), nor can I find 'M309' in a text search of the Excel file - can you provide the line number of the nonworking calculation?

[SarahLinn1]

It's line M305, sorry about that. It's long, but I copied it here in case that's helpful.

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

[JamesTedrick]

The calculation involves a large number of fields, many of which aren't going to be displayed - this causes an issue as those values would be null and null+any value = null.  You can use the coalesce() function to catch null values and substitute a neutral (0) value:

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)

[Anonymous User]

This was exactly what I needed to fix the problem I was having - thank you!!!