Automation Error - Skipping fields with no entry

[Travis Hill]

Im working to sum up several custom fields given some criteria

• The number needs to be less than 200
• The field should be skipped if there is no number there.
• For example if the numbers in the four of the fields are 1,null,2,900 the answer should be 3 (skipping the null and skipping the 900)
• I have the first bullet point figured out and I can sum everything that has data while skkipping things over 200, but my issue occurs when something doesnt have an entry in the field (null).

Any ideas?

Here is what im running right now. Basically check if the field is less than 200, if it is print it and add to the next number:

{{#if(customfield_12863.lte(200))}}{{customfield_12863}}{{/}} + {{#if(customfield_12869.lte(200))}}{{customfield_12869}}{{/}} + {{#if(customfield_12859.lte(200))}}{{customfield_12859}}{{/}}+ {{#if(customfield_12848.lte(200))}}{{customfield_12848}}{{/}}

Thanks in advance!

[Bill Sheboy · Accepted solution]

Hi @Travis Hill 

For a math expression which uses conditional logic, you could handle missing values with:

1. additional conditions for empty values, which can be problematic to identify for some field types, or
2. add default values before the expressions

As an example of the second option, add 0 values before the conditional checks:

{{#=}}
   0{{#if(customfield_12863.lte(200))}}{{customfield_12863}}{{/}}
 + 0{{#if(customfield_12869.lte(200))}}{{customfield_12869}}{{/}}
 + 0{{#if(customfield_12859.lte(200))}}{{customfield_12859}}{{/}}
 + 0{{#if(customfield_12848.lte(200))}}{{customfield_12848}}{{/}}
{{/}}

This prevents the conditions from collapsing to null and breaking the math expression.

Kind regards,
Bill

[Travis Hill]

Thanks Bill, appreciate the response. In the second instance, if I add a default value before the conditional checks, does it add the 0 to the field itself? Or does it just skip if there is no value in the field? The reason I ask is that this set up is for a ranking mechanism, so automatically adding a 0 to the field would be bad news because it would default everything to the top of our lists.

[Bill Sheboy]

Thanks for the additional context, Travis.

I was adding a 0 as a default value before the conditions as you are using math expression.  If some other default value is needed for your context, that value will decide how to proceed.  For example...

• If this is a numerical ranking, where 0 is "higher rank" than 1, you could add a very large default, such as 99999
• If this is some other form of ranking, using a math operation may not be appropriate for this case.  That is, if the value for a field such as custofield_12863 should not contribute to the ranking, what does that look like in "ranking"?

[Travis Hill]

This is helpful thank you. Ill mess around with this a bit and see what happens.

[Travis Hill]

Alright, @Bill Sheboy this worked! Thanks for your help. My follow question is, is there an easy way to then divide by count. So for example if 2 of the 4 fields are under 200 and not blank then my answer to the above formula should then be divided by two. Or would that be a seperate automation rule thats triggered at that point in time?

[Bill Sheboy]

I am glad to learn that helped, and for your second question...

You could use a similar syntax to count them for division.  Perhaps like this, using my same example from above, were I substitute 1 for the values in the conditions:

{{#=}}
(
   0{{#if(customfield_12863.lte(200))}}{{customfield_12863}}{{/}}
 + 0{{#if(customfield_12869.lte(200))}}{{customfield_12869}}{{/}}
 + 0{{#if(customfield_12859.lte(200))}}{{customfield_12859}}{{/}}
 + 0{{#if(customfield_12848.lte(200))}}{{customfield_12848}}{{/}}
 ) / 
(
   0{{#if(customfield_12863.lte(200))}}1{{/}}
 + 0{{#if(customfield_12869.lte(200))}}1{{/}}
 + 0{{#if(customfield_12859.lte(200))}}1{{/}}
 + 0{{#if(customfield_12848.lte(200))}}1{{/}}
)
{{/}}

Essentially this: {{#=}} (sum of the values ) / (count of the values) {{/}}

If the denominator could ever be zero, you may want to first store the count in a variable to handle the edge case.

[Travis Hill]

Awesome, Ill test this out. The denominator technically shouldnt ever be zero, or if it is, it is prob ok because that would mean it is unranked or ranked at 999 both indicating that the work should not be prioritized. Ill let you know how my test goes

[Travis Hill]

@Bill Sheboy Hmm interesting im getting this error: Failed to close '=' tag

But I cant tell what Im doing wrong.

{{#=}}

(

   0{{#if(customfield_12863.lte(200))}}{{customfield_12863}}{{/}}

 + 0{{#if(customfield_12869.lte(200))}}{{customfield_12869}}{{/}}

 + 0{{#if(customfield_12859.lte(200))}}{{customfield_12859}}{{/}}

 + 0{{#if(customfield_12848.lte(200))}}{{customfield_12848}}{{/}}

+ 0{{#if(customfield_12919.lte(200))}}{{customfield_12919}}{{/}}

+ 0{{#if(customfield_12866.lte(200))}}{{customfield_12866}}{{/}}

+ 0{{#if(customfield_12857.lte(200))}}{{customfield_12857}}{{/}}

+ 0{{#if(customfield_12852.lte(200))}}{{customfield_12852}}{{/}}

) /

(

   0{{#if(customfield_12863.lte(200))}}{{1}}{{/}}

 + 0{{#if(customfield_12869.lte(200))}}{{1}}{/}}

 + 0{{#if(customfield_12859.lte(200))}}{{1}}{{/}}

 + 0{{#if(customfield_12848.lte(200))}}{{1}}{{/}}

+ 0{{#if(customfield_12919.lte(200))}}{{1}}{{/}}

+ 0{{#if(customfield_12866.lte(200))}}{{1}}{{/}}

+ 0{{#if(customfield_12857.lte(200))}}{{1}}{{/}}

+ 0{{#if(customfield_12852.lte(200))}}{{1}}{{/}}

)

{{/}}

[Bill Sheboy]

Just use 1 and not {{1}} in that expression and you should be all set.

And...do you actually want to use customfield_12848 5 times in that expression for the conditions, or should it use the other custom field ids / values: 12919, 12866...?

[Travis Hill]

@Bill Sheboy Good catch, thank you. I updated it but still catching that error:

{{#=}}

(

   0{{#if(customfield_12863.lte(200))}}{{customfield_12863}}{{/}}

 + 0{{#if(customfield_12869.lte(200))}}{{customfield_12869}}{{/}}

 + 0{{#if(customfield_12859.lte(200))}}{{customfield_12859}}{{/}}

 + 0{{#if(customfield_12848.lte(200))}}{{customfield_12848}}{{/}}

+ 0{{#if(customfield_12919.lte(200))}}{{customfield_12919}}{{/}}

+ 0{{#if(customfield_12866.lte(200))}}{{customfield_12866}}{{/}}

+ 0{{#if(customfield_12857.lte(200))}}{{customfield_12857}}{{/}}

+ 0{{#if(customfield_12852.lte(200))}}{{customfield_12852}}{{/}}

) /

(

   0{{#if(customfield_12863.lte(200))}}1{{/}}

 + 0{{#if(customfield_12869.lte(200))}}1{/}}

 + 0{{#if(customfield_12859.lte(200))}}1{{/}}

 + 0{{#if(customfield_12848.lte(200))}}1{{/}}

+ 0{{#if(customfield_12919.lte(200))}}1{{/}}

+ 0{{#if(customfield_12866.lte(200))}}1{{/}}

+ 0{{#if(customfield_12857.lte(200))}}1{{/}}

+ 0{{#if(customfield_12852.lte(200))}}1{{/}}

)

{{/}}

[Bill Sheboy]

I see a missing left-curly bracket at the end in the second line of the denominator (for customfield_12869).

 + 0{{#if(customfield_12869.lte(200))}}1{/}}

should be this

 + 0{{#if(customfield_12869.lte(200))}}1{{/}}

Stuff like this is tricky unless you use an IDE / code editor which can match up brackets, parenthesis, etc.

[Travis Hill]

@Bill Sheboy this fixed it and the solution seems to be working now. You are a god among men Bill. Thank you.

[Tobias H]

Hi @Travis Hill! 

I'll preface this with that my smart value math if very limited, but I did run into a similar problems when I had to add sums together in an automation some months ago.

As you said yourself, if the field is empty on submission, it fails. You should add an else statement either in the automation component or in the smart value to add a 0 to empty submissions. Then instead of failing on nulls, it will do +0s which won't change your final sum.

TL;DR:

Either use Else component to say "If empty then set 0"
Or try to work it into the math value if you have the knowledge :)

[Travis Hill]

Thank you Tobias, appreciate the response. Unfortunately I cant default everything to 0 (or any other number) in this instance because the fields are used as a ranking mechanism so it would potentially impact our prioritization lists if we default to another value