Getting the value of a specific column/field in above/previous record automatically

[kelvinn]

Hi everyone,

I want to build a "Billing Meter" table that generates invoices for Meter Readings.

The value of the Meter Readings are sent via email and I have set up an email parser via Parsio, that parses the data (meter) in the email, and creates a record automatically with the parsed data in the "Billing Meter" Table with the info through Zapier.

(Example screenshot of parsed email)

As it's a month to month billing, I have to calculate the usage difference each month, then tabulate the billing based on the difference.

(Example of Table) 

In the table, there are "Billing Meter 1 (Start)" and "Billing Meter 1 (End)" fields. The parsed data, in this case which is the value of "818" (as shown in the 1st screenshot) would be populated in the "Billing Meter 1 (End)" field in a new record that is created automatically when an email is parsed.

The "Billing Meter 1 (Start)" field would be empty for the newly created record since there would not be data for this, in the email. The value for this field has to be from the previous parsed data that is in "Billing Meter 1 (End)"

Below is a screenshot of what I am trying to achieved automatically.

Can anyone please advise if there's a way to achieve this automatically (via formulas or etc)? 

Would greatly appreciate the help.

Thanks!

[Zwerik]

You may be able to resolve this with an automation, but you would need to add a column to order the records by. This way, when a new record is created, you would be able to find the previous record and then update that previous billing record with the newly parsed meter value in the end value. 

[TheTimeSavingCo]

If I were you I'd end up doing the rollup table with the `MAX(values)` formula to identify the latest record per business and use that field in my Zap.  Specifically, after the Zap gets triggered, it would look for a record in the table for that business where the "Is Latest Created Date" value is "Yes", and it would thus be able to grab the billing meter value for use with the "Create Record" action, does that make sense?

Link to base

[kelvinn]

Hi @TheTimeSavingCo,

Thanks much for this and really appreciate it.

I am not sure if the rollup and identifying the latest created date would work in my case. Reason being, there are several different invoices, tied to different businesses, and each business have different meter readings, all in 1 table. 

Wouldn't the automation be confused/messed up since the values and tabulations will be different for each business? Unless I am missing out something here or doing something wrong.

[TheTimeSavingCo]

Hard to say without seeing your data really.  If you could create an example set of data that you think wouldn't work with this setup I could take a look at it for you?

[kelvinn]

Hi @TheTimeSavingCo 

Here's a screenshot of the set of data:

So there are multiple businesses with different values in the Billing Meter End field that it should fetch the value from. And each newly created record may not be in the same consecutive record.

Eg: 

1st record = Business 1

2nd record = Business 3

3rd record = Business 1

(As illustrated in the above screenshot)

Thus, I thought that the rollup would not work in this case. If I am wrong, please kindly point me in the right direction. 

Below is what I am hoping to achieve automatically (Be it via a different setup, formula, automation, scripts, or third party tool like Zapier, Integromat/Make.

Note: I just filled the data manually and grouped it together to better showcase what I am looking to achieve.

Appreciate your kind advise.

Thanks much. 

[TheTimeSavingCo]

Oh, it would work fine, just use the "SN" field as the linked field.  When the Zap triggers, make it look for a record in the rollup table with the same SN value and grab the billing meter value, and create the new record with that value in the "Billing Meter Start" field

[kelvinn]

@TheTimeSavingCo 

That's great to know! Would you be able to point me in the right direction on setting up the rollup table with `MAX(values)` formula? The solution you mentioned. Perhaps a rough working example would greatly help if it's convenient for you. Apologies as I am fairly new to Airtable. 

Thanks much!

[TheTimeSavingCo]

Yeap, check out this base, it has the `MAX(values)` system set up