how are Calc fields handled when editing a submission

Hi,

By chance I noticed that calc fields do not seem to update when editing a received submission. See the screen shot attached.

We had a new school participate. I went to delete the Other entry and add them to the master list for all forms so the students wouldn't have to use Other (and to help me with data parsing later).

As I scrolled to the Submit button, I spotted the various calculations in the totaling fields. Submit sent them as displayed. 

I went back in and tabbed through all the fields. This didn't change anything. 

When I did it again, I re-selected the participant type (student, etc), which I'm guessing is the trigger for the calculations. This time is corrected the calculations. 

Is this normal? It isn't a big deal if so, I just want to know what to expect when the deadline gets close and I'm swamped with submissions.

Thanks!

jim

The calculation should only update if there is a change to a field being used in the calculation.  Using any fields associated with the calculation will update to the new total.  If no field is accessed, the value used for the calculation total should remain the same.

Thanks David,

Based on your answer, I guess there's a bug in the system.

My assumption of functionality is that all unchanged data remains unchanged - including any calculations built into the form. If I change Smith to Jones, only fields that are tied to that entry should update.

Take a look at the Total All Attending and Total Male in the screen shot I sent. One shows a formula. The other appears to have a string of values and then a sum. Neither are correct.

The form initially submitted correctly with the counts from the school. When I edited a spelling issue, all else should have stayed the same. Thankfully, I noticed the bad data after the re-submit.

The wonky values corrected themselves when I re-entered all the Type fields. I assume this forced a re-lookup on the calc fields. The 2nd edited submit worked.

On a more complex form, having to go through all entries to keep data straight will be a nightmare. With this one, my worst case is a couple hundred tabs. That isn't horrific, but it isn't fun.

jim

I checked your form and upon load of the form I noticed that the initial value for your "Total All Students Attending" field is If (0=1) +1, +0), If (0=1, +1,+0) and the initial value for "Total Male Students" field is 1100110.

As per further checking, it seems the issue is on the "Total All Students Attending" field. Can you try the following steps to fix the issue:

1. Open "Total All Students Attending" field widget wizard.

2. Click Finish without changing anything.

This way the calculation formula for the widget/field will be updated/refreshed. When the form loads, the calculation fields are now set to 0 by default.

Hi,

Thanks for checking into this. I opened the form and the calc fields. Each was saved without changes. Both show the wonky results on an edit. If I save the edit, the bad values are stored in the database.

I went through the form one field at a time and watched the calc results. It takes changing one student to something else and back to student (I hit S twice to toggle between sponsor and student) to make the calculations correct themselves.

The good news is I only have to edit one field to fix this when needed. The bad news is there's something wrong that shouldn't need a workaround. 

Because I'm dealing with humans, the chances of an entry error is huge. I get emails from the teachers to correct a spelling error. I also edit the form when a new school applies. The Other entry messes up the database so I add the new school as soon as it appears. Otherwise, I have a bunch of students registering with Other and lots of variations on the school name.

I went through the calc field calculations and they're all correct. I thought about erasing the calculation and starting over, but if each value is correct, that shouldn't be the issue.

I can keep manually correcting for now, but there's definitely something that could cause issues for others.

Thanks for your continued help.

jim

I did some testing with a basic form and the calculation values were retained when editing submissions.  The incorrect values are shown even in the form without any edits:

Also, the female students do not seem to be calculating correctly.  I will look into this and see what the cause is.

ok - thanks for looking further. As long as Student is selected at least once in the form, the values seem to be correct. From my testing, Total Female was working. I have a handful of submissions now and will go through them manually to make sure. 

If the calculation values are used instead of the conditionally updated fields, there is not longer any issues with the calculations:

Here is a test form with the gender and type values used instead of the updated fields:

https://form.jotform.com/63055664889976

There errors are no longer present and the values are retained when editing.  There are quite a few conditions so I am not sure which update is causing them, but I am fairly sure the conditionally updated values are not being input correctly in one or more of the hidden fields somewhere.

Thanks, David!

I think your solution won't work. At first glance, I believe you are counting every attendee for gender. I'll have to import the form into my system and go through it to verify.

I need to know Gender Count for just students. I couldn't figure out a way to separate out students from the others without the conditional testing.

We allocate rooming based on the number of boys and girls from a school (and can't mix). The non-students could be spouses as sponsors and chaperones where we can mix genders in a room. That part is done by hand.

My quick test on your form added a chaperone to the F Student count.

The nested If gives me Type=Student AND Gender=F to trigger the +1. 

From what I see, involving logic is what breaks the edit. In FileMaker, the references stay updated automatically. I'm guessing your system has to have a call for the recalculation.

The only other way I see to make this work is to have a ton of extra fields so each possible permutation yields a value. The summary field would count StudentGender and NonStudentGender. It'll require hundreds more fields and math.

I can live with knowing I have to edit the Type field one time whenever I edit for anything. It just isn't pretty. I assume there are other users that have complicated summary needs where this could cause a problem. 

If you see another way to split out a subset of a specific type in a record without conditional testing, let me know.

Jim

Since you would only want to have and count the genders of students, you could conditionally show the gender fields only if student is selected.  That was, it is counting the students and only counting the genders for students.  I can adjust my form to show how it would work if I get a bit of extra time.

I tried that but our registrar needed more detail to do her job. I was so proud of myself for splitting out the students like you suggested and then she hit me with her request.

While we don't need to count the nonStudent genders, I do need to log them. Not all of the adults are spouses. If I get a female teacher bringing someone's dad as chaperone, we need to know genders for that. We would put two women or two men in a room together and make that call manually.

There too many crossover names to guess. Pat, Jo, Morgan, Jordan, etc.

For students, we have 3, 4 & 5 bed suites. If the school is bringing 3 boys and 5 girls, we can assign rooms at a glance. If they have a single boy, we can room him with a school bringing 2, 3, or 4 boys.

jim

Without redoing each condition, it is hard to tell what specifically is causing the equations to show incorrectly.  If I get a bit more time, I will check through with each to see what the cause is.  Calculations are storing the the correct value properly and if your method of updating is working in the mean time, I would say leave it as is for now.