Daily time tracking

Ask for Help
1

Hey,

I am working on a time tracking application. In the app the team can create entry’s in a calendar view and block the time they have worked. In a database that has all weeks inside I already show how much time they have worked this week and so on.

But I think the best approach is to work as a basis with a database that has all days inside. I am now not sure about the approach how to create a table with all days inside:

  • it will be very huge at the end. So I choose a big table?
  • Is there an option how it can grow automatically? Or how can I do it? For example that it is always 1 year from now and is adding the rows automatically? Or do I have to do that manually?
  • Does anyone has any other suggestions for such a use case? I don’t need a tracking „time entries“ are ok. But it is important that it works without any failure also 1 year late :wink:
  • This big table will then with user specific columns right? So that I can for example say vacation true in a user field, when someone is in vacation on a specific day

Thanks for your help
Andi

2

So in the end the ideal outcome is a view where users can view all days and how much they have worked for each day?

You can use an indexing method, add 365 rows and only ever show the last 365 days, for example.

Apart from the indexing columns (which is a rowID, a lookup and a find element index column), you might just need relations/queries + rollups from there.

1 Like
3

exactly! thats the ideal outcome. + seeing that in a monthly view and so on… but that are just sums to calculate.

I do not really get it with the indexing column. So I create 365 rows… which columns I add beside a row ID? And how they are changing? And how I have then saved the info for a time entry which is 2 years old?

Thanks
Andi

4
  • Add 365 rows (or 366 rows if you advertise that as a year’s worth of data, in case there’s a leap year).
  • Add a rowID column.
  • Add a lookup pointing to that rowID column so you get an array of rowIDs.
  • Add a “find element index” column, find the current row’s rowID from that array, then you get a 0-based index (from 0 to 364/365).
  • Add a math column, with the formula being:
    N - HOUR(N)/24 - MINUTE(N)/1440 - SECOND(N)/86400 - I*0 with N being the Now value, I being the index. You will get the dates.
  • Create a further column to convert the dates to numerical format, and then your relations + rollups to calculate the worked time.
1 Like