Subtracting in field - Record from Record

#1

Good Day,

Looking to subtract a running total collected daily from the previous total to give me the value used since the last entry.

Better explanation (I hope)
Jan 1 my total is: 1111
Jan 2 my total is 1112, so Jan 2 total - Jan 1 total = 1
Jan 3 my total is 1115, so Jan 3 - Jan 2 total = 3

The running total is kept in the same Column/Field and I want the next Column/Field to auto-calculate the difference from day to day.

In excel: Cell B2 I would use =A2-A1
Cell B3 would be =A3-A2
and so on and so on forever since the total never stop rising.

Clear as mud i hope. I’m likely just overthinking it, and not seeing the whole picture. Airtable is new to me, but if I can get this to work, it will be perfect for our needs.

Thanks,
Marc

#2

You could probably create something like that, but you’ll need two tables at least. Maybe some ‘master’ date table (one record for each day) and a table with the log records (changes + date reference).

Then you’ll be able to play with rollup, sum, lookup, grouping, etc. between both tables.

#3

Thanks Tuur,

I have attempted what you suggested but I am still having problems. Would it be possible for you to provide a brief example that I could build from?

Thanks

#4

Can you share a view (in a private message)?

#5

Here is the link to the view:

I hope that it is self explanatory. I am simply trying to calculate the number of “Counts Per Day” by subtracting yesterday’s “Counter” value from today’s.

I really appreciate the help

#6

You have a private message.

#7

I am looking for a solution similar to what I read in this thread. I use a scenario a lot, and a spreadsheet did the job. But I would prefer using AirTable. In a scenario records refer to each other. The first item will take 5 minutes, the next one 15 and so on. The table should accumulate the minutes in each record.
I wonder if anyone has a solution for this application. Would be awesome!
I tried building something with 2 tables but I keep ending up in circular reference errors.

#8

You should be able to build it with only this table and a reference to the former record.

So the second record has a field that links to the first one, the 3rd record links to the 2nd, etc.

#9

I suppose you refer to an earlier message. There’s the message with a reference to a view, but I don’t find the database with the example. Do you have more information for me?
Thanks!

#10

I was referring to your screenshot. Turn that into a table and add a linked field to itself. Link to the former record and you should be able to take it from there by adding a look up field to the ending time and doing calculations for example.

Does this help?

#11

@Tuur
I spent some time trying to apply your suggestions. I do get stuck every time, because of this:
There are only 2 items that are entered manually:

1. The starting time (“00”), and
2. the duration.

Everything else is calculated. And that causes circular references every way I try. Maybe you can go a little deeper in finding a solution? I just don’t find the proper solution.

Regards.

#12

Turns out I was wrong about linking back in one table. I thought I’d done that in the past…

Anyway, you can do it with two tables. It’s not pretty, but it works. If you want to copy the base just let me know.

#13

Thank you @Tuur!! This is what I’ve been looking for.
I would like to have the base, because of the formula’s and the link between tables.

Regards
André

#14

PM incoming in 5, 4, 3, 2, 1…

#15

whats the private message for ??! i am trying to do the same. can you share your solution

#16

@Tuur why PM could you tell us your solution

#17

The solution is already posted. The PM was only for sharing the base.

But I suspect you’d like a link too?

#18

@Tuur

#19

Sure.

You’ve got a PM.

#20

@Tuur thanks for sharing … i d like to have a good feedback on this, but it is really hard to imagine how we can implement a similar solution in a good scale and production.its un-managable
i really liked airtable but i am constantly hitting walls with limitations of the design.

thanks