Formula or Macro?

[Shaun_Hornsby]

So I have done work with formulas on other projects but this one i havnt been able to figure out. I have a table where i am going to keep track of equipment hours and another where i keep track of fuel used.

For the hours table i will add a new column for each day with the equipment hours to track how many hours that machine operated that day but i need to have 1 column that reports the last hours input. I know that i can put a “Ceiling” formula in but that would require modifying the formula to add a new column each day and after awhile that will get stupid. I would need one column that would take the highest number of all columns.

For the Fuel table, i will be adding a new column for everyday to track fuel consumption. This would be easy using the Sum formula but just like the Ceiling formula, i would have to modify the formula everyday, adding a new column for it to include. I would need one column that could be updated with the sum of all columns related to fuel.

Is there anyway to do this without have ridiculous formulas? I am assuming i would need to do some form of macro but i have no clue how to write that out. Any help is much appreciated.

[Bill.French]

Hi @Shaun_Hornsby, and welcome to the community!

@Shaun_Hornsby

… and after a while that will get stupid.

After a while? I think that ship has sailed.

Oddly, this question is best addressed with another over here.

There are three reasons we capture data:

1. Compliance - what happened; when did it happen?
2. Process - where do we stand; what’s next?
3. Analytics - let’s make a decision.

Data modelling is a delicate dance that must embrace each of these key objectives. If you have a data model that cannot effectively embrace the requirements of #3, you need to fix that in the model with deference to the limitations of the database platform, …OR… you must backfill the weaknesses with script automation.

In my view, you are missing a key aspect in your data model - separation of state data from transaction data.

State data is like company details; there is one (and only one) record for each company. It is immutable and while it changes from time-to-time, it is very different from transactional data.

Transaction data is like sales; it’s a log or ledger of activity.

It appears your data model is conflating these fundamentally different information types. To not do stupid, you need to separate them and utilize links to formulate a relational database model.