I asked this question on the Airtable forum a few days ago with no response yet. Maybe someone here has run into this issue and has a suggested solution.
The most basic description of the issue is that forms return data horizontally (one row, many columns), when performing calculations, you want to work with data vertically (many rows, some columns), and there is no transpose function within Airtable to my knowledge.
Currently, we have a manual work around by copy/paste a single row or export/import csv for entire table from our form data table into Google sheets, then transpose, then re-copy and paste back data into our BOM table. I would like to be able to automate this but haven’t been successful yet. The automation steps seem to want to create a single record/single row. I haven’t fully explored using a multi-step zap, but with 50+ items chosen on the form, the concept of a 50-step zap to do what a simple copy/paste does manually seems inefficient even if it is automated.
