Survey123 Calculations not calculating

801
4
04-19-2019 08:05 AM
SarahLinn1
New Contributor II

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?

Tags (2)
0 Kudos
4 Replies
JamesTedrick
Esri Esteemed Contributor

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?

0 Kudos
SarahLinn1
New Contributor II

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}
0 Kudos
JamesTedrick
Esri Esteemed Contributor

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)

by Anonymous User
Not applicable

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

0 Kudos