Automatically add values from multiple fields

Development and integrations Scripting app
#1

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.

#2

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.

#3

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!

#4

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)

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)

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

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.

1 Like
#5

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.

#6

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.

4 Likes
#7

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

2 Likes
#8

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.

#9

Spot on and perhaps better-stated.

#10

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.

#11

@Shaun_Hornsby,

Hopefully to provide a little more practical help, here is a quick and dirty example of the kind of setup @Bill.French and @Rebecca_Elam are talking about when they mention things like “transactional data” and “relational models”.

Feel free to poke around, look at formulas used, add new records to get a feel for how it works, etc. The main thing you’ll want to take away is implementation of your tracking of Fuel consumption as its own table(“tab” at the top), where each each record(“row”) in that table represents one recording of dispensed fuel. Each Fuel Record has a relationship to a single piece of Equipment, and each piece of Equipment has a relationship with many Fuel Records (one for each day, presumably)… this encapsulates the “relational model”. And every time you perform this process of recording the Fuel Dispensed to a piece of Equipment, you do so by creating a new record that captures the date, the item dispensed to, and the amount dispensed, as a transaction that occurred… this encapsulates the “transactional data”.

1 Like
#12

@Jeremy_Oglesby , @Bill.French , @Rebecca_Elam …

I appreciate everyones input. @Jeremy_Oglesby thank you for the visual with your base.

Heres the thing, I understand what you all are talking about, I have built bases that do exactly what yall are talking about. Everytime i fill a piece of equipment, I start a new row, select that equipment number and in the next column input the fuel amount. That works fantastic if all i am doing is managing my farm equipment. But what i am trying to avoid is having 3000-4000 new rows added each month. Which is why i am testing out this other method that i am trying to see if i can make work. Having the equipment number in the first column (currently its only 30 pieces of equipment but soon it will be 100-150). So instead of continuously adding more rows for each fuel entry, i add 1 column that will contain up to 150 fuel entries.

As far as knowing the differences between Airtable and Excel and knowing when to use one vs the other. The entire purpose of using AirTable is for its versatility. I can just load the app on my phone, my tablet or log in via the browser. No matter what device i am using i can access it on a view that is made for the device i am using vs using something like EXCEL which isnt as user friendly when it comes to doing using it on your phone.

As far as what the capabilities of Airtable are, Just because you guys and gals havnt done something yet does not mean that it cant be done. If this was pre-script airtable days i would say you all are 100% correct as the formulas only allow you to do so much. BUT, since airtable introduced a way to run scripts, there should not be much that you cant actually do. I like to push things to their limits and see what i can make it do and im not going to give up on the idea i have just because all of you say it isnt possible or isnt easy to do. Nothing worth having comes easy.

As far as looking at other demos and other threads, one of the ones i looked at yesterday was this one but it doesnt accomplish what it is that i want to do.

There are many others i have looked at but so far i havnt come across anything but i will continue my search. I like a challenge, and at the end of the day, if i cant make it work i cant make it work.

Again, i thank you all for your input and hope you all have a great night.

#13

It always helps to know scale before making recommendations so thanks for the added details.

Indeed, but the one thing you need based on your vision (as I understand it) is the ability to programmatically create fields. This is not currently possible in a Script Block.

Um, actually, it does. If you are intimately familiar with the API and the Blocks SDK, there’s a good chance you do know what can and can’t be done. :wink: Half the challenge of being a productive and successful developer is knowing all of the limitations.

But hey, feel free to knock yourself out. I’ll learn a bunch when you achieve your vision and publish it here. One parting comment - you best try this in React Javascript (custom block), not Script Blocks and certainly not Script Actions.

2 Likes
#14

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?

  1. You go about your daily fueling rounds with your phone.
  2. You have a webpage that is an embedded Airtable view listing all of your equipment. For each piece of equipment there is a button.
  3. 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.
  4. 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.
  5. 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.

2 Likes
#15

So do we. We spend hours upon hours experimenting and playing with what we’re given with Airtable’s base design features and APIs, and that includes finding creative ways to work in/around the limitations of these systems. On top of that, we share ideas with each other all the time. In that light, the knowledge base at work here to try and figure out this system you describe isn’t trivial. When someone in our position talks about things that either can’t be done or would be difficult to do, we don’t say that lightly.

True, but just because something isn’t easy doesn’t mean it’s worth having. :wink: Trust me, we LOVE putting in the time to find solutions to unique problems. However, we’ve also got enough collective experience to know when to stop polishing the you-know-what. In my early Airtable days I built stuff that took a long time and was pretty complex, and yeah, it worked. However, knowing what I know now I would not encourage anyone to build systems like that. It’s equally important to work smart, not just hard. We’re definitely willing to do hard stuff, but if something hard isn’t smart, we’ll say so.

2 Likes
#16

Yeah, when I finished my house made of hub caps I found along the highway, I realized two things - (i) it was hard and (ii) it wasn’t really worth anything. I’ll never do that again.

This thread is beginning to become a treasure trove of metaphors and deep thoughts sadly though, not Jack Handy’s deep thoughts.

There’s a book about knowing when to push through (or stop) - The Dip - highly recommended and especially for Airtable developers. :wink:

3 Likes