Constrain Total Value of Repeat Entries

[AndrewShuman2]

I have a survey that is trying to track work hours done on a variety of tasks. Each different task is set on a repeat. Each worker must account for 8 hours in a day. 

Is it possible to sum the value of the entries and constrain it so that the sum of the entries must be over 8?

This is how I have it set up now:

type	name	label	calculation	constraint	constraint message
calculate	total_hours	Total Hours	${hours_admin}+...{hours_yardwaste}	${total_hours}>=8	Must Enter 8 Hours

What am I missing? I have attached my xlsx.

[AndrewShuman2]

I don't know if my xlsx attached before. 

[DougBrowning]

Probably because some entries are blank.  A blank gets '' which cannot be added since it is not a number.

Usually use coalesce for this  coalesce(${hours_yard_waste_collection}, 0)  this way it is is empty it gets a 0 instead.

Hope that helps

[AndrewShuman2]

Do I need to apply that individually to each answer in the Calculation, or in total as I've shown in the updated xlsx?

[DougBrowning]

Nope have to do it for each one.  Copy paste and it should not be too bad.  Always good to test in a temp field to make sure before doing it all.

[AndrewShuman1]

Sorry, replying back with a work account now. I guess I'm not clear where I am supposed to be adding the Coalesce function. Is it each individual Calculation field? Is it Total Hours Calculation field. I've tried both and I am not having success. Adding in the Coalesce to the total_hours Calculation field is the only place where I have gotten Connect to accept it.

I've attached the sheet for reference.

[DougBrowning · Accepted solution]

I changed the field to a note and it all worked.  It will not fire on constraint on the field that is just a calculate since it does not show on the screen.

As you see above though I do not think this is what you want as this requires each task to be 8.  You need a total outside the repeat and a constraint on that. And you need to bind to decimal otherwise 123 thinks its a string.  (or use decimal then hidden appearance) See attached.

But in the end why are you making one hours field for each type?  This form could be way, way simpler just having a hours field.  See second attached.  You repeat table will show which task it was.  Going forward your original schema is going to be a nightmare to use.  Basically you have to check all those fields all the time.  Just use one hours field is the better schema way to do it.

Hope that helps

[AndrewShuman1]

Perfect! That works great! Thank you.