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