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
- 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
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
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
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
So it should show from 1st January 2024 to 31st December 2024 for example?
yes. And best case would be that the user also can go even further and it shows the period the user selects
Here’s my setup if you want “current year” only.
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.
N-O+1+I
With N being “Now”, O being “Ordinal Date” and I being the index.
1 Like