Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

1664 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Ronja_Deisel
4 - Data Explorer
4 - Data Explorer

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!

12 Replies 12

Thanks @Csaba_Vass

Let me know if you see anything when you look at it later.

I think I follow you… when you add a new date to the table ‘electricity’ will it automatically add the date to the ‘calculation’ table or do you need to keep adding dates to both tables for the calculations to keep going?

It won’t work automatically. When I add a new entry, I need to add (select) the proper dates in calculation fields. I’ll check the sample and fill with some new entry, and then I’ll write the method step by step.

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

The step by step guide:

  1. I check my e.meter monthly on same day usually, and fill in the date and the e.meter data on ‘electricity’ table
  2. change to ‘electricity calculation’ table - nothing happens while I select an entry in ‘last date’ field when a date is selected, in last check field I will see the e.meter’s position in last check.
  3. select the actual date in ‘check date field’ and the el.meter check field is filled, ande date difference and average fields are calculated. Same time in electricity table ‘difference’ and ‘date difference’ fields are filled too.

Last date and check date fields in calculation table are linked fields to ‘electricity’ table so they show the primary field (date)

‘last check’ and ‘el.meter check’ - renamed to ‘actual check’ - fields are lookup field and pointed to electricity table’s ‘el meter at date’ fields, so they show the checked data.

‘… check’ fields’ format is set to currency, and the currency symbol is customized to kWh. You can use it to show any measured unit

The lookup field is working only when you have a linked field too. thats why the actual check fields aren’t filled while the check dates aren’t selected.

I set the your share options to editor, you can try it now in work. It is only a sample copy with some filled data