Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 14, 2021 04:28 PM
Hi Airtable folks. I’m wondering if anyone has run across this message when writing Formulas in Airtable:
“Can’t save field because it exceeds the limit of formula dependencies”
This happened when I was splitting-out 100 image URL’s into individual fields. Everything worked fine until I tried to submit the 99th image Formula.
Here are screengrabs of my Formula, in case anyone sees an inconsistency. I highlighted in RED where I made additions/changes. But again, images 1 through 98 worked just fine:
Jun 15, 2021 05:36 AM
Looks like you’ve hit some kind of limit on the number of columns you can refer to in a formula. This is my guess looking at the error message, you can contact Airtable support to confirm.
I’m not sure what problem you are trying to solve with Airtable but 100 columns sound like a lot. Is it possible to convert those columns into rows?
Jun 15, 2021 05:43 AM
Yes, as @Raminder_Singh said, your images should be in rows, not fields.
Jun 15, 2021 07:49 AM
Thank you both for your replies! I will contact Airtable support.
I will also consider converting the columns into rows (though I’m not sure how, yet), but that appears to greater complicate my use case — partially because it exponentially reduces my maximum # of 50,000 records per base, but also because it would require me to have to create a unique table of image attachments per user (and I expect to have 500 to 1,000 users). Which I think would make for a messy Airtable base to administer.
And @ Raminder_Singh, since you were asking about use-case:
Jun 15, 2021 11:55 AM
Here’s what you can do to simplify the process down a bunch using an Automation. This assumes you have 4 Multiple Line Text
fields that the Automation can copy no more than 25 links into.
{Attachment}
field gets updated.Run a script
step that has two input config variables: recordId
which should be the ID of the trigger record, and links
which should be the list of URLs from the trigger record’s {Attachment}
field. Use the following script:let table = base.getTable("Name of Table")
let {recordId, links} = input.config()
// Rename each item in the below array to match the names of the fields of your base
let fieldsToSplitAmongst = ["Batch 1", "Batch 2", "Batch 3", "Batch 4"]
let maxPerBatch = 25
let recordUpdates = Object.fromEntries(fieldsToSplitAmongst.map((field, index) => {
let batch = links.splice(index, (maxPerBatch * (index + 1)))
return [field, batch]
}))
output.set("recordUpdates", recordUpdates)
{
Batch 1: Array(25),
Batch 2: Array(15),
Batch 3: Array(0),
Batch 4: Array(0)
}
Update record
step to update all "Batch"
fields with the values from the output of the script. Note: The script can be modified to handle updating the record from within the script to avoid having this step.Jun 15, 2021 02:30 PM
Wow, I’m really impressed that you scaled up the formulas that far. I never had the patience to go past about 30. Thank you for posting your findings.
Airtable formulas do have limits, even though they are far higher than needed for most use cases. This thread has another example of reaching the limits of a formula.
In general, if you find you are approaching the limits of what a formula can do, I strongly recommend looking into scripting or an alternative base design.
Jun 16, 2021 12:39 PM
Thanks, @kuovonne! I was able to get this far, thanks to you! And yes, it took a few hours because I’m not a developer and don’t have crazy automations to repeat items automatically. I copied and pasted the old fashioned way, from a text editor — which was a lot easier to do/see than editig the formulas within Airtable. I’m also talking with Airtable tech support now, and the engineers are reviewing if this can be improved upon.
Jun 16, 2021 12:41 PM
Thanks so much, @ Kamille_Parks! If your method works this would be very useful. I’ll try it out and let you know!
Jun 16, 2021 01:11 PM
I tried it, but the test output failed in the script editor. I’ve never created an Airtable script so it’s possible that I didn’t update the right part. I just replaced “Name of Table” with the actual name of the table, in the quotes. I also created 4 Multiple Line Text fields, and named them “Batch 1”, “Batch 2”, “Batch 3”, “Batch 4” just to keep it simple for now. Was I also supposed to replace “recordID” and “links” from an actual record?
Here’s the error message:
Here is a screenshot of the code line #'s, in case that helps understand the error message:
Jun 16, 2021 01:14 PM
You need to create them as input variables to the scripting automation. Those are the names of the variables, and you will select the values from the record in step 1. Kamille gave more details in her post with the script. Note that this script is designed to run as an automation, and not from a button in scripting app.