How might we manipulate results of "Find Records" action in automation to insert into email?

[Joel_R_Putnam]

Is there a way to use formulas in the Send an Email or other action in Airtable automations that takes the data from a Find Records step and does some simple manipulations with it, like a SUM() function of all properties of listed records?

In my example, I charge my car off of an electrical outlet in front of my landlord's house and pay them each month for the electricity I use. I have a base with an entry for each charging session that calculates the money I owe for each charge. I can easily send an email with a list of all the charging sessions for the previous month, but I would like to send a sum of the cost property (a formula field in the base that outputs numbers) instead of just the list of individual entries.

What seems like the most simple way to do it would be something like a "Formula" action that takes data from previous steps and manipulates them using the Airtable Formula Field reference, and then lets subsequent action (update record, send email) use the results from that action, but I'm not seeing anything like that immediately. What's a working alternative?

[TheTimeSavingCo]

> Is there a way to use formulas in the Send an Email or other action in Airtable automations that takes the data from a Find Records step and does some simple manipulations with it, like a SUM() function of all properties of listed records?

Unfortunately, no.  You could write some JavaScript to help with this if you'd like to keep it all within the automations?

If not, I think you'll need to structure your data specifically for this purpose I'm afraid

[ScottWorld]

@Joel_R_Putnam 

Unfortunately, Airtable gives no programmatic access to the summary bar, even though customers have been requesting this for years.

As far as I know, you only have 3 workarounds for this:

1. Restructure your data and tables to provide you with this information by using linked record fields, lookup fields, and rollup fields.

2. Write your own custom JavaScripts.

3. Use the external automation tool Make, which is the only automation tool on the market that offers many different types of “aggregation tools” that can aggregate data across multiple found records. There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. There are also many Make experts hanging out there who can answer other Make questions.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you create this, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Joel_R_Putnam]

I'd be up to cobble together some JS; I didn't know you could do that within automatons. How would you get started?

[Joel_R_Putnam]

I've actually been using Make since the Integromat days :). I think that's what spoiled me into thinking you could do something like what I was planning. I'm running up against paywall limits though so was hoping to find a solution within Airtable.

I'm open to something that involves cobbling some JS together and would like to try that before restructuring data. Are you talking about just wiritng and hosting your own scripts that bypass the automation interface altogether or is there a way to work JS into the automation interface?

[TheTimeSavingCo]

I'd put the run a script action after the "Find Records" action and make a new list of the numbers I needed to sum:

Then I'd set the sum as an output variable that I'd use in the email

I threw a copy of the automation together here for you to check out; should do what you need!

[Joel_R_Putnam]

Ah, got it. Thank you. Unfortunately I'm a free user, and the Run a Script action is available to paying customers only. Which makes sense, but is a little disappointing.

Thanks for your help and work on this, I hope it's useful to other users!

[TheTimeSavingCo]

Ahh fair enough.  Yeah, then I think you're going to need to create a new "Summary" table, link it to your current table, and then have an automation that'll:
1. Execute that "Find Records" action
2. Create a new record in that "Summary" table
3. Link all the records from step 1 to the record in step 2

You'd then have a rollup field in the "Summary" table with `SUM(values)` set up that would sum everything up for you, and you could use that field in your email

[Lillian_Lin]

This example was amazing!! Thank you so much for this - exactly what I needed! Appreciate the time you took to set this up.