How do I make a comma separated string list of numbers into array that is summable

Change your lookup field to a rollup field and use the formula: SUM(values)

I can’t specify which chunk of the values to use with rollup though right? My ultimate goal is to have a running total that increases with the month increasing. Here’s my current formula:

SUBSTITUTE(REGEX_EXTRACT(ARRAYJOIN(a, ","), "(?:,?[\d\.]+){0," & ({extracted month number} + 1) & "}.*?"), ",", ", ")

Regex shown here

Rollups are based on linked records in another table, which are linked through a linked record field. So, as long as your records are in your linked record field, then you can use the rollup field to access them.

Rollup fields also have the extra ability to let you conditionally filter which linked records to use based on a certain hardcoded value that you manually hardcode in. (No dynamic values are allowed, unfortunately.)

You can learn more about linked record fields & rollup fields here:

Yes, I’m aware of that fact. I just don’t see how I can automatically link only X amount of records using automations. What I’m doing now is linking all records to one record in a different table where I do a lookup, then I do a lookup of the lookup and regex that to a certain point to as an example only get the first 6 entries of the lookup at the record for the 6th month.

SUBSTITUTE(REGEX_EXTRACT(ARRAYJOIN(a, ","), "(?:,?[\d\.]+){0," & ({extracted month number} + 1) & "}.*?"), ",", ", ")

Ergo, send in the “special teams” scripting feature to score the goal.

I don’t want to use automations though, as I would very quickly reach my quota that way

Scripts don’t have to run in automations.

Yeah, but my users don’t want to press a button each time they want to see the latest stats

Indeed, the double edge of automated workflows.

To create high-value data visualizations and analytics, you need to plan and such planning should include some degree of resource assessment. In your particular case, I would have planned the data architecture such that none of this would be a factor.

While your users want a clean pathway to the latest data, you are faced with crafting a comma-separated list of numbers to achieve that. I would ask -

How could I design this system such that I would never need to create a comma-separated list of numbers?

If you rule out automation and manual steps, it leaves you only one out; design it in a way that neither of these unfavorable approaches is required.

But I have to have a rolling sum graph based on total earnings up to each month. So if in January I got 200, and February I got 300, it would show 200 for January and 500 for February. I already have a comma separated list of values, why can’t I just sum it, like I can with lookup comma separated value lists?

You’ve adopted the idea that Airtable is a spreadsheet comprised of static cells and a formula model that is not much different from Google sheets or Excel. In reality, Airtable is quite a departure from that which you assume, and to be fair - it is intentionally different to solve many other data-centric challenges.

The equivalent to lookups and list summations in the world of databases is commonly (and loosely) known as stored procedures; code blocks that perform these types of operations on certain rows. Script blocks and extensions are designed to bridge the gap between database-like platforms and the desire to perform these types of multi-row data aggregations that are so common in the world of spreadsheets.

The irony is that we all want Airtable to be different but exactly the same. :winking_face:

I’ll bet there’s a clever Airtable consultant that can engineer a way to avoid this challenge but still see the exact information you need for reporting.

When users hit the wall with stuff like this, it suggests there’s a corner and you’ve unintentionally painted yourself into it. With a little data modeling and requirements planning, I have a hunch you can avoid this altogether.

This is the solution! Worked for me anyway with a similar problem

"Change your lookup field to a rollup field and use the formula: SUM(values)"