After updating I returned to my personal budget form and noticed that the TOTAL field type isn’t calculating totals based on a sum of all the other fields in the calculation. It turns out that it won’t return a value unless there is something entered in each field involved in the calculation. More specifically, you can’t leave a field blank, rather you must manually enter a 0 in each field or set up a default value of 0 in the first place.
This is a problem because even the TOTAL fields are summed which tells me how much money I should have in the bank. I can’t go back and enter zeros in hundreds of records/fields nor would I want to as it would look messy.
Please tell me this can be fixed. I really don’t want to start again.
I’ve attached an example…
Attachments:
You must be
logged in to view attached files.
You should add some IFEMPTY() checks to your formula to solve this problem.
IFEMPTY(Groc, 0, Groc)
If the value of Groc is empty, return 0, otherwise return its proper value.
That’ll solve the problem.
I had to make it work this way because previously empty values were treated as 0, but that prevented the IFEMPTY() and IFNOTEMPTY() functions from working.
Ok, well a bit of a pain but I’ll give it a shot.
Where would you place the checks in relation to the rest of the formula? I tried it this way (see attachment) and it didn’t do a thing.
Attachments:
You must be
logged in to view attached files.
Well that’s not going to work. Basically wherever you were referencing your field that could possibly be empty, replace it with the IFEMPTY(field, 0, field) check.
Ok got it. It’s an ugly formula, but it works. I put the revised formula below in case someone else has this problem. If you were creating this form from scratch, I’d suggest setting a default value as “0” so that there’s something in each record in each field. I suspect it might throw some people off though.
Attachments:
You must be
logged in to view attached files.
An alternative would be to have filled all empty values with 0, then set the default value to 0 for new records, then you wouldn’t have to adjust the formula. You can use the multi-column list view with the Fill Down function to easily set the empty values to 0.
Another alternative is add an number field in calculation with value: –
Example:
a+b-c
Where “c” is empty
stravedere,
Could you please elaborate on what you have in mind? grooveagent’s original problem was that calculation totals show blank whenever any of the contributing values is blank. How does adding another blank field fix that?
Thanks,
Mike
Mike, add “-0” in TOTAL calculation fields fix the problem. Now you can leave blanks fields.
A+B+C-0
.
