I was looking at this work around for calculating fields that may have possible null values, per this example that statement is within the calculations field, but I already have a calculation there summing total amounts from a repeat group. Would I still write this IF statement in the calculations field including the sum equation or would it also work in the constraints field? I copied the support document for the work around and a snap shot of the fields I need with this IF statement.
This is for the total acres of chemicals used for spraying weeds calculation:
${amtapp} div ${apprate} |
This is for the total release in acres of biocontrol agents for weed control calculaiton"
sum(${bioamt}) |
These are the fields being calculated for the total acres being treated, summing total chemical acres + total biocontrol acres:
sum(${chemacres}) |
sum(${biosum_count}) |
Since not all of these will be used at once and I can't get the "Total Acres Treated" to calculate if any of these are null, (and can't count on field crews to remember to say '0') nor can I default these to 0 with the calculations being in the first two equation I mentioned about, I'm wanting to create the workaround for these null fields so the "Total Acres Treated" still calculates.
TIA
Check out this function
coalesce(value1, value2)
Returns the first nonempty value. This function supports only two values.
coalesce(${question_one}, ${question_two})
You can use this inside the repeat so that it is either 0 or null then your final sum will work.
Hope that helps
So I was able to create a '0' default for both "Total Biological (acres)" with the equation "sum(${biosum_count})" and "Total Mechanical (acres)" with equation "sum(${mechaacres})", but the "Total Chemical (acres)" with the equation "sum(${chemacres}), highlighted in yellow, I couldn't get to '0' out and so will still show a null value, therefore my "Total Acres Treated" formula will not calculate .... would I write the coalesce(value1, value2) within the other highlighted area (the equation for "Acres Treated (Chemically)" with equation ${amtapp} div ${apprate}? That's the question used to populate "Total Chemical (acres)" or would I use that function where the equation for "Total Chemical (acres)" is? Or would these be listed under the ${amtapp} and ${apprate} questions (these questions are located under the Mixing information group), which are used to get the "Acres Treated (Chemically)"?.
Sorry hope this makes sense, I'm very new to all this and am pretty much teaching myself how to do surveys and deal with these equations from scratch. I appreciate the help.
Not sure which may be blank? One or both?
But it would look like
coalesce(${amtapp},0) div ${apprate}
That didn't seem to work. I did try with apprate being coalesce and then tried both and none of those worked.
I did try to default those questions, amtapp and apprate to 0, but that's not doing it either.
I may just set the "Total Chemical (acres)" question to be required so at least they know they need to populate it.
But if you have other suggestions to try.... otherwise thanks again for your help!
Are these of calculate type? If so try setting the bind:type column to the correct data type since the form defaults to text that can get you. If that still does not work post the form something else is up.
They are all decimal types.
If you can post or send me the form in a message I can look. Something else is up