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
5

Hey,
thanks again for your last input. Now after doing it as you mentioned we come up with the result that it is better for us that the table is showing the actual year. So that you can go through the last month. Or best case would be to have a table where you can navigate through every month back and forth…

So we have a TAKS table where we have all tasks / time entrys inside. And with the 365 Table it should be possible to go through all the single months of a year like a calendar and see the summary of your work.

Do you understand what I mean? At the moment it works with the entries in the future from today but not with the past.

Thanks for your input
Andi

6

So it should show from 1st January 2024 to 31st December 2024 for example?

7

yes. And best case would be that the user also can go even further and it shows the period the user selects

8

do you have an idea?

9

Here’s my setup if you want “current year” only.

image
image566×880 56 KB

Calculate the ordinal date of “today”.

function calculateOrdinalDate() {
    // Get the current date and time
    const now = new Date();
    
    // Extract the year and start of the year
    const year = now.getFullYear();
    const startOfYear = new Date(year, 0, 1); // January 1st of the current year

    // Calculate the difference in days
    const differenceInMilliseconds = now - startOfYear;
    const ordinalDate = Math.floor(differenceInMilliseconds / (1000 * 60 * 60 * 24)) + 1;

    return ordinalDate;
}

return calculateOrdinalDate(p1)

Then proceed with the helper table, index calculation, and you’ll get something like this.

image
image1208×317 52.5 KB

image
image610×607 28.3 KB

N-O+1+I

With N being “Now”, O being “Ordinal Date” and I being the index.

1 Like