I have a simple timesheet app where a user captures their time on a project for a certain day. What makes it complex is that a user can log time for multiple projects in a day. So, what I want to do is total the time logged for each day that a user logs time against a project.
For example, on Monday I spend 1 hour on project A and 1 hour on project B, and on Tuesday I spend 1 hour on project A. That should tally to 2 hours on Monday and 1 hour on Tuesday. Here is where I’m currently with the build but I want to know if I’m doing anything wrong. For reference, I tried following the answer in this thread: How to get total of rows based on a column value? - #8 by Jeff_Hager
Working with dates and times in Glide can be a bit tricky. One thing to be aware of (and always keep in the back of your mind) is that what you see is not necessarily what you get.
What I mean by that is that even though you might have a date/time column configured to display date only, or time only, or whatever - Glide will still store the full date and time under the hood. So if you start making assumptions based on what you see in the Data Editor you can very quickly come unstuck.
With that in mind, I have one suggestion to make.
Instead of relating the Date column to itself, what I would do is first create a template column that just takes the date column and converts it to a string…
And then use that column to create the relation (ie. relate the template column to itself). This will ensure that you are comparing apples to apples, so to speak.
Other than that, it looks like you are on the right track. I’ve just finished building a fairly complex timesheet app for what sounds like a very similar use case. So feel free to reach out if you get stuck with anything.
2 Likes
Thank you so much for the reply. I’ve applied the changes you mentioned, and it seems to be working still. But I’m curious as to how I can display the rollup value for the day without showing duplicate dates. I’d love to hear your suggestion on this. Thank you so much in advance!
Where and how do you want to display this?
Can you describe to me how you want it to look on the app screen?
What I have at the moment is a tab that displays a list of timesheet logs (the list filters to only show timesheet records of the logged-in user), and an add form button to capture time. Then what I’d like to add is another tab that displays a list of all days where time was logged (but only one date record per day, not every individual record for each day) and the total time logged against that day).
I’m not sure how to, but I can share my app with you if you’d like? Maybe that will help.
Lots of different ways you can approach this.
For your summary screen, do you want to show a total for every day that has hours logged? (even if it’s 5 years worth of data?)
Or do you want to restrict it to a certain time period?
- Current week
- Current month
- Past 7 days
- Past 30 days
- ??
I think you need to decide that first, and then take it from there.
Let’s say you decide that you want to show a rolling 7 day window. What I would probably do is create a working table with 7 rows - one for each of the past 7 days. You can use a bit of Date Math to make that dynamic. Then what you can do is use a combination of the signed in users UserID plus the date to build a relation to your main timesheet data table, and fetch the data for each of the past 7 days.
That’s just one approach. As I mentioned, there could be several other approaches. But first it’s important to have a clear definition of what you want the end result to be.
Hi Darren. Thanks for the response. Ideally, it would be for the current month. I’m familiar with how to display this in Adalo but it’s quite a bit different trying to build this in Glide.
Okay, so similar concept.
Creating a dynamic table like I mentioned above can be done as follows:
- Start with a new Glide Table, and add 31 rows
- Add a RowID column
- Add a Lookup column, that targets the RowID column (this will give you an array of the RowIDs)
- Add an Array->Find Element Index column, that looks for the RowID in the Lookup column (this will give you a number for each row, beginning at zero)
- Add a math column, using the following formula:
N+1-DAY(N)-HOUR(N)/24-MINUTE(N)/1440-SECOND(N)/86400 (Use the “Now” special value as a replacement for N. This will give you the date of the first day of the current month)
- Add another math column with the following formula:
First + Offset, where First is the first day of month column, and Offset is the Row number.
- Finally, add a template column to convert that date to a string (similar to that I described earlier)
This should give you a table with each row representing a day for the current month. With this, you can add a relation to join to your timesheet data, and then use a rollup column to get a sum for each day.
You could then use that in an inline list, or if you wanted to get a bit fancy you could create a HTML table with a couple of extra templates and a joined list.
Here is roughly what it should look like:
1 Like
Hi Darren, I’ve gotten to the second to last step but when I type in the formula or paste it in, I get a blank black screen. Not sure why it’s happening. I’ve tried in various browsers but still happening. Seems like it crashes at that step.
Okay never mind, I tinkered with it and got it to work.
