Reference entry of record in same table, eg "previous" record

[Ronja_Deisel]

I want to track meter readings for electricity and water consumption.

The relevant columns are:

• Date of the reading
• Reading (eg. kWh, m^3)

I want to calculate how much kWh or m^3 was used between meter readings and then how much was used per day.
In excel or similar systems I referenced the Date and Readings Entries for the previous Record to calculate the difference in kWh or m^3 and Time. I’m having problems modelling the same concept here. How would you do it?

Or do I need another table for my analysis? If so: can I automatically populate the Date column in the Analysis Table from the Readings Table?

Thanks!

[Csaba_Vass]

Hi @Ronja_Deisel

I use Currency field with custom currency symbol for example kWh.
Just set your symbol to what you need and it will be placed befor the reading number.

To calculate the difference between the last and actual readings, it needs an other table with linked columnt to dates and lookup columns to the readings (both actual and last)
This is my solution for this problem.

[Ronja_Deisel]

That’s a good idea about the currency, I’ll do that!

And thanks for the solution with the two tables, too. I was thinking too much in spreadsheet terms to think of that!
Thanks!

[Peter_Kaseman-W]

This is what I’m looking for also - could you post a sample?

[Csaba_Vass]

Hi Peter!
I shared a base with you. You can see the electricity meter checks by date and electricity calculations (difference, average…) the calculated usage is linked back to electricity table. The checked unit field is a currency filed with customized unit.

Sample linked record to latest record with customized unit

[Peter_Kaseman-W]

Thanks much!

Peter Kaseman-WoldGoodland Tree Works, Inc.
608-221-9565**www.goodlandtreeworks.com
http://www.goodlandtreeworks.com/

[Russ_Layton]

How did you create the shift in the “last date” and “Check date” columns of the electricity calculation table?

It looks like these two columns are sent to this table using the Link To field type in another table…or am I wrong?

you have the primary field which is a formula field but I can’t see what the formula is?

[Csaba_Vass]

Hey @Russ_Layton, sorry for tha late answer,

The last date and check date columns are linked fields to electricity table. This is the ‘trick’. You can’t link an other fileld in the same table. So I needed an extra table ‘electricity calculations’ to link a record with the latest, and calculate the differrence.

The formula in the primary field is {el. meter check} - {last check} - what calculates the difference between the actual and the latest checks.

[Russ_Layton]

@Csaba_Vass no worries on the late response. My question is how did you create the shift in the data such that you have a single row with two different dates on it such that you could calculate the difference. It seems that once I can get the data from two different dates on the same row the math is easy…

It looks like you are using “Link to another record” and “Lookup” field types to do this but I cannot figure out how to get there to be a shift in the dates… I can only get the dates to be the same date on the same row. I feel like I’ve tried many different ways but it always winds up on the same row…

Furthermore, the date in your base is the Primary Field and I can’t select Link to Another record as the field type?

Please see the attached image for a sketch of what I can’t figure out.

[Csaba_Vass]

@Russ_Layton

The shift:

I have the table ‘electricity’ with the date field: when I checked the e. Meter. The date is primary field, so, when I link a record to ‘calculation’ table, I’ll see the date there. I linked two row: ‘check date’ for actual, and ‘last date’ the latest before. I select them manually. When the dates are selected, the lookup fields get the electricity meters’s positions at the selected dates. From this point I can easily calculate the difference. As I remember, I linked back the diff. To electricity Table.

I renamed the field ‘vill fogyasztás’ to difference (monthly) - the base was made originally in Hungarian. The field shows the calculated difference between the two dates check. Because it is the primary field, when I link it back to electricity table, I’ll see there the difference.

Now I checked the base only on my phone. I will see it deeper later.

Russ_Layton airtable@discoursemail.com ezt írta (időpont: 2018. szept. 15., Szo 15:15):