Out of curiosity, how are you doing data entry? Are people manually typing in data into a user interface? Or do you have a more automated system with sensors on dispensers automatically feeding data to some digital file? You say that you like Airtable because you can use it on many devices, but you don’t say, for example, if you want to be able to do data entry on your phone.
What do you do with the individual daily fuel amounts, other than calculate the total fuel usage for both the day and the piece of equipment? While it is handy to have those calculated automatically, both could be calculated in a script. Do you need to use those amounts in an other calculations that cannot be done by a script? Do you have a Pro account with the ability to run 50,000 scripting automations per month?
Have you considered using a long text field to store data in json format? It would require reworking of how you do data entry. It would also require reworking how you do some calculations. However, it could help you deal with Airtable’s record limits and field limits (max 500 fields/table).
And now to answer your original question.
Scripting App can
- read all the existing field names in all of the tables in the base
- perform string handling to identify if a field name matches a pattern
- read cell values
- perform arithmetic
- write values back into Airtable
Thus, yes, it is possible to do the calculations that you are asking. Someone would still have to create the new field every day, but the calculations are possible. All of the other people who have provide advice on this thread know that this can be done. However, they also know that it is a bad idea, which is why they are steering you in a different direction instead of telling you how to do it.
I’m making a ton of assumptions, but what do you think of this workflow?
- You go about your daily fueling rounds with your phone.
- You have a webpage that is an embedded Airtable view listing all of your equipment. For each piece of equipment there is a button.
- When you fuel a piece of equipment, you click the button for that piece of equipment. A new browser window opens up prefilled with the equipment name and the current date. The only information you need to type in is the amount of fuel, then you click submit.
- After you are done with your rounds, you go back to your office and click the run button for Scripting App. Scripting App does a bunch of calculations, including moving the data entered from the forms into json format, and deleting the original records created by the web form.
- An equipment table displays the total fuel dispensed to each piece of equipment. A table of days shows the total fuel dispensed for each day. If you really want to see all the details for the fuel dispensed for each piece of equipment or each day, you view a long text field with the data.
You don’t have to create a new field every day. You don’t have to worry about limits on the number of fields or the number of records.
If this workflow doesn’t make sense for your situation, I invite you to explain more about your situation.
While scripting does open up a lot of new possibilities, there are still lots of things that cannot be done with just scripting. Custom apps opens up even more possibilities, but the development effort is much bigger, and there are still limitation. On the other hand, just because something can be done doesn’t mean that it should be done.
I like to focus on creating things that both can and should be done, with the understanding that nothing is ever perfect and there are always tradeoffs.