# Subtracting last two created linked record

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

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

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.

Accessing Previous Records

**bdelanghe**#3

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

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.

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

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

**W_Vann_Hall**#6

âŚ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âŚ

No idea why the first attempt to post this triggered some sort of âoffensive contentâ autoblockâŚ

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**#8

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.â

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**#10

Typical @W_Vann_HallâŚ

AKA, âCan anyone *other than* @W_Vann_Hall helpâŚ pleaseâŚ?â