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. :slight_smile:


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

Scenario


#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… :thinking:

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. :slight_smile:


#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 :slight_smile: 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
yes please send me the link to base


#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