Subtracting last two created linked record

[David_Harris1]

I am keeping track of an inventory of dogs in foster homes.
We try to weigh these dogs almost every week.
The weigh-ins are recorded in a separate table. That table has weight , dog name and weigh in date.
I want to be able to automatically subtracts the last two weigh in to know if the dog lost or gained weight.
I am assuming it could be done through a roll up column. If so how do I select the only the last two weigh ins
Thank You

[Kamille_Parks]

Your request was extraordinarily difficult for me, but I believe I accomplished what you’re asking for.

Airtable

Subtract Last 2 - Airtable

Explore the "Subtract Last 2" base on Airtable.

There are several formula fields in the [Weigh Ins] that make it possible. Essentially, the table finds each record (a weight check for a dog)'s date and logged weight and concatenates them together. Then, it uses that concatenation to find the weight log that immediately precedes it using Rollups and LookUp Fields in addition to another Formula field. Then, in another formula field, it finds the wight gain or loss.

The formula used to get the previous weight log (reported by the PriorLog field)is:

SUBSTITUTE(
  SUBSTITUTE(SUBSTITUTE({AllFinds}&"",RIGHT(AllFinds&"",LEN(AllFinds&"")-FIND(ThisLog,AllFinds&"")-LEN(ThisLog)+1),""),","&ThisLog,""),
  LEFT(
    SUBSTITUTE(SUBSTITUTE({AllFinds}&"",RIGHT(AllFinds&"",LEN(AllFinds&"")-FIND(ThisLog,AllFinds&"")-LEN(ThisLog)+1),""),","&ThisLog,""),
    FIND(
      RIGHT(LEFT(AllWeeks&"",FIND(ThisWeek,AllWeeks&"")-2),6),
       SUBSTITUTE(SUBSTITUTE({AllFinds}&"",RIGHT(AllFinds&"",LEN(AllFinds&"")-FIND(ThisLog,AllFinds&"")-LEN(ThisLog)+1),""),","&ThisLog,"")
  )-1),
  ""
)

Feel free to copy the base and adjust it to fit your needs. It sounds like for your needs you could hide all formula fields in the [Weigh Ins] table other than the {Change} field.

[bdelanghe]

@Kamille_Parks I find your formula beautiful. But I an alternative that is a bit more simple.

Airtable

Subtract Last 2 Alternative - Airtable

Explore the "Subtract Last 2 Alternative" base on Airtable.

This works by finding the latest weigh-in and creating finding that weight. Then removing that date. Finding the next latest date and finding the associated weight. Then subtracting the two if the dog has been weighed at least twice.

For finding only a few records I like this method more than using lists, but when there are quite a few records I’d definitely go with string manipulation.

[Kamille_Parks]

When I got to the 4th Substitute() I just knew there had to be another way :joy:

Well, @David_Harris1, you’ve got options now!

[W_Vann_Hall]

…and another option.

This is the simplest method, but it assumes weigh-ins are always recorded sequentially — that is, a dog’s weight for yesterday is always recorded before the weight for today. (It also assumes no dog weighs more than 999.99 pounds, but that seemed a reasonably safe assumption.)

There’s nothing fancy here: I simply convert date and weight to text strings, making sure weight is always saved in 000.00 format, with leading and trailing zeroes if necessary; concatenate and return the values using a rollup; and carve latest and previous records out of the resulting string based on known string lengths. (In a production environment, you’d probably hide {WtStr} and {DateStr}.)

It works, given the caveats already mentioned, and barring any significant change to how Airtable stores records…

Airtable

Subtract Last 2 Alternative #2 - Airtable

Explore the "Subtract Last 2 Alternative #2" base on Airtable.

---

No idea why the first attempt to post this triggered some sort of ‘offensive content’ autoblock…

[Kamille_Parks]

I don’t know it never occurred to me to write a full formula in a Rollup field before. I think I found the least efficient way to do this lol

[W_Vann_Hall]

Support for aggregation formulas (formulas entered into the configuration field ordinarily used for aggrgation functions) is one of Airtable’s greatest un[der]documented features. While I’m not sure of anything they can accomplish that couldn’t be done using aggregation functions and more fields, they appear to allow some things to be done much more efficiently.

Your approach to the problem was textbook: It analyzed the requirements, broke down the steps needed to meet them, and implemented the solution. @bdelanghe improved upon it by taking a step back and identifying sections of your solution that could be optimized — something that’s easier to do after-the-fact than while trying to implement a solution.* I looked at both your solutions and decided to cheat by defining the problem more narrowly. Mine may be a more efficient solution when it works, but it has the potential to fail spectacularly…

---

* Eventually, you’ll have a quiver of generic processes that can be adapted for and plugged into the base at hand. I have a few I use perhaps too often, which is why you’ll sometimes see requests here for assistance than doesn’t involve ‘linking all records from one table to a single record in a second table.’

[Kamille_Parks]

Also I think I misunderstood the initial request. I took it to mean “find the difference between each successive log” but “what’s the current progress” makes more sense.

[Jeremy_Oglesby]

W_Vann_Hall:

I looked at both your solutions and decided to cheat

Typical @W_Vann_Hall… :winking_face:

W_Vann_Hall:

which is why you’ll sometimes see requests here for assistance than doesn’t involve ‘linking all records from one table to a single record in a second table.’

AKA, “Can anyone other than @W_Vann_Hall help… please…?” :rofl: