Help

Multiply an Array

3303 12
cancel
Showing results for 
Search instead for 
Did you mean: 
Carl_Duffaut
4 - Data Explorer
4 - Data Explorer

Hey there,

Is there a way to multiplyer an array of number to a constant or variable.

I have all my item with a default quantity on a table like so :
piecegroup.png

I’ve created group of item so I only have to add one group to list of the items with their default quantity (to prevent errors)

group.png

My problem is here on the packing List, when I want to be able to multiply the default quantity per Tower I need but I can’t figure out how to do so. How to multiply an array with “Tower needed” ?

packing list.png

12 Replies 12
Ariel_Sigler
6 - Interface Innovator
6 - Interface Innovator

Hi
SO how di you multiplied the array values by 2 ?

This can’t currently be done with Airtable’s formulas, which have no way of parsing array data. Using Airtable’s native features, the only way to do this is with a script, either in the Scripting app, or via an automation. If you’d like help with either option, let us know.

Do I need a PRO account with enabling this ?

DO you have an exmaple of how an automation like this might look like?

Use of Airtable’s Scripting app is free for all members until March 2021, at which point it will probably revert to only being available for workspaces that are Pro plan or higher. To use a scripting action in an automation requires the base to be in a Plus plan workspace or higher.

I haven’t yet tried building a script to accomplish what @Carl_Duffaut asked about previously. It probably wouldn’t take long, but I won’t have time to play with it until tomorrow.

Here’s a script that will do the job. This is set up to work in the Scripting app. I don’t recommend using an automation for this because there are several possible changes that would require a recalculation, and catching some of those could be tricky.

// Setup
let table = base.getTable("Group")
let record = await input.recordAsync("Select a record", table)

// Collect the list
let items = record.getCellValueAsString("Item Names").split("\n")
let qty = record.getCellValue("Quantity")
let newItems = []

// Calculate new totals
for (let item of items) {
    let parts = item.split(" ")
    let newQty = Number(parts[0]) * qty
    newItems.push(`${newQty} ${parts.slice(1).join(" ")}`)
}

await table.updateRecordAsync(record, {"Total Items": newItems.join("\n")})

Here’s the script in action:

calculateTotal

Aside from changing the table and field names in the script to match your scenario, the primary requirement is that the items are on separate lines, as the original example above shows. This can be done by using the following aggregation formula for any rollup fields: ARRAYJOIN(values, "\n"). A number must be at the beginning of each line, with a space separating it from later pieces.

Julian_E_Post
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi Justin! (or other script experts),
I’m hoping to resurrect this thread because I used your code for a similar problem (thank you!). I was able to get it to work as you recommended, and now I’m hoping to modify it so that the script runs through an automation whenever the Quantity field is updated. I pasted the same script into an Automation, which produced an error

Screen Shot 2022-01-06 at 9.31.28 PM

I tried to fix that by changing line 3 to read:

let record = input.config()

but this gave me an error on line 6. Any guidance would be appreciated!

Automations run in the background, and a such they can’t get user input. The input object in an automation “Run script” action doesn’t have the same features as the same-named one in the Scripting app. The automation version only contains a single config() method, which is used for retrieving input variables. You’ll need to use that to get the record ID from the trigger step. Pull up the “API” reference at the bottom of the script window, click on “Input”, and you’ll see an example of how to use that method.

Ok so I think I’ve been able to do that. I defined the text that needs to be split as “singleAmount”, then tried to use the same split function. The result was this error:

TypeError: singleAmount.split is not a function