Use value from row 1 in row 2 without automation

[Olli76]

Hello dear community,

I have already searched but found nothing. I want to use a value from one row (for example, row 1) in a formula in row 2. In row 2, the value from row 1 should be used minus the value from row 2 (same field). How does that work?

Automation is not the right way, as the dataset is not newly created (trigger), but already exists. Specifically, it is about the following:

I want to use the value "km" from the field "refueled at km" in row 1 and calculate it in row 2 in the field "kilometers driven". This value is calculated from the field "refueled at km" in row 1 minus "refueled at km" in row 2. Can someone help me? Thanks in advance.

[Mike_AutomaticN]

Hey @Olli76!

That cannot be done natively within Airtable, unless you use a lookup field to bring value from Row 1 as a new field in Row 2. However, in order to get such lookup field, you'd need to somehow (manually) or automatically link Row 1 to Row 2. Which is in turn an issue in itself as you'd be in the same place you started.

I'd say that automation is actually the way you want to go. However finding the right logic for it is your biggest challenge. You can solve this in two different ways at least probably, but it will depend on how your data is structured.

You'll probably want to have an automation that will:
0. Get triggered when record meets certain condition or when record is created.
1. Find all records meeting certain criteria
2. Sort such list of records
3. Use only the latest record on such sorted list
4. Update the record that triggered the automation.

Again, without having much info on your data set this might not be enough of an answer, but either through plain vanilla automation, or scripting, this can be absolutely achieved. It is a matter of understanding the logic to it.

Please feel free to provide further context, or reach out via Private Message and I'd be happy to help out.

Best,
Mike, Consultant @ Automatic Nation

[VikasVimal]

Please try to avoid thinking of Airtable like Excel. Each cell in Excel can be independently configured. That's not how Airtable or other databases behave. 
You can have a workaround by linking records WITHIN the same table, then using lookup/rollups to get data from that linked record.
If I have 5 task in sequence and I want to trigger the next task based on previous task completion, I'd create a linked record field within the same table, then for each record, I'll fill it with the previous record in the same table, and then lookup the completion status of the linked record. Then run automations off of that.

[Olli76]

I know that Airtable is not a replacement for Excel, but a function like "use the field in the previous row" would be really practical. 🙂 Or even {Field1 - 1} to refer to the previous row.

It's basically about a control field. In the first table, kilometers are recorded. In a second table in the same base, the kilometers for refueling are noted. Now I just want to know how many kilometers were driven between the current refueling and the last refueling in the same table, but with the value in Table 2 in row 2 minus the value in row 1. So, a simple calculation. 🙂

An automation only solves the problem when I want to create a new record. Once this record is created, the automation doesn't help if the refueling kilometers change afterwards. And they do change.

[VikasVimal]

Like I said, That's not how any database operates. Records simply don't know what the previous or the next cell contains. Building that feature would mean recalculating the fields every time someone does a sort or a filter operation and would break the purpose of a database. As you said, it is a pretty simple calculation, until someone sorts the table by kms instead of dates.

In this case, you can locate the most recent refueling entry and link it to the one you create using automation. Then you could look up the previous data and the system would calculate distance travelled.

If you feel needing to use an Excel feature (you're not alone), sometimes it does make sense to actually use Excel instead of Airtable. Although this particular use case can be solved by playing with automations for 20-30 minutes.