Need help with a calculation please

Hi all. I need some help please as I want to perform a calculation but I think it will need to be done in javascript but I have no idea how to write javascript.

I have a table (below) that records weight for neonate kittens. What I would love to include is a fields that calculates the difference between the weight entered for the current row to the prior recorded weight for the same kitten. So, for example, for Fonzie, for the weight entered on 07/03, it is +16g compared the prior recorded weight on 06/03. The issue is 1) we may record more than one weight for the kitten per day (depends on how critical they are) and b), they may be entered out of order so couldn’t just say look back for the prior entry for this kitten. Mostly likely they would be entered in order, but they may not. And c), sometimes we may not record a weight.

The difference may be +/- and, ultimately, when we go to the paid version, I want create an automation that if it is - it will email the foster coordinator with an urgent alert… but that is down the line. Right now I just would love some help on how to code this - at least I assume it would need to be a javascript??

Thanks in anticipation :grinning_cat:

You don’t need JavaScript, you can do it with a Query column as follows:

  • Create a Query column in the same table, target the table, and apply the following filters:
    – CatsID is This row->CatsID
    – Date is before This row->Date
    – Weight is not empty
  • Order the Query by Date in descending order
  • Next add a Single Value column, target it at the Query column, and select first->Weight. This will give you the prior recorded weight for each cat.
  • Now you can use a simple Math column to determine the difference (Weight - Single Value Weight)

Below is a video that demonstrates the general technique.

2 Likes

Oh wow! Ok, cool! I will have a crack! Thanks so much!

oh, one thing I missed - to cater for the fact that you don’t always record a weight, add a 3rd filter condition to your Query:

  • Weight is not empty

(I updated my earlier reply to reflect the above)

Thanks Darren. I’ve tried that but I’ve hit a snag in the Single Value column. I don’t have any option to select first>Weight. Here is what I get:

Which I assume is why my third column isn’t working as no WeightSort??

This is wrong :down_arrow:

You need to target the Query column directly, not via the table. You should see it near the top of the list.

1 Like

Thank you so much, Darren! I got it working! This is fabulous! I’m loving Glide and I’m only scratching the surface as I’m a complete newbie when it comes to all this stuff!

1 Like

Nice, well done :+1:

All thanks to your excellent instructions and patience.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.