Automatically add values from multiple fields

[Shaun_Hornsby]

I tried to get an answer to a couple different things im working on over in the formulas section but didnt get the answer i want so im going to try it here.

I am working on a new base that will keep track of fuel used in equipment that i take care of. in one column i have the equipment number, everyday i will be adding a new column to enter the fuel dispensed in each machine that day. The sum at the bottom of that column will let me know the total amount of fuel dispensed for the day but i also need to keep a running total of fuel dispensed for each piece of equipment. My field names will all start with “Fuel” followed by the date… ex (Fuel 1-11-21) , (Fuel 1-12-21) , etc.

Creating a formula for this is easy enough if there was only a few fields to add but adding a new field for each day would make that formula long and would require me to go in and modify the formula each day to add the new field to the calculation. Does anyone know how i can do this with a script where the script will find all fields that contain “Fuel” and then add each cell in each row to a “Total” field? The script would need to overwrite/replace the contents of the Total field each time it is ran.

I appreciate any help in advance, i have been searching for an answer but cant seem to find what im looking for.

[Justin_Barrett]

Looking at your other thread, I see one response from @Bill.French that brings up some important points and questions. I’m curious how, despite that reply, you felt that you didn’t get the answer you want. I don’t feel that it’s wise to just dismiss what he addressed and try a different angle on the same question.

[Shaun_Hornsby]

I am not dismissing what he said. But instead of helping me come up with a solution he just criticized the whole thing. I’m all for corrective criticism when needed but this isn’t one of those times. I know what I want to make happen, I know it’s possible, it’s just a matter of if anyone will help make that happen of if it takes me 6 months to figure it out on my own. With this tread, I thought I’d break it down a little better for one of the tasks I need instead of throwing both of them into one question because the other (machine hours) isn’t as necessary as this one. So either you can help or not but so far you are not helping either!

[Bill_French]

Shaun_Hornsby:

but adding a new field for each day would make that formula long and would require me to go in and modify the formula each day to add the new field to the calculation.

I’ll try to be kinder. Square peg; round hole.

Airtable doesn’t provide the formulaic or API machinery to match your data model vision – a vision where transactional data is stored in ever-increasing numbers of new fields.

And I believe Airtable doesn’t support this approach for good reason. As I mentioned in the earlier thread, it’s not wise to do this for the exact reason you raise - it requires you to constantly babysit the process and eventually it will seize up because the number of possible fields has a practical limit.

In contrast, the number of records, while Airtable also has a practical limit of about 50,000 rows, it is probably ideal for your solution because 50,000 days is about 136 years of data for one piece of equipment or about 1.3 years of data for about 100 pieces of equipment.

Ideally, you should entertain a 90-degree pivot for your transactional fuel and other data points that occur daily for each piece of equipment. With this approach, the following requirement is made possible by simply summing all records for a given date. A simple view with a groupBy achieves this - no formulas required. (i.e., group by transaction date)

Shaun_Hornsby:

The sum at the bottom of that column will let me know the total amount of fuel dispensed for the day

And to meet the next requirement, another groupBy makes it possible to isolate transactions (and their sums) based on each piece of equipment. (i.e., group by equipment name or id)

Shaun_Hornsby:

i also need to keep a running total of fuel dispensed for each piece of equipment.

The final requirement is achieved quite easily because it’s unnecessary.

Shaun_Hornsby:

Does anyone know how i can do this with a script where the script will find all fields that contain “Fuel” and then add each cell in each row to a “Total” field?

Instead of searching for field names (a process that arguably could be performed in a script block with the meta SDK), you are simply filtering by various attributes as needed - a feature that is fundamental to all database management tools and in Airtable, requires no code.

[ScottWorld]

Running totals (or running balances) are tricky in Airtable, since it’s not a built-in function of Airtable. However, several people have posted great demo databases that actually solve this problem. I would do a search in these forums for the phrase “running total” or “running balance”, and you should get lots of results.

[Rebecca_Elam]

Look i have a massive equip. management base, its what i do, and i can tell you Airtables just not meant to be used like that^^^. If you’re gunna use it like a spreadsheet and not use the software how its designed to be used just use excel. You’re making the task harder by putting it in Airtable the way you’re trying to do it. i’m sorry they’re not the answers you want but there’s no way to do what you’re asking ,the way you’re asking to do it, efficiently. Bill and Justin are right. and once you figure that out you’ll be able to make something that works but what you’re proposing, isn’t going to work in the long run. that’s what everyone is trying to tell you. we cant even answer your question because you haven’t laid out your base properly from the beginning and you need to redesign before someone can provide you a viable solution.

[Rebecca_Elam]

Right now your table says x truck used x amount of fuel on this day and x amount of fuel on this day and x amount of fuel on this day. If built correctly it should be one table that says on this day, this truck used this much fuel and add new records not new fields. even though the truck is on that list multiple times you link back to the trucks and roll up the total from the transactional table. That’s (my best attempt at) what bill meant about separating state data vs transactional data but bill feel free to come at me i always learn a lot when i get to interact with ya haha

[ScottWorld]

Oh right — to echo what the others have said — your base should really be setup properly first, before you can really dive into this wholeheartedly.

[Bill_French]

Rebecca_Elam:

what bill meant about separating state data vs transactional data but bill feel free to come at me i always learn a lot when i get to interact with ya haha

Spot on and perhaps better-stated.

[Bill_French]

ScottWorld:

your base should really be setup properly first

And the definition of “properly” is always a bit fleeting if not subjective in some cases. There are many ways to design an effective data model for this solution, but some are really bad and a few others could be far better. So far, @Shaun_Hornsby is apparently determined to choose one of the worst approaches for a database and possibly one of the more common (although still problematic) ways to do this in a spreadsheet.

The term “database” is quoted and italicized in my comments for good reason - it presupposes you have a goal and inclination to use the benefits of a relational model where transactions (fuel consumption, etc) are separated from state records (equipment). The instant you abandon this logical separation, you best be checking the thickness of the ice below because it will crack and you will get wet if not drown.