[DONE] Calculation not summing Higher Values of related records

🔧 Bugs & Issues
1

Hello Community.

I’ve been trying to find out whats going on at a calculation field which should sum values of defined Status in a related app.

Here are the values filtered:

image
image678Ă—582 43.6 KB

But the calculation is not bringing the same value as the sum:

image

Here is the code I am using (pretty basic, but I tried to treat the numbers formating also, and couldnt reproduce the actual sum for this case):

var val = @All of Total Budget Amount R$ (Main);

var tip = @All of Status;

var total = 0;

for(var i = 0; i < val.length; i++)

{if((tip[i] == "Billed" || tip[i] == "Approved")){total += val[i];}}

total

There is one specific record that is missing to the sum, which is the higher value (more then 1M), but why does it sum by the bottom correctly, but not on calculation?

2 Likes
2

Hi @toni — thanks for the detailed report with screenshots, that was really helpful for diagnosing this.

We took a close look at your calculation and I believe I’ve identified the issue. The good news: your data is correct and the bottom SUM is right. The issue might be a subtle pitfall in how the script references work when you use multiple @All of arrays together.

What’s happening:

When you write:

var val = @All of Total Budget Amount R$;
var tip = @All of Status;

Each of these arrays is built independently from the related records. Critically, if any related record is missing a value for one of these fields (e.g., a record has a budget amount but no status set, or vice versa), that null entry is silently removed from the respective array.

This means val and tip can end up with different lengths, and their indices no longer correspond to the same records. So when your loop checks tip[i] to decide whether to include val[i], it may be comparing values from completely different records.

In your case, it appears one of your related records (likely the one with the R$ 1.357.402,79 value) is either missing a Status or there’s another record missing a budget amount — causing the arrays to shift out of alignment.

Potential fix:

Use the “with null” variant of the relation references. In your calculation field editor, when inserting the field references, select the option that preserves null values. This ensures both arrays maintain the same length and index alignment, with null in place of missing values. Then add a null guard to your loop:

var val = @All of Total Budget Amount R$ (with null);
var tip = @All of Status (with null);

var total = 0;
for (var i = 0; i < val.length; i++) {
  if (val[i] !== null && (tip[i] == "Billed" || tip[i] == "Approved")) {
    total += val[i];
  }
}
total

This way, val[i] and tip[i] will always refer to the same related record, and your conditional sum should match the filtered SUM at the bottom.

Also worth double-checking: make sure every related record in that view has a Status value set — if any are missing a status, that’s what’s causing the misalignment.

Hope that helps — let us know if you run into any further issues!
Cheers to :brazil:
Tim

2 Likes
3

OKAY NOW I SLEPT ON THE FLOOR.

hahaha

I’m always pushing the team to use “with nulls” now I felt in my own trick… hahha @FelipeK

Tks Tim, sorry for that…

BTW, in which situation would we apply not to use this “with nulls” value. On loops for example, it is very easy to mess all data if any field is missing…

For tables, if you miss this you’ll print a very misaligned table.

So, wouldnt it be good that the first option would be this and the second option when searching dor fields to reference would be “not empty” or “without nulls” idk…

Just thinking if the most secure usage wouldnt be “with nulls” in most cases.

Anyways, it incredbly solved the issue! haha

Thank you for your time and effort on explanation, and sorry, I knew it all, forgot this time! haha :upside_down_face:

3 Likes