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

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.

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!

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

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

Thanks much!

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

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?

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.

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

@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):

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