Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 10, 2020 08:40 PM
Hi All,
Im trying to create a formula field that sums all number fields or all fields that start with the letter M. I have nearly 1200 number fields and add more daily.
At the moment I am adding in the field name to my formula every time i create a new one. Other collaborators don’t know how to do this and i worry it maybe missed.
Any help would be very appreciated.
Apr 10, 2020 09:01 PM
Your current method of adding the field name to the formula when you create a new field is the only way to update the formula.
Another alternative is to have a script that calculates the sum for you. You would have to click the “run” button to calculate the sum, but the script could have logic to include all number fields that start with the letter M so you do not have to edit the script when you add a new field.
However, having 1200 number fields is a lot of fields. There is also a limit of 500 fields per table. You might want to consider a different base design with fewer fields.
Apr 10, 2020 09:48 PM
1200 fields/columns is a crazy wide table. I have been doing this stuff for almost three decades. I think I had a table once that had close to 500 fields/columns. But in general, my tables almost never have more than a couple of hundred fields.
You don’t explain what those fields are for, but I’m going to go out on a limb and say that this base simply isn’t properly structured. You’re simply not supposed to be adding lots of fields every day to a database: You add records constantly, but you add fields only when you need to change your structure. It sure looks to me like all those “M” fields represent the same sort of thing – survey answers, perhaps – and if that’s the case, you should consider putting them into a related table as new records.
And as @kuovonne noted: There’s a limit to the number of fields you can have in an Airtable base.
Make sense?
William
Apr 30, 2020 09:03 PM
Sorry Im at 321 fields but this will increase. Yes your right it is crazy wide. I have tried structuring it the other ways but it makes the data difficult to enter.
Im trying to make it as automated as possible and need this field to speak with other formula fields, so i don’t think a script would work.
Rather than starting with the letter M could a create a formula that sums all number fields?