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
#17

@kuovonne , I apologize for taking so long to get back with you but i do appreciate your detailed response.

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.

I am doing data entry mostly via my computer every evening, sometimes every other evening. With that being said, I prefer to do data entry on my computer (via the browser) for my fuel logs at the end of the day, BUT throughout the day, if i need to just add, remove or edit a piece of equipment, most of the time i do that with my phone or tablet, whichever i have on me at the time.

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?

The main thing that i do currently with the individual daily fuel amounts is use it as a reference for a couple of different things. If a machine or generator goes dead or runs out a fuel, I can pull up the last few days or even the last week or so worth of records to see when the last time i filled it was and how often. If i fill that machine everyday and it uses the same amount of fuel everyday on average and never ran out before then we know that either there is a fuel leak, someone siphoned fuel or something else is wrong. Having this info available will also allow me to see if i didn’t fill something.

As far as calculations go, I do not have to do any calculations with this info. It would just be nice to be able to look in and say that over the course of 3 months, or 6 months, etc… equipment number 150 used approximately 3000 gallons of fuel. I personally have no need for that info, its honestly just something i want to do for fun.

I do have a Pro account.

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).

I have not considered this, I have built a number of other bases for my mom and I to track different types of data and this is how i have done those so its what i am use to. I like the idea, I am just not sure how this would work if/when the time comes i need to reference data.

The whole reason that i have not done my data entry for fuel logs the way that several others have suggested is this. You take a 150 pieces of equipment x 6 days a week x 30 days and you get 27,000 ish entry logs in one month. That doesn’t include the machines that will need filled twice a day because they have a day shift and night shift running. A month and a half in and my 50,000 record limit is hit. That is the main reason i was attempting to do it the way that i am.

As far as my work flow currently goes, I have a paper log that i fill out through out the day. That is our company log that is also required by DOT to have in my truck at all times when on my route. At the end of the day, i take a picture of that log and turn it into the office. When i get home, i use that picture to update airtable for the Fuel pumped out that day.

I love the idea of the workflow you suggested but the problem with that is cell service. Where my current project is, there is very little cell service. Even with a cell booster, data loads too slow to even attempt doing it this way. The only way to do it that way would to have a full blown app that would store all of the data i input throughout the day internally on the device, when i get done and get on wifi or get a decent cell signal, hit a button to sync the data and with this just being a project for myself and not my company, i am not putting that kind of time into this.

Fuel logs are not the only thing i am tracking with this. I have a table with all of my equipment info (unit number, vendor info, Serial Number, Make, Model, etc). I have another Table that contains Equipment hours. In the Hours table, several times a week i will go in and “update” the current hours for each machine, another field contains when it was last service, another contains the service intervals (250 hrs, 500 hrs, etc) and then a formula that calculates how many hours left until the next service. And then I have the Fuel Logs. There is more i plan to do with it eventually but thats what all i have going in it so far.

Basically what i am trying to do with this current setup is this.

After i “Manually” created a new field for the days fuel logs. I “Manually” enter the fuel amount for each machine. Then i would like to “Manually” run a script that will find all fields that contain specific values. I figured “Fuel” would work but it can be anything.

Once the script finds all fields that contain the value “Fuel”, it performs simple math and adds all of those fields for each machine (row). It then stores that number that it just added into an EXISTING field labeled “Total”. It would need to over write or clear the existing data that is in the total field before storing the new number.

What i had planned on doing is say middle of Feb, go in and copy/paste all data from Jan into a spreadsheet, add up all the data for each machine. Once that is done, i would delete all of the Daily fuel fields for jan and have 1 field “Fuel Jan”. I would paste the totals for jan into that field and because the title contains “Fuel”, the script would treat it just like it does the daily fields and use it in the calculations for the “Total”.

That is something that would only take me 5-10 minutes to do once a month and it keeps me from having several hundred fields at the end of the project. I am not looking a script to add and delete fields, i will do all that myself as i need to. But, like i said, i am open to your idea of the long text field and storing data in json format, i just have never done that so am unsure of how that would work.

#18

It would work like this.

Given these requirements, you have three options if you decide to stay with Airtable:

  1. Pursue a design where columns are days.
  2. Follow the design recommendations that @kuovonne and others have suggested.
  3. Compress the fuel transactions into JSON objects to expand the 50,000 record ceiling.

#1 works for you and you are already on that pathway. it’s ugly, difficult to maintain, and it’s laden with manual effort, but it won’t hit a wall for a while.

#2 would require a monthly archive process of some sort.

#3 is complex and costly and I don’t get the sense you have any budget for design work let alone implementation assistance.

Adalo and other app builders can do this and sync to Airtable. But, you’ve ruled this out, so no point in continuing.

I think you have your answer - Option #1 it is.

#19

Actually, my design suggestion was to have data entry as individual records, then have a script append the data to JSON in a text field and delete the individual records. The script could also do some other calculations, such as a running total or daily average. This would avoid the 50,000 record limit, but still preserve the ability to do data entry on the individual level. Eventually the limits of a rich text field would be reached, but that would take quite a long time.

Given the new information that data entry is performed manually at a computer from a handwritten log once an evening, I think that this system could still work, but instead of (or in addition to) storing the data in JSON, the script could append the info into a rich text field to be more human readable.

How costly this method would be is hard to determine. If @Shaun_Hornsby hires someone else to write the script, it depends on the rate of the person hired. On the other hand, if he learns to code (which it looks like he might do), it costs the time it takes to learn to code, and practically no money. Another alternative is to do a mix–hire someone for a bit of coaching/design work, but do the majority of the coding himself.

Your math is a little off here. Notice that your units do not cancel out because you are multiplying times 6 days/week and 30 days/month. You’re really looking at more like
150 entry logs/ day * 6 days /week * 4.34524 weeks / month = 3,911 entry logs/ month

This is why I asked about the pro account. With a pro account, you would have enough automation so that each new entry log could trigger an automation that would run a script to convert the data to JSON, then delete the original record. However, since you are doing data entry at then end of the day, I think a regular app script run from a button would be better than an automation script.

1 Like
#20

Lol yes you are correct, my brain doesn’t want to work some days.

Is that something that would be hard to code? Running a JSON script once a day? Also, if doing that, how would I access that info in the event I need to see how often I filled a piece of equipment up? Would it be in a format that would be easy to read?

And yes, I would be doing most of the code myself. I have takin a few programming classes years ago and I havnt messed with much programming since. It may take me some time but if someone could point me into write direction for which online course would best suit what I need to learn/re-learn that would be helpful.