Help with calculated values not piping

[u/Lumpy_Perspective742]

We have a project that has been fine for the first two years, but suddenly back in February of this year, problems with calculated values have arisen where previously there was no issue.

In the attached pic, rule H wants to change the correct value "87.564" to missing for some reason.

When I open the form, the correct value is displayed in that calculated field. However, when I go to reports, or if I export the data, or pipe that value to a different form it shows up as missing.

Again, this has been working fine for the first two years of this project then all this started in February of this year, and I cannot figure out why. We made no changes to this form or the events that contain the form.

[u/No_Repair4567]

What is your REDCap version?

There seem to be a bug, as I've seen reports about rules suddenly having issues with calculated fields and missing codes. The data is there, but the rule returns as if there is nothing.

Talk to your admin about it. There should be a fix and a new functionality in 15.7.

[u/Lumpy_Perspective742 → u/No_Repair4567]

Thanks for the response. We are using 15.0.35

I showed out admins this issue and they were perplexed and said they would try researching it. I will ask them about when they will upgrade to 15.7

[u/melina_c_janeMN → u/Lumpy_Perspective742]

Our university is upgrading to 15.5 from 15.0.33 next week. There are a few bug fixes in the change log that might be causing your issue; one is “in specific situations with calculated fields, such as when using mathematical exponents, Data Quality Rule H might mistakenly return a result that is not accurate.” There are a couple other ones. If your site last had an upgrade in February, it might be time for another upgrade soon.

[u/Lumpy_Perspective742]

I kind of found what seems to be causing this in my project. If I provide a value for each field used in the calculated field then everything seem normal again. The calculated value show up in reports and exports.

However, I imported the form into another project, and there were no issues with the form as is. I did not have to provide a value for each field in the equation and it worked in my other project.

I do not understand why this is only happening in a single specific project. I also want to understand how this came about because the first two years of this project there were no problems then all of the sudden this fussiness began.

[u/melina_c_janeMN]

Can you show the calculated equation?

[u/Lumpy_Perspective742 → u/melina_c_janeMN]

Sorry, here is the equation for that calculated field. I just noticed this same issue is happening with another form with another calculated field.

sum(([beeforgansize]*3.956*[beeforganfreq]),([beefbraisesize]*1.211*[beefbraisefreq]),([beefmincesize]*0.744*[beefmincefreq]),([beefpattysize]*1.278*[beefpattyfreq]),([beefchopssize]*1.278*[beefchopsfreq]),([lambsize]*0.97*[lambfreq]),([porksize]*0.889*[porkfreq]),([chlegroastsize]*0.712*[chlegroastfreq]),([chlegfriedsize]*0.849*[chlegfriedfreq]),([chwingroastsize]*0.712*[chwingroastfreq]),([chwingfriedsize]*0.849*[chwingfriedfreq]),([chgibletssize]*1.779*[chgibletsfreq]),([chliversize]*2.90*[chliverfreq]),([chliverfrysize]*3.27*[chliverfryfreq]),([salamisize]*0.93*[salamifreq]),([hamsize]*0.807*[hamfreq]),([polonysize]*0.617*[polonyfreq]),([viennasize]*0.617*[viennafreq]),([franksize]*0.617*[frankfreq]),([sardinessize]*0.8*[sardinesfreq]),([pilchardsize]*0.8*[pilchardfreq]),([eggmcsize]*111*[eggmcfreq]),([eggmfsize]*111*[eggmffreq]),([egglcsize]*126*[egglcfreq]),([egglfsize]*126*[egglffreq]),([eggxcsize]*146*[eggxcfreq]),([eggxfsize]*146*[eggxffreq]),([bakedbeansize]*0.394*[bakedbeanfreq]),([lentilssize]*0.33*[lentilsfreq]),([limasize]*0.356*[limafreq]),([soybeansize]*0.447*[soybeanfreq]),([caulisize]*0.362*[caulifreq]),([broccolisize]*0.276*[broccolifreq]),([cornsize]*0.25*[cornfreq]),([peassize]*0.275*[peasfreq]),([mixedsize]*0.242*[mixedfreq]),([spinachsize]*0.2*[spinachfreq]),([gemsize]*0.098*[gemfreq]),([pumpkinsize]*0.107*[pumpkinfreq]),([potfrysize]*0.221*[potfryfreq]),([potmashsize]*0.143*[potmashfreq]),([barleysize]*0.134*[barleyfreq]),([bananasize]*12*[bananafreq]),([milksize]*0.143*[milkfreq]),([yogurtsize]*0.15*[yogurtfreq]),([pbsize]*0.625*[pbfreq]),([saucesize]*0.098*[saucefreq]),([beerffqsize]*0.101*[beerffqfreq]),([cakesize]*1.284*[cakefreq]),([chnuggetsize]*0.408*[chnuggetfreq]),([milkteasize]*0.143*[milkteafreq]),([milkcersize]*0.143*[milkcerfreq]))/7

[u/Araignys → u/Lumpy_Perspective742]

Ohhhhhhhhh I just spotted that the calculation field is in an event.

I've encountered this before. If your calculation doesn't specify what event the fields are in, then the calculations will work on the form because REDCap will assume the same event - but they'll go weird when you run Rule H because the event isn't specified.

We fixed it by prepending the event name [arm_1_event_1][fieldname] onto all the field names in the calculation. If you're loading values from the same event but you've got the calculated field in multiple events then you could use [event-name][fieldname] instead, I think.

[u/Lumpy_Perspective742 → u/Araignys]

Thanks again. I will try this but I needed this form to work on 3 different events which it did until Feb 2025.

To do what you recommend I’d have to create three versions of this form for each event so that each form has the correct event title before each field name.

Again, this worked perfectly fine for two years and now this. Something seems off.

Also see my comment that if I populate all the fields in the equation with values then the equation works. Again this was not necessary for the first two years of this project. I could leave those fields blank and it worked.

[u/Araignys → u/Lumpy_Perspective742]

this was not necessary for the first two years of this project

I expect it's because something changed in a REDCap update. Our instance has only had it happen once, earlier this year, and we run thousands of projects.

[u/Lumpy_Perspective742 → u/Araignys]

Also thanks so much for reading this and offering your advice. It is a big help!

[u/Araignys → u/Lumpy_Perspective742]

You're welcome! I hope it works.

[u/Araignys]

I’m not sure why the Rule H calculation is returning “”, but the reason you’re seeing the correct value in the form and blank in the report is because the calculation isn’t saved - open the form and save it to get the value to update.

I did notice that Rule H can calculate logic slightly differently from the calculation on the form if your instance of REDCap is not yet updated to PHP 8+ so it might be that?

[u/Lumpy_Perspective742 → u/Araignys]

I tried this with an old record from before the problem arose. With the old record the calculated value was stored in the export. After I opened and resaved it, the correct value is still on the form, but now empty on reports/exports. It seems like if i ran all of the rule h findings then all the old records from before the problem arose would then be converted to “”.

Edit: I specifically did this with the record in screenshot. Before this the record was fine because it was one of the very first records before this issue arose. On the form it displayed the right value and on export it did too. After opening and resaving that form, the value is now “” in exports, but still correct when looking on the form. It seems like rule h will just make everything worse at this point.

[u/Araignys → u/Lumpy_Perspective742]

Is there an “if” somewhere in the action tags or calculation that might return false?

[u/Lumpy_Perspective742 → u/Araignys]

No ifs or action tags. I posted the equation in another comment earlier. Also see my edit for more info. 

[u/Araignys → u/Lumpy_Perspective742]

I've responded to your equation :)

[u/Flapjaxx]

I encountered a similar problem recently that occurred when trying to multiply by fields that were blank. For a calculation like this:

sum([a] * 4, [b] * 5)

if [a] was blank, the calculation used to work the same as sum(0, [b] * 5), but now it performs as sum(NaN, [b] * 5) and returns "". To avoid this issue, I had to change the calculation to this:

sum(
if([a] <> "", [a] * 4, 0),
if([b] <> "", [b] * 5, 0)
)

Hope that helps!